Copy data from all worksheets into one but not below but sideways

[Stormrider2230]

Hello.
How to copy data from all worksheets in a workbook, into a new single worksheet, creating a new worksheet named "Combined" but not adding them below into same columns (last used row +1), but to the side. First data from the first copied worksheet into "Combined" shoud go into A1, then next worksheet to the last used column +1 to the right side, and looping across all ather worksheets and pasting data like mentioned....to the last used column +1.
There can be two or more worksheets, they can have different names, length of rows and columns also varies, so generic formula should be used.
I tried to adjust some code from the example below but no luck with adjusting Selection.Copy Destination.

Dim Sun As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Combined"
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
For Sun = 2 To Sheets.Count
Sheets(Sun).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End sub

Will appreciate any help.
Thanks and regards,

Rob

 

[Joe4]

If you want to put it in the next available column in row 1, you could do something like:

Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).Select

 

[Stormrider2230]

I tried, but I don't understand VBA enough to incorporate it into the code above.

 

[Joe4]

If you would like more help, can you show us an example of what your data looks like and your desired output?
I think we may be able to clean up the code even more if we have a clearer idea of your data structure.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.