Help me to combine multiple sheet into single worksheet.

[sampath]

Hello,

I have No. of sheets in excel workbook. and need to combine all sheets into one sheet in same workbook.

Am using below code to combine all sheets. but the result is all the sheets are combined single column in master sheet. Need to combine every sheet data into ever column of master sheet.

Sub Combine()
Dim J As Integer

On Error Resume Next
Sheets(1).Select
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "Combined"

' copy headings
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")

' work through sheets
For J = 2 To Sheets.Count ' from sheet 2 to last sheet
Sheets(J).Activate ' make the sheet active
Range("A1").Select
Selection.CurrentRegion.Select ' select all cells in this sheets

' select all lines except title
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

' copy cells selected in the new sheet on last line
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub


Kindly modify the code are provide the new code for my request.

Regards,
Sampath.S

 

[Khalid NGO]

Hi Sampath,

I think you have got solution in your previous thread:
http://chandoo.org/forum/threads/help-me-to-combine-multiple-sheet-into-single-worksheet.21168/

Regards,

 

[sampath]

Hi Khalid NGO,

In Previous thread, Mergeme code is working fine but every time have to change the sheet no when that workbook have huge data.

and test code is not working which was shown like "runtime error" and subscript out of range" message.

Kindly solve my request.

Regards,
Sampath.S