combine different worksheets to a single worksheet

Hi

I have many work sheets in the work book and need to on a number of
occasions create a single worksheet by appending one work sheet after the
other. Is it possible to do this with a function, or command? instead of
doing it manually by copying each of the worksheets to a single work sheet.

thanking you in advance

regards

norman

--
Message posted via http://www.officekb.com

If you need this just for printing, there'sno need to append all the
worksheets: just select all the tabs of the worksheets you want to print.
If for other reasons, you need to select the cell under last used cell in
column A1 in the first sheet and input = , select the cell A1(pay attention
to eliminate absolute reference, i.e. "$" signs) in your next sheet and
press ENTER; then you have to copy this formula to recover all the content
of the second sheet and then repeat for all the sheets you need. In this way
you make the job once, because each change in the sheets refleact in the
first one.

Hoping to be helpful

Franz


"Norman Kong via OfficeKB.com" <[email protected]> ha scritto nel
messaggio news:[email protected]...
> Hi
>
> I have many work sheets in the work book and need to on a number of
> occasions create a single worksheet by appending one work sheet after the
> other. Is it possible to do this with a function, or command? instead of
> doing it manually by copying each of the worksheets to a single work
> sheet.
>
> thanking you in advance
>
> regards
>
> norman
>
> --
> Message posted via http://www.officekb.com

Norman,

I asked the sdame question some time ago and the following macro was
provided to me.

Set newWks = Workbooks.Add(1).Worksheets(1)

HeadersAreDone = False

ActiveWorkbook.Unprotect

For Each wks In mySelectedSheets
With wks
If HeadersAreDone = True Then
'do nothing
Else
'for both rows 1 & 2, use .rows("1:2").copy _
'instead of the next line
.Rows(2).Copy _
Destination:=newWks.Range("a1")
HeadersAreDone = True
Set DestCell = newWks.Range("a2")
End If

.Range("a3", .Cells.SpecialCells(xlCellTypeLastCell)).Copy
DestCell.PasteSpecial Paste:=xlPasteValues
.Protect Password:=""

With newWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

End With
Next wks

Please note that all my sheets contained headers, to ensure I only had 1
header, the code caters for it.

HTH
"Franz" wrote:

> If you need this just for printing, there'sno need to append all the
> worksheets: just select all the tabs of the worksheets you want to print.
> If for other reasons, you need to select the cell under last used cell in
> column A1 in the first sheet and input = , select the cell A1(pay attention
> to eliminate absolute reference, i.e. "$" signs) in your next sheet and
> press ENTER; then you have to copy this formula to recover all the content
> of the second sheet and then repeat for all the sheets you need. In this way
> you make the job once, because each change in the sheets refleact in the
> first one.
>
> Hoping to be helpful
>
> Franz
>
>
> "Norman Kong via OfficeKB.com" <[email protected]> ha scritto nel
> messaggio news:[email protected]...
> > Hi
> >
> > I have many work sheets in the work book and need to on a number of
> > occasions create a single worksheet by appending one work sheet after the
> > other. Is it possible to do this with a function, or command? instead of
> > doing it manually by copying each of the worksheets to a single work
> > sheet.
> >
> > thanking you in advance
> >
> > regards
> >
> > norman
> >
> > --
> > Message posted via http://www.officekb.com
>
>
>

Norman,

I asked the sdame question some time ago and the following macro was
provided to me.

Set newWks = Workbooks.Add(1).Worksheets(1)

HeadersAreDone = False

ActiveWorkbook.Unprotect

For Each wks In mySelectedSheets
With wks
If HeadersAreDone = True Then
'do nothing
Else
'for both rows 1 & 2, use .rows("1:2").copy _
'instead of the next line
.Rows(2).Copy _
Destination:=newWks.Range("a1")
HeadersAreDone = True
Set DestCell = newWks.Range("a2")
End If

.Range("a3", .Cells.SpecialCells(xlCellTypeLastCell)).Copy
DestCell.PasteSpecial Paste:=xlPasteValues
.Protect Password:=""

With newWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

End With
Next wks

Please note that all my sheets contained headers, to ensure I only had 1
header, the code caters for it.

HTH
"Franz" wrote:

> If you need this just for printing, there'sno need to append all the
> worksheets: just select all the tabs of the worksheets you want to print.
> If for other reasons, you need to select the cell under last used cell in
> column A1 in the first sheet and input = , select the cell A1(pay attention
> to eliminate absolute reference, i.e. "$" signs) in your next sheet and
> press ENTER; then you have to copy this formula to recover all the content
> of the second sheet and then repeat for all the sheets you need. In this way
> you make the job once, because each change in the sheets refleact in the
> first one.
>
> Hoping to be helpful
>
> Franz
>
>
> "Norman Kong via OfficeKB.com" <[email protected]> ha scritto nel
> messaggio news:[email protected]...
> > Hi
> >
> > I have many work sheets in the work book and need to on a number of
> > occasions create a single worksheet by appending one work sheet after the
> > other. Is it possible to do this with a function, or command? instead of
> > doing it manually by copying each of the worksheets to a single work
> > sheet.
> >
> > thanking you in advance
> >
> > regards
> >
> > norman
> >
> > --
> > Message posted via http://www.officekb.com
>
>
>

Thank you, I will try it and give you feedback

Regards

Norman

--
Message posted via http://www.officekb.com