Copy all data from multiple sheets into one new sheet

Question

Here is my situation: I want to copy tables from multiple excel sheets and combine it into one new sheet.The macro I have thus far does select the tables, and does create a new sheet to combine the data, HOWEVER it does not select the last row of the tables when combining. Thanks for the help:

 Sub Trytocombine()

 Dim J As Integer


On Error Resume Next
Sheets(1).Select
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "For Drafting"

' copy headings
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.CurrentRegion.Select
Selection.Copy Destination:=Sheets(1).Range("A1")

' work through sheets
For J = 2 To Sheets.Count ' from sheet 2 to last sheet
    Sheets(J).Activate ' make the sheet active
    Range("A1").Select
    Selection.CurrentRegion.Select ' select all cells in this sheets

    ' select all lines except title
    Selection.Offset(0, 0).Resize(Selection.Rows.Count - 1).Select

    ' copy cells selected in the new sheet on last line
    Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub

I don't usually use "Offset" and "Selection" and similar things, but I'm guessing the "Selection.Offset(0, 0)" should be "Selection.Offset(1, 0)". As it currently is, I **think** it is copying the first row to the last row - 1. (But Scott's method is a much more preferred way of doing things.) — YowE3K, Jul 18 '16 at 19:56

score 1 · Answer 1 · edited May 23 '17 at 12:14

1

Refactored to avoid select (and copy after last row):

Sub Combine()

Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "For Drafting"

' copy headings
Sheets(1).Range("A1").EntireRow.Value = Sheets(2).Range("A1").EntireRow.Value 'not most effecient, but will do for this

' work through sheets
Dim J As Integer
For J = 2 To Sheets.Count ' from sheet 2 to last sheet
    With Sheets(J)
        .Range(.Cells(2,1),.Cells(.Range("A" & .Rows.Count).End(xlUp).Row,.Cells(2,.Columns.Count).End(xlToLeft).Column)).Copy _ 
             Sheets(1).Range("A" & Rows.Count).End(xlUp).Offset(2)
    End With
Next

End Sub

edited May 23 '17 at 12:14

Community

1
1

answered Jul 18 '16 at 19:37

Scott Holtzman

27,099
5
37
72

I get an error on your copy headings task: Object does not support this method or property – matt rashty Jul 18 '16 at 19:41
@mattrashty - try now. forgot range specification. – Scott Holtzman Jul 18 '16 at 19:43
Sorry Scott, I get the same error except on this line: Sheets(J).CurrentRegion.Resize(Sheets(J).CurrentRegion.Rows.Count - 1).Copy _ Sheets(1).Range("A" & Rows.Count).End(xlUp).Offset(1) – matt rashty Jul 18 '16 at 19:45
My original code works well with the exception of getting that darn last line in – matt rashty Jul 18 '16 at 19:47
try the new edit. I know the code ou have works good except the last line, but the code i wrote is way more sustainable going foward. – Scott Holtzman Jul 18 '16 at 19:51
@ScottHoltzman - There is a slight difference between what your code does and what the OP did - your code is selecting everything down to the last row in column A, whereas the OP was only selecting everything in the **CurrentRegion** starting at A1 (so you will sometimes, possibly, be copying more data than wanted) – YowE3K Jul 18 '16 at 20:03
good point @YowE3K - i assumed the data block was all contiguous on the sheet, but forgot to mention as such! – Scott Holtzman Jul 18 '16 at 20:06
@ScottHoltzman - A comment in the OP's code does say `select all cells in this sheets`, so he probably **intended** to copy the entire sheet, so what you have supplied is probably fixing another "bug" earlier rather than later – YowE3K Jul 18 '16 at 20:10
Your code still does not work unfortunately. I get an error on the .Range(.cells....) – matt rashty Jul 18 '16 at 20:14
@mattrashty - my bad. added the extra right paren and **fully** tested this time :) – Scott Holtzman Jul 18 '16 at 20:31
I regret to say...it only takes column A – matt rashty Jul 18 '16 at 20:47
@matt rashty - each sheet has headers right, in row 1? if not, you can play with the row to use where it grabs the last column by starting at the right-miost column and shifting `xlToLeft` back to the last column. It currently uses row 1 for this – Scott Holtzman Jul 18 '16 at 20:54
No, each sheet has one blank row at the top. The purpose of the blank row, is when I combine the tables onto one sheet, they have an emty row in between – matt rashty Jul 18 '16 at 20:55
1

at long last, it worked! I sincerely appreciate your help. Thank you scott – matt rashty Jul 18 '16 at 20:58
You show appreciation by selecting his answer as the right one and upvoting it. – Marko Bonaci Jul 17 '18 at 14:08

Copy all data from multiple sheets into one new sheet

1 Answers1