Copy range from multiple sheets rather than just one sheet

0
votes

Background

I have a workbook with 7 sheets but only 6 with data. All of these 7 sheets have the same structure/headings, it's just that the last sheet (sheet 7) is blank.

I want to write a code that will copy the contents of sheets 1-6 in the range of A2:J15 into the sheet 7 worksheet. I currently have a code that I have been using to test and see if it works but the code I have only copies and pastes from one worksheet only (see below). Any suggestions?

In the below, I have two sheets where I want the data to come from and the destination sheet where I want the data to go:

Sub sbCopyRangeToAnotherSheet()
Sheets("Source1").Range("A1:B10").Copy
Sheets("Source2").Range("A1:B10").Copy
Sheets("Destination").Activate
Range("A1:B10").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
3
vbaexcel
Please provide images of the worksheets such that we can better understand their structure. This is indeed a doable task and is well suited for VBA. – JahKnows
Don't use .Select/.Activate. Instead, use worksheet variables to do it. Also, when you do that second .Copy, it is "overwriting" your first .Copy, effectively negating the need for that first line. Also, on your Sheet7, I assume you don't want to overwrite the data each time, but instead add the blocks of data from the other sheets to below the newely copied info? – BruceWayne
how can you copy the same range A2:J15 from all 6 sheets to the 7th sheet? Doing this would just make the 7th sheet to have the 6th sheets data – Gowtham Shiva

3 Answers

1
votes

Your problem is coming from your attempt to copy two items directly after each other. The second copy call is overwriting the data you copied in the first call.

Sheets("Source1").Range("A1:B10").Copy
Sheets("Destination").Activate
Range("A1:B10").Select
ActiveSheet.Paste

Sheets("Source2").Range("<your new range here>").Copy
Sheets("Destination").Activate
Range("<your new range here>").Select
ActiveSheet.Paste
Application.CutCopyMode = False

The code above should explain what I mean albeit not the most efficient way. A more effective way would be to use:

Sheets("Source1").Range("A1:B10").Copy Destination:=Sheets("Destination").Range("A1:B10")

Sheets("Source2").Range("A1:B10").Copy Destination:=Sheets("Destination").Range("<range>")
1
votes

As sugguested in the comments:

Sub sbCopyRangeToAnotherSheet()
     Sheets("Source1").Range("A1:B10").Copy Sheets("7").Range("A1")
     Sheets("Source2").Range("A1:B10").Copy Sheets("7").Range("A1").end(xlDown).offset(1,0)
     Sheets("Source3").Range("A1:B10").Copy Sheets("7").Range("A1").end(xlDown).offset(1,0)
     Sheets("Source4").Range("A1:B10").Copy Sheets("7").Range("A1").end(xlDown).offset(1,0)
     Sheets("Source5").Range("A1:B10").Copy Sheets("7").Range("A1").end(xlDown).offset(1,0)
     Sheets("Source6").Range("A1:B10").Copy Sheets("7").Range("A1").end(xlDown).offset(1,0)
End Sub
0
votes

Assuming that you want to paste the data by rows (and not overwrite it), and your sheets are named Source1 to Source6, then the following should work: 

Sub testSO()

For i = 1 To 6
    Sheets("Source" & i).Range("A1:B10").Copy Sheets("Destination").Range("A" & Rows.Count).End(xlUp).Offset(1)
Next i

End Sub