CODEWITHSUNDEEP
vbaexcel
Jay Sterling
Jay Sterling

Reputation: 19

Copy range from multiple sheets rather than just one sheet

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

Upvotes: 0

Views: 1349

Answers (3)

idktho
idktho

Reputation: 109

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

Upvotes: 0

qz_99
qz_99

Reputation: 175

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>")

Upvotes: 1

Kiralancer
Kiralancer

Reputation: 51

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

Upvotes: 1

Related Questions