Hello I'm stuck in the automation of a file (kind of new in VBA). Here is the file information:
- Columns A-B + D-L + Q all have formulas based on column C and another external excel file.
- Columns M-P have manual data entered by several different people.
- In order to update column C without affecting the manual data entered in columns M-P, I've made 3 columns U-V-W:
- I paste in column U the new data which needs to be replaced in column C.
- Column V is a vlookup of cell C??? in column U. If I obtain an error, then it means the data in column C should be deleted.
- Column W is a vlookup of cell U??? in column C. If I obtain an error, then it means the data in column U needs to be added to the end of column C.
With filters in place, I can easily update the file, but it takes about 30 minutes each morning (including some other sheets).
I started to automate the file and this is where I'm stuck (I replaced some names below):
Code:
Windows("A.xlsm").Activate
Sheets("Unique ID#").Select
Range("D3:D9000").Select
Selection.Copy
Windows("B.xlsm").Activate
Sheets("TLP").Select
Range("U52").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
LastRow = Range("U" & Rows.Count).End(xlUp).Row
Range("W52").Select
Selection.AutoFill Destination:=Range("W52:W" & LastRow)
LastRow2 = Range("Q" & Rows.Count).End(xlUp).Row
Range("V52").Select
Selection.AutoFill Destination:=Range("V52:V" & LastRow2)
ActiveSheet.Range("$A$51:$W$9000").AutoFilter Field:=23, Criteria1:= _
"0ADD ID"
Range("U51").Select
What I want to do from here is select all filtered results that appear in column U and paste them at the end of column C.
After that, I'll filter field 22 by "0DELETE ID".
Another thing I'd like to do from here is to select all the rows from that filter and delete them.
These are my first 2 roadblocks, I don't think I'll get anymore if I get past those. Also, an example of what my file and formulas look like can be found attached.
Thanks for any help you can provide, it's greatly appreciated.
Kind Regards,
Younes