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]

ok, no worries, let us know.

 

[PuntingJawa]

ok, no worries, let us know.

I had a moment to try it out. I could just be exhausted but I can't seem to get it to function. I'm 100% certain at this point that it's all human error on my part. I'd hate to ask but is there any way I could get a full copy of the workbook that you created for this? I am certain I am just putting things in the wrong spot.

 

[johnnyL]

I'll set up a click button for you. Which sheet and which cell would you like a click button?

 

[PuntingJawa]

I'll set up a click button for you. Which sheet and which cell would you like a click button?

Page 1 cell V1 V2ish? Thanks so much for your help. I know it's been chaotic.

 

[johnnyL]

Are you referring to Sheet1(Main) in "OP-AUX Database Test(MacroEnabled).xlsm"

 

[PuntingJawa]

Are you referring to Sheet1(Main) in "OP-AUX Database Test(MacroEnabled).xlsm"

Yeah, sorry. I should have specified.

 

[johnnyL]

LinkToFile

 

[PuntingJawa]

LinkToFile

Your file works great. I haven't implemented it fully yet (A lot changed in this insane month and I'll have to rewrite the whole database) but I can definitely use this. Thank you so much for all you help on this. I also appreciate your patience with me.

 

[johnnyL]

Your welcome.