Combine multiple worksheets in one Workbook into one big worksheet

[S Commar]

I am exporting a report out of Crystal and its basically putting 65000
rows
in each worksheet before sending the rest of the rows into
additional
worksheets of 65000 rows each ( it seems to emulate the Excel 2003
limitation of rows per sheet.

Could someone assist me with a macro or other solution to automate
the
combining of multiple worksheets in a workbook into one big worksheet
in
Office 2007

Thanks very much

Sunny

 

[Don Guillett Excel MVP]

Should do it regardless of structure
Sub combinesheetsSAS()
Sheets(1).Select
For i = 2 To Sheets.Count
'MsgBox Sheets(i).Name
With Sheets(i)
la = .Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Address 'Row + 1
MsgBox la
lrd = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Row + 1
'MsgBox lrd
..Range("a1:" & la).Copy Cells(lrd, 1)
End With
Next i
End Sub

 

[S Commar]

Should do it regardless of structure
Sub combinesheetsSAS()
Sheets(1).Select
For i = 2 To Sheets.Count
'MsgBox Sheets(i).Name
With Sheets(i)
la = .Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Address 'Row + 1
MsgBox la
lrd = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Row + 1
'MsgBox lrd
.Range("a1:" & la).Copy Cells(lrd, 1)
End With
Next i
End Sub






- Show quoted text -

Thank so much. Really appreciate your help . Testing now

 

[S Commar]

Thank so much. Really appreciate your help . Testing now- Hide quoted text -

- Show quoted text -

Actually it bombed out on the 9th sheet with teh following message
Run Time error 91- Object or variable not set.
When i clicked on debug it showed the following code in yellow

la = .Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Address 'Row + 1

 

[Don Guillett Excel MVP]

You apparently have sheet 9 with NO data. So add

on error resume next

before the line with
for

 

[S Commar]

You apparently have sheet 9 with NO data. So add

on error resume next

before the line with
for






- Show quoted text -

Thanks very much. It is almost there. The last sheet which just had
61000 odd rows - it did not add these rows except the very end 10
lines from the last sheet.
Could you please assist.
Thanks so much again. I am very grateful for your help.

 

[Don Guillett Excel MVP]

Thanks very much. It is almost there. The last sheet which just had
61000 odd rows - it did not add these rows except the very end 10
lines from the last sheet.
Could you please assist.
Thanks so much again. I am very grateful for your help.- Hide quoted text-

- Show quoted text -

try it this way

Sub CopyCurrentRegionToMasterSheetSAS()
Sheets(1).Select
For i = 2 To Sheets.Count
lrd = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Row + 1
Sheets(i).UsedRange.Copy Cells(lrd, 1)
Application.CutCopyMode = False
MsgBox i
Next i
End Sub