VBA to Copy Identical Cells From Multiple Sheets into a Single Column on Another Sheet

Joined
Aug 15, 2019
Messages
12
Needing a macro to copy Cells J3:J20 on multiple sheets ('01','02',...'51') then pasted as values to Column A on a 'Matrix' sheet, starting in Cell A2; if possible, removing any duplicates.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
I probably took the long way around, but it works for what I need it to do. Putting this hear for anyone who might have a similar issue in the future.

Sub CopyPasteSortItemCodes()


'Clearing the contents of the target area

Sheets("Matrix").Range("A3:A" & Range("A3").End(xlDown).Row).ClearContents


'Copying data from first sheet and pasting to second as values only

Sheets("01").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").PasteSpecial Paste:=xlPasteValues
Sheets("02").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("03").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("04").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("05").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("06").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("07").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("08").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("09").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("10").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("11").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("12").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("13").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("14").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("15").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("16").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("17").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("18").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("19").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("20").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("21").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("22").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("23").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("24").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("25").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("26").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("27").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("28").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("29").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("30").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("31").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("32").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("33").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("34").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("35").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("36").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("37").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("38").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("39").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("40").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("41").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("42").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("43").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("44").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("45").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("46").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("47").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("48").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("49").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("50").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("51").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("52").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("53").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("54").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("55").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("56").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("57").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("58").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("59").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("60").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("61").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("62").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("63").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("64").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("65").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Sheets("66").Range("J4:J20").Copy
Sheets("Matrix").Range("A3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues

Dim MyRange As Range
Dim LastRow As Long


'Removing duplicates

LastRow = Sheets("Matrix").Range("A" & Rows.Count).End(xlUp).Row
Set MyRange = Sheets("Matrix").Range("A3:A" & LastRow)
MyRange.RemoveDuplicates Columns:=1, Header:=xlYes


'Sorting in ascending order

Sheets("Matrix").Range("A3:A1204", Range("A3:A1204").End(xlDown)).Sort Key1:=Range("A3:A1204"), Order1:=xlAscending, Header:=xlNo


'Reformatting target cells

Sheets("Matrix").Range("A2").Copy
Sheets("Matrix").Range("A2:A1204").PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False


'Here only to return to the top of the sheet once done

Sheets("Matrix").Range("A3").Copy
Sheets("Matrix").Range("A3").PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False

End Sub
 
Upvote 0
Welcome to the MrExcel board!
I probably took the long way around,....
For the copy part, try

Code:
Dim i As Long

For i = 1 To 66
  Sheets(Format(i, "00")).Range("J4:J20").Copy
  Sheets("Matrix").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
Next i
 
Last edited:
Upvote 0
Solution
Welcome to the MrExcel board!
For the copy part, try

Code:
Dim i As Long

For i = 1 To 66
  Sheets(Format(i, "00")).Range("J4:J20").Copy
  Sheets("Matrix").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
Next i

Same result, but much cleaner. Thank you! :)

- SI
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top