#1
|
|||
|
|||
unable to retrieve data
Hi there,
i have a VBA code that retrieves its data from an external excel workbook(masterplandata workbook) and copies it onto the (example workbook). what the VBA code does: 1. retrieve the data for July 2018 by the fabric delivery column 2. copy the data according to columns of the example workbook 3. vba would not clear records in the example workbook with OFM, KH and Collar and Cuff as these are typed in by the users. 4. sort the records according to the PO column Now with the code, i am not able to copy over the records. does anyone know why? i have attached the example workbook below |
#2
|
|||
|
|||
Quote:
The why is because the b array is not being written to the sheet. Care to post a link to where you got the code ? |
#3
|
|||
|
|||
Code:
Sub July() Dim arr, c, b(), n& Application.ScreenUpdating = False Worksheets("July 2018").Range("A4").AutoFilter Workbooks.Open "C:\Users\sophia.tan\Desktop\MasterPlanData.xlsx", 0, 1 arr = Sheets("Excel").UsedRange ActiveWorkbook.Close 0 c = Array(0, 2, 13, 14, 7, 8, 11, 1, 9, 10, 16, 17, 20, 22, 15, 30, 27, 28, 29, 3, 4, 39) d = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 14, 15, 16, 17, 18, 19, 20, 21, 23) ReDim b(1 To UBound(arr), 1 To 23) For i = 2 To UBound(arr) If arr(i, 13) >= DateSerial(Year:=2018, Month:=7, Day:=1) And arr(i, 12) <= DateSerial(Year:=2018, Month:=7, Day:=31) Then n = n + 1 For j = 1 To UBound(c) b(n, d(j)) = arr(i, c(j)) Next End If Next With Worksheets("Sheet2") .Range("A4:W" & Rows.Count).CurrentRegion.SpecialCells(xlCellTypeVisible).AutoFilter field:=1, Criteria1:="<>OFM" .Range("A4:W" & Rows.Count).CurrentRegion.SpecialCells(xlCellTypeVisible).AutoFilter field:=13, Criteria1:="<>Collar & Cuff" .Range("A4:W" & Rows.Count).CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).Clear .Range("A4:W" & Rows.Count).Resize(UBound(b, 1), UBound(b, 2)) = b .AutoFilter.ShowAllData .Range("A4").CurrentRegion.Sort key1:=Range("G6"), order1:=xlAscending, Header:=xlYes .Range("A4").Select End With Call Fabrication Application.ScreenUpdating = 1 End Sub |
#4
|
|||
|
|||
Afraid I don't really follow what you're trying to do,
but this is the line that wrote the b array to the sheet Code:
.Range("A4:W" & Rows.Count).Resize(UBound(b, 1), UBound(b, 2)) = b |
#5
|
|||
|
|||
if i use the above code
Code:
.Range("A4:W" & Rows.Count).CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).Clear i have attached the excelworksheet below. the error are in red |
#6
|
|||
|
|||
Not all of the red is error, unless you're not filtering the way you want.
See if this does what you're after. Because your sample columns A:W differ in length, lastRow is calculated different than originally. The loop that populates "M" based on "J" I've only applied to newly added data. Only columns A:W portion of rows are deleted, column "Y" cells are numbers to verify this. There is a button to put the original sample values back on the sheet so you can easily play with it over and over. If you uncomment the STOP instruction in the code and split your screen half code and half sheet, you can step through the code a line at a time with the F8 key and watch what happens on the sheet with execution of each instruction. I hope I've commented the code enough to follow without too much trouble. |
#7
|
|||
|
|||
Thanks this is what i am looking for.
however the code still cannot auto filter for Collar & Cuff and whenever i click on the button it would paste the same row again. it there a way for it to not have duplicate? i have attahced the duplicated excel workbook |
#8
|
|||
|
|||
i ran through the codes with the F8 key and the autofilter seems to filter all the records instead of the only the stated criteria
|
#9
|
|||
|
|||
No cells in column A contain OFM and KH.
I suspect the filter operator should be xlOR, and you've changed things from the original file displaying rows where A is not OFM to displaying rows where A is OFM, and neither line displayed after filtering for OFM or KH has Collar & Cuff in column M. So, I'm not sure what it is you want the filters to do, can you put into words, what should be displayed on screen after the filters have been applied. Quote:
Also, please alter the clear what's left section of code to be Code:
' clear what's left On Error Resume Next 'suppress error notification in case nothing visible .Offset(1).SpecialCells(xlCellTypeVisible).ClearContents On Error GoTo 0 'remove suppression of error notification |
#10
|
|||
|
|||
sorry for the confusion, i would like to the code to filter out rows that contain OFM and KH in column A and also filter out rows with Collar & Cuff in column M.
so that the excel would not delete these rows. for the duplicate part, what i meant was for example in the external workbook, in this case, there is 5 rows to be copied over. Hence no matter how many times i click on the button, the excel worksheet would only show the 5 rows. however in this code now, if i click the button again, it would copy over the same 5 rows below the previous one. i have attached a excel worksheet example of what happens if i click on the button three times. it shows that the same 5 rows three times. those in red are suppose to appear only once |
#11
|
|||
|
|||
Sorry RosieSummers, currently I am no longer participating on this forum.
|
#12
|
|||
|
|||
is there any other forums that i can contact u on to get help on this?
|
#13
|
|||
|
|||
Are still needing assistance with this ?
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
"word was unable to open the data source" error | meganadand | Mail Merge | 6 | 02-27-2020 11:25 AM |
Retrieve the last activated document name. | eduzs | Word VBA | 1 | 08-22-2017 03:11 PM |
Unable to open Outlook, receiving message unable to open because (myaccount)@gmail.c | Flyin' Aces Tattoo | Outlook | 0 | 04-20-2013 11:56 AM |
Retrieve PPS files | uncledewey | PowerPoint | 2 | 08-10-2012 09:08 AM |
Microsoft Scheduler retrieve Calendar data | badda | Office | 0 | 11-22-2009 06:04 AM |