I have a workbook with, say, 5 sheets (could be variable numbers of sheets)
all with the the EXACT same structure (column headers), but with vary data
and numbers of rows. I would like to automate the process of concatenating
all the sheets into one "massive" worksheet. At that time, I will be doing a
lot of other "stuff" to that massive sheet, but I need to get it there,
first.

Any ideas on a simple way to concatenate a "variable" number of worksheets
in a workbook, into either a new workbook, or, simply another worksheet (n+1
worksheets, where n= the original number of worksheets) in the same workbook?

Probably simple, but not for a noob like me. Thanks!

Pat

Start here Pat
http://www.rondebruin.nl/copy2.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"PatK" wrote in message ...
I have a workbook with, say, 5 sheets (could be variable numbers of sheets)
all with the the EXACT same structure (column headers), but with vary data
and numbers of rows. I would like to automate the process of concatenating
all the sheets into one "massive" worksheet. At that time, I will be doing a
lot of other "stuff" to that massive sheet, but I need to get it there,
first.

Any ideas on a simple way to concatenate a "variable" number of worksheets
in a workbook, into either a new workbook, or, simply another worksheet (n+1
worksheets, where n= the original number of worksheets) in the same workbook?

Probably simple, but not for a noob like me. Thanks!

Pat

This looks like it should work great. I am getting an error, however, I
believe on the lastrow function (see below). Am I missing something?

For Each sh In ThisWorkbook.Worksheets
If sh.Name < DestSh.Name Then
Last = lastrow(DestSh) ' <-----error here on lastrow
shLast = lastrow(sh)

"PatK" wrote:

I have a workbook with, say, 5 sheets (could be variable numbers of sheets)
all with the the EXACT same structure (column headers), but with vary data
and numbers of rows. I would like to automate the process of concatenating
all the sheets into one "massive" worksheet. At that time, I will be doing a
lot of other "stuff" to that massive sheet, but I need to get it there,
first.

Any ideas on a simple way to concatenate a "variable" number of worksheets
in a workbook, into either a new workbook, or, simply another worksheet (n+1
worksheets, where n= the original number of worksheets) in the same workbook?

Probably simple, but not for a noob like me. Thanks!

Pat

You must copy the lastrow function also in the module


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"PatK" wrote in message ...
This looks like it should work great. I am getting an error, however, I
believe on the lastrow function (see below). Am I missing something?

For Each sh In ThisWorkbook.Worksheets
If sh.Name < DestSh.Name Then
Last = lastrow(DestSh) ' <-----error here on lastrow
shLast = lastrow(sh)

"PatK" wrote:

I have a workbook with, say, 5 sheets (could be variable numbers of sheets)
all with the the EXACT same structure (column headers), but with vary data
and numbers of rows. I would like to automate the process of concatenating
all the sheets into one "massive" worksheet. At that time, I will be doing a
lot of other "stuff" to that massive sheet, but I need to get it there,
first.

Any ideas on a simple way to concatenate a "variable" number of worksheets
in a workbook, into either a new workbook, or, simply another worksheet (n+1
worksheets, where n= the original number of worksheets) in the same workbook?

Probably simple, but not for a noob like me. Thanks!

Pat

Works wonderfully well (now that I read the ENTIRE document!) :-) And fast,
too! What I need to do next is figure out how to get all the rows, including
the header from the first page, then just the data from all the rest so I
have one set of headers. I am using your "copy from row 2...." approach. I
think I can figure out the rest, using each example. Thanks so much!

pat

"Ron de Bruin" wrote:

You must copy the lastrow function also in the module


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"PatK" wrote in message ...
This looks like it should work great. I am getting an error, however, I
believe on the lastrow function (see below). Am I missing something?

For Each sh In ThisWorkbook.Worksheets
If sh.Name < DestSh.Name Then
Last = lastrow(DestSh) ' <-----error here on lastrow
shLast = lastrow(sh)

"PatK" wrote:

I have a workbook with, say, 5 sheets (could be variable numbers of sheets)
all with the the EXACT same structure (column headers), but with vary data
and numbers of rows. I would like to automate the process of concatenating
all the sheets into one "massive" worksheet. At that time, I will be doing a
lot of other "stuff" to that massive sheet, but I need to get it there,
first.

Any ideas on a simple way to concatenate a "variable" number of worksheets
in a workbook, into either a new workbook, or, simply another worksheet (n+1
worksheets, where n= the original number of worksheets) in the same workbook?

Probably simple, but not for a noob like me. Thanks!

Pat

