Combine multiple worksheets in one Workbook into one big worksheet
 

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

Combine multiple worksheets in one Workbook into one big worksheet
 

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

On Jan 22, 8:22*am, S Commar wrote:
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

Combine multiple worksheets in one Workbook into one big worksheet
 

On Jan 22, 10:01*am, Don Guillett Excel MVP
wrote:
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

On Jan 22, 8:22*am, S Commar wrote:



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- Hide quoted text -

- Show quoted text -

Thank so much. Really appreciate your help . Testing now

Combine multiple worksheets in one Workbook into one big worksheet
 

On Jan 22, 1:26*pm, S Commar wrote:
On Jan 22, 10:01*am, Don Guillett Excel MVP
wrote:





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

On Jan 22, 8:22*am, S Commar wrote:

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- Hide quoted text -

- Show quoted text -

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

Combine multiple worksheets in one Workbook into one big worksheet
 

You apparently have sheet 9 with NO data. So add

on error resume next

before the line with
for

On Jan 22, 3:23*pm, S Commar wrote:
On Jan 22, 1:26*pm, S Commar wrote:





On Jan 22, 10:01*am, Don Guillett Excel MVP
wrote:

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

On Jan 22, 8:22*am, S Commar wrote:

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- Hide quoted text -

- Show quoted text -

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- Hide quoted text -

- Show quoted text -

Combine multiple worksheets in one Workbook into one big worksheet
 

On Jan 22, 5:23*pm, Don Guillett Excel MVP
wrote:
You apparently have sheet 9 with NO data. So add

on error resume next

before the line with
for

On Jan 22, 3:23*pm, S Commar wrote:



On Jan 22, 1:26*pm, S Commar wrote:

On Jan 22, 10:01*am, Don Guillett Excel MVP
wrote:

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

On Jan 22, 8:22*am, S Commar wrote:

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- Hide quoted text -

- Show quoted text -

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- Hide quoted text -

- Show quoted text -- Hide quoted text -

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

Combine multiple worksheets in one Workbook into one big worksheet
 

On Jan 23, 6:54*pm, S Commar wrote:
On Jan 22, 5:23*pm, Don Guillett Excel MVP
wrote:





You apparently have sheet 9 with NO data. So add

on error resume next

before the line with
for

On Jan 22, 3:23*pm, S Commar wrote:

On Jan 22, 1:26*pm, S Commar wrote:

On Jan 22, 10:01*am, Don Guillett Excel MVP
wrote:

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

On Jan 22, 8:22*am, S Commar wrote:

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- Hide quoted text -

- Show quoted text -

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- Hide quoted text -

- Show quoted text -- Hide quoted text -

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