I have multiple worksheets about 51 that has so many different number of rows
in each sheet that I want to copy the first 4 colums and paste them into a
new worksheet, but not side by side I would like to stack all copied rows and
columns on top of each other in the new worksheet. for example:


In the first worksheet I have.
A B C D
1 2 3 4

I dont know if thats possible without using Visual B.
Are u familiar a little with VBA???

"sam81" wrote:

I have multiple worksheets about 51 that has so many different number of rows
in each sheet that I want to copy the first 4 colums and paste them into a
new worksheet, but not side by side I would like to stack all copied rows and
columns on top of each other in the new worksheet. for example:


In the first worksheet I have.
A B C D
1 2 3 4
.
.


"second worksheet "
A B C D
5 6 7 8
.
.

"third worksheet"
A B C D
9 10 11 12
.
.

And so on




In the new worksheet I would like to have.

A B C D
1 2 3 4
.
.
5 6 7 8
.
.
9 10 11 12
.
.

In each sheet I have many rows

Plese help.
Thank you.

I have this code but I get an error about Lastrow

Sub Test2()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim shLast As Long

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

'Delete the sheet "MergeSheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("MergeSheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "MergeSheet"
Set DestSh = ThisWorkbook.Worksheets.Add
DestSh.Name = "MergeSheet"

'loop through all worksheets and copy the data to the DestSh
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
sh.Range(sh.Rows(2), sh.Rows(shLast)).Copy DestSh.Cells(Last + 1,
"A")

End If
Next

Application.Goto DestSh.Cells(1)

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub




N.F wrote:
I dont know if thats possible without using Visual B.
Are u familiar a little with VBA???

I have multiple worksheets about 51 that has so many different number of rows
in each sheet that I want to copy the first 4 colums and paste them into a
[quoted text clipped - 38 lines]
Plese help.
Thank you.