I have worksheets named 1 through 21 that are pulling data from several other worksheets in the same file. I need a way to copy A1:C58 of each of these numbered sheets (but only these) and paste the ranges vertically on an existing sheet named "Save As DIF".
I've searched for this, but everything I've seen has been copying every sheet in the workbook. Thanks!
Re: Copy same range from certain worksheets and paste to one sheet
This might help, two methods
1st Method: -
Copies from a range and then pastes it to sheet1, first 4 lines, first bit of code copies from sheet 2 and pastes into first 4 rows of sheet1, the second bit of code copies from sheet3 and pastes into Sheet1 A5 the next 4 rows. If you go with this method, then you will need to do this for all your sheets. e.g sheet4 will paste into Sheet1 A9.
I have set the copy range of 4 rows from the other sheets, yours will differ
Private Sub CommandButton1_Click()
'Copies range from sheet2 to sheet1
Worksheets("Sheet2").Range("A1:P4").Copy _
Destination:=Worksheets("Sheet1").Range("A1")
'Copies range from sheet3 and pastes in sheet1 A5
Worksheets("Sheet3").Range("A1:P4").Copy _
Destination:=Worksheets("Sheet1").Range("A5")
End Sub2nd Method:-
Second method is the same apart from it finds the next blank row to paste into for Sheet1, if your data has to be in some order i.e sheet 10 data is shown before the data of sheet3 has been copied and pasted then the code will look like the one in the last bit of code. As this is how the code will excute.
Private Sub CommandButton1_Click()
'Copies range from sheet2 to sheet1 NEXT BLANK ROW
Worksheets("Sheet2").Range("A1:P4").Copy _
Destination:=Worksheets("Sheet1").Range("A65536").End(xlUp).Offset(1)
'Copies range from sheet3 and pastes in sheet1 NEXT BLANK ROW
Worksheets("Sheet3").Range("A1:P4").Copy _
Destination:=Worksheets("Sheet1").Range("A65536").End(xlUp).Offset(1)
End Sub
This bit is info only
Private Sub CommandButton1_Click()
'Copies range from sheet2 to sheet1 NEXT BLANK ROW
Worksheets("Sheet2").Range("A1:P4").Copy _
Destination:=Worksheets("Sheet1").Range("A65536").End(xlUp).Offset(1)
'[B]Copies range from sheet10 and pastes in sheet1 NEXT BLANK ROW
Worksheets("Sheet10").Range("A1:P4").Copy _
Destination:=Worksheets("Sheet1").Range("A65536").End(xlUp).Offset(1)
End Sub[/B]
'Copies range from [B]sheet3[/B] and pastes in sheet1 NEXT BLANK ROW
Worksheets("[B]Sheet3[/B]").Range("A1:P4").Copy _
Destination:=Worksheets("Sheet1").Range("A65536").End(xlUp).Offset(1)
End Subhope this helps
Re: Copy same range from certain worksheets and paste to one sheet
I think the 2nd one works best for me. Thanks!
Don’t have an account yet? Register yourself now and be a part of our community!