File automation problem (VBA)

YounesB3 · #1 09-21-2012, 02:38 PM

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
ckBox automation	coconutt	Word VBA	6	09-11-2012 04:22 PM
Document automation	coconutt	Word	1	05-02-2012 01:45 PM
Urgent help regarding automation.	aligahk06	Excel	1	01-14-2010 01:55 PM
What is Application Automation and How can I use it in VBA	KramerJ	Excel	0	03-30-2009 12:59 PM
COM Automation Errors	ivanm	Word	0	03-23-2009 07:02 PM