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" <(E-Mail Removed)> wrote in message news:B0CCB0CD-5429-42F4-B9DC-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message news4A2B18D-E77D-4118-8523-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message news4A2B18D-E77D-4118-8523-(E-Mail Removed)...
> > 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 <P...@discussions.microsoft.com> 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" <P...@discussions.microsoft.com> wrote in messagenews4A2B18D-E77D-4118-8523-(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message news:AD1D61A8-A643-4AB4-8949-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message news4A2B18D-E77D-4118-8523-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message news:AD1D61A8-A643-4AB4-8949-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message news4A2B18D-E77D-4118-8523-(E-Mail Removed)...
> >> > 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
> >>
>

Why not use this

Dim wb As Workbook
Sheets("MergeSheet").Copy
Set wb = ActiveWorkbook

You have a new workbook now with only this sheet.

And you can use wb.SaveAs.............. now to save the file



--

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


"PatK" <(E-Mail Removed)> wrote in message news:35246B36-0723-402B-87E3-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message news:AD1D61A8-A643-4AB4-8949-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message news4A2B18D-E77D-4118-8523-(E-Mail Removed)...
>> >> > 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
>> >>
>>

Well...that certainly cut back on the code, but HOW do you learn these
different things like " Sheets("MergeSheet").Copy" . How does a noob look
up to know that you can do a "sheets.copy" function if you don't know what to
look for. I have 4 Excel VBA books, and they all presume you simply have
this knowledge. Any pointers to resurces you would suggest? By the way,
here is how the code ended up...thanks again!

Sub CopySheetRoutine(Foldername As String)

Dim FileExtStr As String 'used
Dim FileFormatNum As Long 'used
Dim Destwb As Workbook

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

Sheets("MergeSheet").Copy
Set Destwb = ActiveWorkbook

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

With Destwb
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

"Ron de Bruin" wrote:

> Why not use this
>
> Dim wb As Workbook
> Sheets("MergeSheet").Copy
> Set wb = ActiveWorkbook
>
> You have a new workbook now with only this sheet.
>
> And you can use wb.SaveAs.............. now to save the file
>
>
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
> "PatK" <(E-Mail Removed)> wrote in message news:35246B36-0723-402B-87E3-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message news:AD1D61A8-A643-4AB4-8949-(E-Mail Removed)...
> >> > 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" <(E-Mail Removed)> wrote in message news4A2B18D-E77D-4118-8523-(E-Mail Removed)...
> >> >> > 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
> >> >>
> >>
>