On Jul 26, 2:30 pm, PatK wrote:
Works wonderfully well (now that I read the ENTIRE document!) :-) And fast,
too! What I need to do next is figure out how to get all the rows, including
the header from the first page, then just the data from all the rest so I
have one set of headers. I am using your "copy from row 2...." approach. I
think I can figure out the rest, using each example. Thanks so much!

pat



"Ron de Bruin" wrote:
You must copy the lastrow function also in the module

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

"PatK" wrote in ...
This looks like it should work great. I am getting an error, however, I
believe on the lastrow function (see below). Am I missing something?

For Each sh In ThisWorkbook.Worksheets
If sh.Name < DestSh.Name Then
Last = lastrow(DestSh) ' <-----error here on lastrow
shLast = lastrow(sh)

"PatK" wrote:

I have a workbook with, say, 5 sheets (could be variable numbers of sheets)
all with the the EXACT same structure (column headers), but with vary data
and numbers of rows. I would like to automate the process of concatenating
all the sheets into one "massive" worksheet. At that time, I will be doing a
lot of other "stuff" to that massive sheet, but I need to get it there,
first.

Any ideas on a simple way to concatenate a "variable" number of worksheets
in a workbook, into either a new workbook, or, simply another worksheet (n+1
worksheets, where n= the original number of worksheets) in the same workbook?

Probably simple, but not for a noob like me. Thanks!

Pat- Hide quoted text -

- Show quoted text -

I have done some extensive customizing of Ron's excellent examples. I
have the same process fully automated now and it takes any number of
files with any names as long as at least one of the worksheets in each
file has the name and structure you want. I have a button that looks
to named range values to get the folder path and sheet name. Thanks
Ron for sharing your well commented tools. His Add-In will work for
you own use or you can customize the code to fit your needs and work
transparently.
Dennis

Hi Dennis,

Please could you share that code as i've basically pasted Ron's code and it
works wonderfully but the problem is i have multiple excel files...

Cheers in advance!

Wayne

"ssGuru" wrote:

On Jul 26, 2:30 pm, PatK wrote:
Works wonderfully well (now that I read the ENTIRE document!) :-) And fast,
too! What I need to do next is figure out how to get all the rows, including
the header from the first page, then just the data from all the rest so I
have one set of headers. I am using your "copy from row 2...." approach. I
think I can figure out the rest, using each example. Thanks so much!

pat



"Ron de Bruin" wrote:
You must copy the lastrow function also in the module

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

"PatK" wrote in ...
This looks like it should work great. I am getting an error, however, I
believe on the lastrow function (see below). Am I missing something?

For Each sh In ThisWorkbook.Worksheets
If sh.Name < DestSh.Name Then
Last = lastrow(DestSh) ' <-----error here on lastrow
shLast = lastrow(sh)

"PatK" wrote:

I have a workbook with, say, 5 sheets (could be variable numbers of sheets)
all with the the EXACT same structure (column headers), but with vary data
and numbers of rows. I would like to automate the process of concatenating
all the sheets into one "massive" worksheet. At that time, I will be doing a
lot of other "stuff" to that massive sheet, but I need to get it there,
first.

Any ideas on a simple way to concatenate a "variable" number of worksheets
in a workbook, into either a new workbook, or, simply another worksheet (n+1
worksheets, where n= the original number of worksheets) in the same workbook?

Probably simple, but not for a noob like me. Thanks!

Pat- Hide quoted text -

- Show quoted text -

I have done some extensive customizing of Ron's excellent examples. I
have the same process fully automated now and it takes any number of
files with any names as long as at least one of the worksheets in each
file has the name and structure you want. I have a button that looks
to named range values to get the folder path and sheet name. Thanks
Ron for sharing your well commented tools. His Add-In will work for
you own use or you can customize the code to fit your needs and work
transparently.
Dennis

Hi Pat

One way is to add this line to the macro before the loop start

'Add headers from row 1 of Sheet1
Worksheets(2).Rows(1).Copy DestSh.Cells(1)

'loop through all worksheets and copy the data to the DestSh
For Each sh In ThisWorkbook.Worksheets


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"PatK" wrote in message ...
Works wonderfully well (now that I read the ENTIRE document!) :-) And fast,
too! What I need to do next is figure out how to get all the rows, including
the header from the first page, then just the data from all the rest so I
have one set of headers. I am using your "copy from row 2...." approach. I
think I can figure out the rest, using each example. Thanks so much!

pat

"Ron de Bruin" wrote:

You must copy the lastrow function also in the module


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"PatK" wrote in message ...
This looks like it should work great. I am getting an error, however, I
believe on the lastrow function (see below). Am I missing something?

For Each sh In ThisWorkbook.Worksheets
If sh.Name < DestSh.Name Then
Last = lastrow(DestSh) ' <-----error here on lastrow
shLast = lastrow(sh)

