0

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
Community
  • 1
  • 1
Jay Sterling
  • 19
  • 7
  • 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 Jul 12 '17 at 15:14
  • 1
    [Don't use `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/). 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 Jul 12 '17 at 15:16
  • 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 Jul 12 '17 at 15:28

3 Answers3

1

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
Kiralancer
  • 51
  • 1
  • 7
  • Hi @Kiralancer , I tried this code that you have suggested, I get a error which says that 'subscript out of range' - Sub sbCopyRangeToAnotherSheet() Sheets("Source 1").Range("A1:J15").Copy Sheets("7").Range("A1") – Jay Sterling Jul 13 '17 at 07:35
  • What are the sheet names and ranges. The above ranges were just derived from yours. – Kiralancer Jul 16 '17 at 23:05
1

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>")
qz_99
  • 185
  • 1
  • 12
0

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
idktho
  • 109
  • 8
  • Hi @idktho I get the same error where it says "subscript out of range" for; Sheets("Source" & i).Range("A1:J15").Copy Sheets("Destination").Range("A" & Rows.Count).End(xlUp).Offset(1) The only thing i have changed is the range. – Jay Sterling Jul 13 '17 at 07:39
  • Hi @JaySterling , please check that the sheets name are correct and it exists, and it is the active workbook (close other workbooks just in case if you have it open). Also, is there a spacing between "Source" and "1" ? – idktho Jul 13 '17 at 07:54