Copying Sheet from one Workbook to another.

[PuntingJawa]

I'm curious if it is possible to copy data from a worksheet to another using VBA.
Example.
I am trying to pull all data in Workbook "OP-AUX Database Test(MacroEnabled)" sheet19 to workbook "SN Log" sheet1.
With the database I have made (with massive help from this community) the macro I have auto logs particular columns on sheets2-17 to sheet19 while removing duplicates. Would there be a way to do this same task but move it to another workbook so there's less clutter and easier logging while also removing duplicates like the following VBA code? I'm currently using the following VBA to capture required information to Sheet19 on the "OP-AUX Database Test(MacroEnabled)" workbook.

Private Sub Worksheet_Activate()
 Sheet2.Range("K2", Sheet2.Range("K" & Rows.Count).End(xlUp)).Copy
 Sheet19.Range("A1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Sheet19.Range("A2", Sheet19.Range("A" & Rows.Count).End(xlUp)).RemoveDuplicates 1
 Sheet19.Range("B2:B" & Sheet19.Range("A" & Rows.Count).End(xlUp).Row).Value = Date
 Sheet2.Range("L2", Sheet2.Range("L" & Rows.Count).End(xlUp)).Copy
 Sheet19.Range("c1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Sheet19.Range("c2", Sheet19.Range("c" & Rows.Count).End(xlUp)).RemoveDuplicates 1
 Sheet19.Range("D2:D" & Sheet19.Range("C" & Rows.Count).End(xlUp).Row).Value = Date
 
 Sheet3.Range("B2", Sheet3.Range("B" & Rows.Count).End(xlUp)).Copy
 Sheet19.Range("e1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Sheet19.Range("e2", Sheet19.Range("e" & Rows.Count).End(xlUp)).RemoveDuplicates 1
 Sheet19.Range("F2:F" & Sheet19.Range("E" & Rows.Count).End(xlUp).Row).Value = Date
 
 Sheet3.Range("C2", Sheet3.Range("C" & Rows.Count).End(xlUp)).Copy
 Sheet19.Range("g1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Sheet19.Range("g2", Sheet19.Range("g" & Rows.Count).End(xlUp)).RemoveDuplicates 1
 Sheet19.Range("H2:H" & Sheet19.Range("G" & Rows.Count).End(xlUp).Row).Value = Date
 
 Sheet4.Range("B2", Sheet4.Range("B" & Rows.Count).End(xlUp)).Copy
 Sheet19.Range("i1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Sheet19.Range("i2", Sheet19.Range("i" & Rows.Count).End(xlUp)).RemoveDuplicates 1
 Sheet19.Range("J2:J" & Sheet19.Range("I" & Rows.Count).End(xlUp).Row).Value = Date
 Sheet4.Range("C2", Sheet4.Range("C" & Rows.Count).End(xlUp)).Copy
 Sheet19.Range("k1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Sheet19.Range("k2", Sheet19.Range("k" & Rows.Count).End(xlUp)).RemoveDuplicates 1
 Sheet19.Range("L2:L" & Sheet19.Range("K" & Rows.Count).End(xlUp).Row).Value = Date
 
 Sheet5.Range("B2", Sheet5.Range("B" & Rows.Count).End(xlUp)).Copy
 Sheet19.Range("m1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Sheet19.Range("m2", Sheet19.Range("m" & Rows.Count).End(xlUp)).RemoveDuplicates 1
 Sheet19.Range("N2:N" & Sheet19.Range("M" & Rows.Count).End(xlUp).Row).Value = Date

 Sheet6.Range("B2", Sheet6.Range("B" & Rows.Count).End(xlUp)).Copy
 Sheet19.Range("o1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Sheet19.Range("o2", Sheet19.Range("o" & Rows.Count).End(xlUp)).RemoveDuplicates 1
 Sheet19.Range("P2:P" & Sheet19.Range("O" & Rows.Count).End(xlUp).Row).Value = Date
 Sheet6.Range("C2", Sheet6.Range("C" & Rows.Count).End(xlUp)).Copy
 Sheet19.Range("q1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Sheet19.Range("q2", Sheet19.Range("q" & Rows.Count).End(xlUp)).RemoveDuplicates 1
 Sheet19.Range("R2:R" & Sheet19.Range("Q" & Rows.Count).End(xlUp).Row).Value = Date

 Sheet7.Range("B2", Sheet7.Range("B" & Rows.Count).End(xlUp)).Copy
 Sheet19.Range("s1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Sheet19.Range("s2", Sheet19.Range("s" & Rows.Count).End(xlUp)).RemoveDuplicates 1
 Sheet19.Range("T2:T" & Sheet19.Range("S" & Rows.Count).End(xlUp).Row).Value = Date
 Sheet7.Range("C2", Sheet7.Range("C" & Rows.Count).End(xlUp)).Copy
 Sheet19.Range("u1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Sheet19.Range("u2", Sheet19.Range("u" & Rows.Count).End(xlUp)).RemoveDuplicates 1
 Sheet19.Range("V2:V" & Sheet19.Range("U" & Rows.Count).End(xlUp).Row).Value = Date
 
 Sheet8.Range("B2", Sheet8.Range("B" & Rows.Count).End(xlUp)).Copy
 Sheet19.Range("w1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Sheet19.Range("w2", Sheet19.Range("w" & Rows.Count).End(xlUp)).RemoveDuplicates 1
 Sheet19.Range("X2:X" & Sheet19.Range("W" & Rows.Count).End(xlUp).Row).Value = Date
 Sheet8.Range("C2", Sheet8.Range("C" & Rows.Count).End(xlUp)).Copy
 Sheet19.Range("y1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Sheet19.Range("y2", Sheet19.Range("y" & Rows.Count).End(xlUp)).RemoveDuplicates 1
 Sheet19.Range("Z2:Z" & Sheet19.Range("Y" & Rows.Count).End(xlUp).Row).Value = Date
 
 Sheet9.Range("B2", Sheet9.Range("B" & Rows.Count).End(xlUp)).Copy
 Sheet19.Range("aa1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Sheet19.Range("aa2", Sheet19.Range("aa" & Rows.Count).End(xlUp)).RemoveDuplicates 1
 Sheet19.Range("AB2:AB" & Sheet19.Range("AA" & Rows.Count).End(xlUp).Row).Value = Date
 Sheet9.Range("C2", Sheet9.Range("C" & Rows.Count).End(xlUp)).Copy
 Sheet19.Range("ac1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Sheet19.Range("ac2", Sheet19.Range("ac" & Rows.Count).End(xlUp)).RemoveDuplicates 1
 Sheet19.Range("AD2:AD" & Sheet19.Range("AC" & Rows.Count).End(xlUp).Row).Value = Date

 Sheet10.Range("B2", Sheet10.Range("B" & Rows.Count).End(xlUp)).Copy
 Sheet19.Range("ae1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Sheet19.Range("ae2", Sheet19.Range("ae" & Rows.Count).End(xlUp)).RemoveDuplicates 1
 Sheet19.Range("AF2:AF" & Sheet19.Range("AE" & Rows.Count).End(xlUp).Row).Value = Date

 Sheet11.Range("B2", Sheet11.Range("B" & Rows.Count).End(xlUp)).Copy
 Sheet19.Range("ag1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Sheet19.Range("ag2", Sheet19.Range("ag" & Rows.Count).End(xlUp)).RemoveDuplicates 1
 Sheet19.Range("AH2:AH" & Sheet19.Range("AG" & Rows.Count).End(xlUp).Row).Value = Date


 Sheet12.Range("B2", Sheet12.Range("B" & Rows.Count).End(xlUp)).Copy
 Sheet19.Range("Ai1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Sheet19.Range("Ai2", Sheet19.Range("Ai" & Rows.Count).End(xlUp)).RemoveDuplicates 1
 Sheet19.Range("AJ2:AJ" & Sheet19.Range("AI" & Rows.Count).End(xlUp).Row).Value = Date

 Sheet13.Range("B2", Sheet13.Range("B" & Rows.Count).End(xlUp)).Copy
 Sheet19.Range("Ak1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Sheet19.Range("Ak2", Sheet19.Range("Ak" & Rows.Count).End(xlUp)).RemoveDuplicates 1
 Sheet19.Range("AL2:AL" & Sheet19.Range("AK" & Rows.Count).End(xlUp).Row).Value = Date

 Sheet14.Range("H2", Sheet14.Range("H" & Rows.Count).End(xlUp)).Copy
 Sheet19.Range("Am1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Sheet19.Range("Am2", Sheet19.Range("Am" & Rows.Count).End(xlUp)).RemoveDuplicates 1
 Sheet19.Range("AN2:AN" & Sheet19.Range("AM" & Rows.Count).End(xlUp).Row).Value = Date
 Sheet14.Range("I2", Sheet14.Range("I" & Rows.Count).End(xlUp)).Copy
 Sheet19.Range("Ao1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Sheet19.Range("Ao2", Sheet19.Range("Ao" & Rows.Count).End(xlUp)).RemoveDuplicates 1
 Sheet19.Range("AP2:AP" & Sheet19.Range("AO" & Rows.Count).End(xlUp).Row).Value = Date
 
 Sheet15.Range("B2", Sheet15.Range("B" & Rows.Count).End(xlUp)).Copy
 Sheet19.Range("Aq1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Sheet19.Range("Aq2", Sheet19.Range("Aq" & Rows.Count).End(xlUp)).RemoveDuplicates 1
 Sheet19.Range("AR2:AR" & Sheet19.Range("AQ" & Rows.Count).End(xlUp).Row).Value = Date
 Sheet15.Range("C2", Sheet15.Range("C" & Rows.Count).End(xlUp)).Copy
 Sheet19.Range("As1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Sheet19.Range("As2", Sheet19.Range("As" & Rows.Count).End(xlUp)).RemoveDuplicates 1
 Sheet19.Range("AT2:AT" & Sheet19.Range("AS" & Rows.Count).End(xlUp).Row).Value = Date
 
 Sheet16.Range("K2", Sheet16.Range("K" & Rows.Count).End(xlUp)).Copy
 Sheet19.Range("Au1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Sheet19.Range("Au2", Sheet19.Range("Au" & Rows.Count).End(xlUp)).RemoveDuplicates 1
 Sheet19.Range("AV2:AV" & Sheet19.Range("AU" & Rows.Count).End(xlUp).Row).Value = Date
 Sheet16.Range("L2", Sheet16.Range("L" & Rows.Count).End(xlUp)).Copy
 Sheet19.Range("Aw1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Sheet19.Range("Aw2", Sheet19.Range("Aw" & Rows.Count).End(xlUp)).RemoveDuplicates 1
 Sheet19.Range("AX2:AX" & Sheet19.Range("AW" & Rows.Count).End(xlUp).Row).Value = Date
 Sheet16.Range("M2", Sheet16.Range("M" & Rows.Count).End(xlUp)).Copy
 Sheet19.Range("Ay1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Sheet19.Range("Ay2", Sheet19.Range("Ay" & Rows.Count).End(xlUp)).RemoveDuplicates 1
 Sheet19.Range("AZ2:AZ" & Sheet19.Range("AY" & Rows.Count).End(xlUp).Row).Value = Date
 
 Sheet17.Range("B2", Sheet17.Range("B" & Rows.Count).End(xlUp)).Copy
 Sheet19.Range("ba1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Sheet19.Range("ba2", Sheet19.Range("ba" & Rows.Count).End(xlUp)).RemoveDuplicates 1
 Sheet19.Range("BB2:BB" & Sheet19.Range("BA" & Rows.Count).End(xlUp).Row).Value = Date
 Sheet17.Range("C2", Sheet17.Range("C" & Rows.Count).End(xlUp)).Copy
 Sheet19.Range("bc1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Sheet19.Range("bc2", Sheet19.Range("bc" & Rows.Count).End(xlUp)).RemoveDuplicates 1
 Sheet19.Range("BD2:BD" & Sheet19.Range("AC" & Rows.Count).End(xlUp).Row).Value = Date
End Sub

 

[johnnyL]

@PuntingJawa It looks like you have separated the code sections into two different places. Put them all in one place, otherwise you are likely to get the "object" error.

 

[PuntingJawa]

@PuntingJawa It looks like you have separated the code sections into two different places. Put them all in one place, otherwise you are likely to get the "object" error.

When placed in module or thisworkbook

Debug

 

[johnnyL]

@PuntingJawa The module doesn't go in the SN Log.xlsm, it goes in the other one. The 'Subscript out of range' is occurring because it doesn't see the worksheets being referenced.
Sorry, I should have caught that from your previous picture.

 

[PuntingJawa]

@PuntingJawa The module doesn't go in the SN Log.xlsm, it goes in the other one. The 'Subscript out of range' is occurring because it doesn't see the worksheets being referenced.

I see. Well, I am home from work now but Monday I'll test it out and see what happens. I didn't bring my backup copy home with me and the one I have here is severely outdated (ie sheets separated into two databases so not the same format). Sorry for my ignorance on what goes where. I'm getting pretty good with formulas but have a lot to be desired on the VBA understanding part. From what I see it's very similar to javascript which I haven't touched since I was in middle school.

 

[PuntingJawa]

Made a module in op aux and getting the same thing

 

[johnnyL]

If you are getting the 'subscript out of range' error it's probably the sheet names. Replace the main code with the following:

Private Sub Worksheet_Activate_V2()
'
'    Dim wsCopy      As Worksheet
'    Dim wsDest      As Worksheet
'
'    Set wsCopy = Workbooks("OP-AUX Database Test(MacroEnabled).xlsm").Worksheets("Sheet19")
'    Set wsDest = Workbooks("SN Log.xlsm").Worksheets("Sheet1")
'
'
    SheetToCopyTo = "Sheet19"
'
'
    SheetToCopyFrom = "OP1": ColumnToCopyFrom = "K": ColumnToCopyTo = "A": DateColumn = "B"  '-- OP1 Sheet2 to Sheet19 Pt. 1
    Call CopyPasteEtc
'
    SheetToCopyFrom = "OP1": ColumnToCopyFrom = "L": ColumnToCopyTo = "C": DateColumn = "D"  '-- OP1 Sheet2 to Sheet19 Pt. 2
    Call CopyPasteEtc
'
    SheetToCopyFrom = "OP3": ColumnToCopyFrom = "B": ColumnToCopyTo = "E": DateColumn = "F"  '-- OP3 Sheet3 to Sheet19 Pt. 1
    Call CopyPasteEtc
'
    SheetToCopyFrom = "OP3": ColumnToCopyFrom = "C": ColumnToCopyTo = "G": DateColumn = "H"  '-- OP3 Sheet3 to Sheet19 Pt. 2
    Call CopyPasteEtc
'
    SheetToCopyFrom = "OP4": ColumnToCopyFrom = "B": ColumnToCopyTo = "I": DateColumn = "J"  '-- OP4 Sheet4 to Sheet19 Pt. 1
    Call CopyPasteEtc
'
    SheetToCopyFrom = "OP4": ColumnToCopyFrom = "C": ColumnToCopyTo = "K": DateColumn = "L"  '-- OP4 Sheet4 to Sheet19 Pt. 2
    Call CopyPasteEtc
'
    SheetToCopyFrom = "OP5": ColumnToCopyFrom = "B": ColumnToCopyTo = "M": DateColumn = "N"  '-- OP5 Sheet5 to Sheet19
    Call CopyPasteEtc
'
    SheetToCopyFrom = "OP6": ColumnToCopyFrom = "B": ColumnToCopyTo = "O": DateColumn = "P"  '-- OP6 Sheet6 to Sheet19 Pt. 1
    Call CopyPasteEtc
'
    SheetToCopyFrom = "OP6": ColumnToCopyFrom = "C": ColumnToCopyTo = "Q": DateColumn = "R"  '-- OP6 Sheet6 to Sheet19 Pt. 2
    Call CopyPasteEtc
'
    SheetToCopyFrom = "OP7": ColumnToCopyFrom = "B": ColumnToCopyTo = "S": DateColumn = "T"  '-- OP7 Sheet7 to Sheet19 Pt. 1
    Call CopyPasteEtc
'
    SheetToCopyFrom = "OP7": ColumnToCopyFrom = "C": ColumnToCopyTo = "U": DateColumn = "V"  '-- OP7 Sheet7 to Sheet19 Pt. 2
    Call CopyPasteEtc
'
    SheetToCopyFrom = "OP8": ColumnToCopyFrom = "B": ColumnToCopyTo = "W": DateColumn = "X"  '-- OP8 Sheet8 to Sheet19 Pt. 1
    Call CopyPasteEtc
'
    SheetToCopyFrom = "OP8": ColumnToCopyFrom = "C": ColumnToCopyTo = "Y": DateColumn = "Z"  '-- OP8 Sheet8 to Sheet19 Pt. 2
    Call CopyPasteEtc
'
    SheetToCopyFrom = "OP9": ColumnToCopyFrom = "B": ColumnToCopyTo = "AA": DateColumn = "AB"    '-- OP9 Sheet9 to Sheet19 Pt. 1
    Call CopyPasteEtc
'
    SheetToCopyFrom = "OP9": ColumnToCopyFrom = "C": ColumnToCopyTo = "AC": DateColumn = "AD"    '-- OP9 Sheet9 to Sheet19 Pt. 2
    Call CopyPasteEtc
'
    SheetToCopyFrom = "AUX CK-EXT": ColumnToCopyFrom = "B": ColumnToCopyTo = "AE": DateColumn = "AF"   '-- AUX CK-EXT Sheet10 to Sheet19
    Call CopyPasteEtc
'
    SheetToCopyFrom = "AUX CK-INT": ColumnToCopyFrom = "B": ColumnToCopyTo = "AG": DateColumn = "AH"   '-- AUX CK-INT Sheet11 to Sheet19
    Call CopyPasteEtc
'
    SheetToCopyFrom = "AUX EDO": ColumnToCopyFrom = "B": ColumnToCopyTo = "AI": DateColumn = "AJ"   '-- AUX EDO Sheet12 to Sheet19
    Call CopyPasteEtc
'
    SheetToCopyFrom = "AUX GL-EXT": ColumnToCopyFrom = "B": ColumnToCopyTo = "AK": DateColumn = "AL"   '-- AUX GL-EXT Sheet13 to Sheet19
    Call CopyPasteEtc
'
    SheetToCopyFrom = "AUX GL-INT": ColumnToCopyFrom = "H": ColumnToCopyTo = "AM": DateColumn = "AN"   '-- AUX GL-INT Sheet14 to Sheet19 Pt. 1
    Call CopyPasteEtc
'
    SheetToCopyFrom = "AUX GL-INT": ColumnToCopyFrom = "I": ColumnToCopyTo = "AO": DateColumn = "AP"   '-- AUX GL-INT Sheet14 to Sheet19 Pt. 2
    Call CopyPasteEtc
'
    SheetToCopyFrom = "AUX MDC+SPEC": ColumnToCopyFrom = "B": ColumnToCopyTo = "AQ": DateColumn = "AR"   '-- AUX MDC+SPEC Sheet15 to Sheet19 Pt. 1
    Call CopyPasteEtc
'
    SheetToCopyFrom = "AUX MDC+SPEC": ColumnToCopyFrom = "C": ColumnToCopyTo = "AS": DateColumn = "AT"   '-- AUX MDC+SPEC Sheet15 to Sheet19 Pt. 2
    Call CopyPasteEtc
'
    SheetToCopyFrom = "AUX MLS+MKS": ColumnToCopyFrom = "K": ColumnToCopyTo = "AU": DateColumn = "AV"   '-- AUX MLS+MKS Sheet16 to Sheet19 Pt. 1
    Call CopyPasteEtc
'
    SheetToCopyFrom = "AUX MLS+MKS": ColumnToCopyFrom = "L": ColumnToCopyTo = "AW": DateColumn = "AX"   '-- AUX MLS+MKS Sheet16 to Sheet19 Pt. 2
    Call CopyPasteEtc
'
    SheetToCopyFrom = "AUX MLS+MKS": ColumnToCopyFrom = "M": ColumnToCopyTo = "AY": DateColumn = "AZ"   '-- AUX MLS+MKS Sheet16 to Sheet19 Pt. 3
    Call CopyPasteEtc
'
    SheetToCopyFrom = "AUX SLRU+DMCU": ColumnToCopyFrom = "B": ColumnToCopyTo = "BA": DateColumn = "BB"   '-- AUX SLRU+DMCU Sheet17 to Sheet19 Pt. 1
    Call CopyPasteEtc
'
    SheetToCopyFrom = "AUX SLRU+DMCU": ColumnToCopyFrom = "C": ColumnToCopyTo = "BC": DateColumn = "BD"   '-- AUX SLRU+DMCU Sheet17 to Sheet19 Pt. 2
    Call CopyPasteEtc
End Sub

I think I replaced all the sheet names properly. Double check the last two (sheet17) because that part was cut off in your picture. You may have to replace Sheet19 at the top of the code if you have renamed Sheet19. I didn't see Sheet19 listed in your pic.

Try that and let us know.

 

[PuntingJawa]

That's odd. It's showing in mine. Here's a new SS with everything. I'm not 100% sure why it's not working between certain ones.

 

[johnnyL]

Change

SheetToCopyTo = "Sheet19"

To

SheetToCopyTo = "LOG"

 

[PuntingJawa]

Change

SheetToCopyTo = "Sheet19"

To

SheetToCopyTo = "LOG"

Same thing. Although, now it's back to highlighting

    Sheets(SheetToCopyFrom).Range(ColumnToCopyFrom & "2", Sheets(SheetToCopyFrom).Range(ColumnToCopyFrom & Rows.Count).End(xlUp)).Copy  ' Copy Column Data to Clipboard

 

[johnnyL]

Did it copy anything to sheet19 (LOG)?