"PatK" wrote:

I have a workbook with, say, 5 sheets (could be variable numbers of sheets)
all with the the EXACT same structure (column headers), but with vary data
and numbers of rows. I would like to automate the process of concatenating
all the sheets into one "massive" worksheet. At that time, I will be doing a
lot of other "stuff" to that massive sheet, but I need to get it there,
first.

Any ideas on a simple way to concatenate a "variable" number of worksheets
in a workbook, into either a new workbook, or, simply another worksheet (n+1
worksheets, where n= the original number of worksheets) in the same workbook?

Probably simple, but not for a noob like me. Thanks!

Pat

What I ultimately ended up doing was inserting these items into your code:

Dim FirstPage As Boolean
FirstPage = False

Then....down where you looped through the sheets, I made this mod....

For Each sh In ThisWorkbook.Worksheets

If sh.Name < DestSh.Name Then
Last = LastRow(DestSh)
shLast = LastRow(sh)

'This example copies everything, if you only want to copy
'values/formats look at the example below the first example
If Not FirstPage Then 'only copy headers from
first page
sh.Range(sh.Rows(1), sh.Rows(shLast)).Copy DestSh.Cells(Last
+ 1, "A")
FirstPage = True
Else
sh.Range(sh.Rows(2), sh.Rows(shLast)).Copy DestSh.Cells(Last
+ 1, "A")
End If
End If
Next

So, maybe a bit kludgy, but it worked. I do have another little problem,
tho, along the same lines. What I am doing, after creating the mergesheet,
is that I wish to copy JUST the mergesheet to a new file, then save that
file. However, when I save (using this code), I end up saving my SOURCE
file, and not the new file I just created. Here is the subroutine I am using
to do that, once the mergesheet is created:

Sub CopySheetRoutine(result As Boolean)

Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Sourcews As Worksheet
Dim Destwb As Workbook
Dim Destws As Worksheet
Dim DateString As String
Dim FolderName As String

With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With

Sheets("MergeSheet").Select
Cells.Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "All Tickets"
Sheets("All Tickets").Select
Application.CutCopyMode = False
Set Sourcewb = ActiveWorkbook.ActiveSheet '<this does not seem to work

'Create new folder to save the new files in
DateString = Format(Now, "yyyy-mm-dd hhmmss")
FolderName = Sourcewb.Path & "\" & Sourcewb.Name & " " & DateString
MkDir FolderName
FolderName = FolderName & "\"

MsgBox "Ticket File will be stored at: " & FolderName

With Sourcewb
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
End If
.SaveAs FolderName & .Sheets(1).Name & FileExtStr,
FileFormat:=FileFormatNum
.Close False
End With

With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With


End Sub


The SET command seems to set it to the original source workbook, not the one
just created, as I had thought it would. Any ideas how I can get Sourcewb to
be pointing at the new workbook instead of the "original" source? Also,
where does one learn about all these commands like "activatesheet.paste" etc?
It is like somehow you just magically need to know they exist. Thanks
again, Ron!

"Ron de Bruin" wrote:

Hi Pat

One way is to add this line to the macro before the loop start

'Add headers from row 1 of Sheet1
Worksheets(2).Rows(1).Copy DestSh.Cells(1)

'loop through all worksheets and copy the data to the DestSh
For Each sh In ThisWorkbook.Worksheets


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"PatK" wrote in message ...
Works wonderfully well (now that I read the ENTIRE document!) :-) And fast,
too! What I need to do next is figure out how to get all the rows, including
the header from the first page, then just the data from all the rest so I
have one set of headers. I am using your "copy from row 2...." approach. I
think I can figure out the rest, using each example. Thanks so much!

pat

"Ron de Bruin" wrote:

You must copy the lastrow function also in the module


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"PatK" wrote in message ...
This looks like it should work great. I am getting an error, however, I
believe on the lastrow function (see below). Am I missing something?

For Each sh In ThisWorkbook.Worksheets
If sh.Name < DestSh.Name Then
Last = lastrow(DestSh) ' <-----error here on lastrow
shLast = lastrow(sh)

"PatK" wrote:

I have a workbook with, say, 5 sheets (could be variable numbers of sheets)
all with the the EXACT same structure (column headers), but with vary data
and numbers of rows. I would like to automate the process of concatenating
all the sheets into one "massive" worksheet. At that time, I will be doing a
lot of other "stuff" to that massive sheet, but I need to get it there,
first.

Any ideas on a simple way to concatenate a "variable" number of worksheets
in a workbook, into either a new workbook, or, simply another worksheet (n+1
worksheets, where n= the original number of worksheets) in the same workbook?

Probably simple, but not for a noob like me. Thanks!

Pat