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

Can you use Copy and Paste to the new sheet


" 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

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

There are about 20 to 25 worksheets.

What do you mean by pastspeacial values?

I did copy it in the vb and run but didn;t get correct results.

Any advice.

thanks
Martin Fishlock wrote:
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