Combin / Merge Multiple Worksheets / sheets into one worksheet / s
Tarone,
Try this, you may need to consider pastespecial values:
Option Explicit
Sub makesummarycopy()
Dim lRow As Long ' start row for pasting
Dim lws As Long ' worksheet number
Dim wsNew As Worksheet ' summary worksheet
Dim ws As Worksheet ' worksheet copying
On Error GoTo Finished
Set wsNew = ActiveWorkbook.Worksheets.Add(Worksheets(1))
wsNew.Name = "Summary" ' name it
lRow = 1 ' start row for pasting
For lws = 2 To ActiveWorkbook.Worksheets.Count ' loop thru sheets
Set ws = ActiveWorkbook.Worksheets(lws)
wsNew.Cells(lRow, 1) = "Sheet: " & ws.Name 'insert title
lRow = lRow + 1
ws.UsedRange.Copy (wsNew.Cells(lRow, 1)) ' copy the used range
' may need to consider copy pastespecial....
lRow = lRow + ws.UsedRange.Rows.Count + 1 ' increment the rows
Next lws
Finished:
End Sub
--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.
" wrote:
I have several worksheet in one file
e.g
SHEET1
Name Number Field Qty Age
John 7878 8989 234 32
Peter 7877 23 9 NA
Mike 3433 343 --
Sheet2
Name Number Field Qty Age
Shart 343 343
William 323 52 5
I just want to combine into one worksheet as follows
New Sheet
Name Number Field Qty Age
John 7878 8989 234 32
Peter 7877 23 9 NA
Mike 3433 343 --
Name Number Field Qty Age
Shart 343 343
William 323 52 5
any advice
thanks
|