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

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

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

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

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 -

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.

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