Combine multiple worksheets in one Workbook into one big worksheet


S

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
 
Ad

Advertisements

D

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

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

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
 
D

Don Guillett Excel MVP

You apparently have sheet 9 with NO data. So add

on error resume next

before the line with
for
 
S

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.
 
Ad

Advertisements

D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top