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" ha scritto nel
messaggio ...
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" ha scritto nel
messaggio ...
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" ha scritto nel
messaggio ...
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