Help me to combine multiple sheet into single worksheet.

[sampath]

Hello,

I have en number of sheets in excel workbook. I need to merge all the sheets into single worksheet.

Herewith, I have attached the example file.kindly help me for the same.

Thanks with regards,
Sampath.S

 

[Somendra Misra]

Hi Sampath,

See the file.

Regards,

 

[Khalid NGO]

Hi Sampath,

Try the following formula in Output sheet:
=INDEX(INDIRECT("Sheet"&COLUMNS($A1:A1)&"!A1:C9"),ROW(),1)

Copy across and down.
Regards,

 

[Smallman]

Hi Sampath

Give the following a try.

Option Explicit

Sub MergeMe()
Dim i As Integer
    For i = 1 To Worksheets.Count - 1
        Sheets(i).[a1].CurrentRegion.Copy Sheet4.Range("IV1").End(xlToLeft)(, 2)
    Next i
End Sub

Take care

Smallman

 

[skyh3ck]

Hi Sampath,

See the file.

Regards,

i like what you did there, can you please explain, i know you used macro, but can you elaborate

 

[Somendra Misra]

@skyh3ck

Refer the comments in the code below:

Option Explicit

Sub test()

Dim lastRow As Long
Dim ws As Worksheet
Dim i As Long
Dim lastCol As Integer

Application.ScreenUpdating = False

' We have to Loop through all Worksheet Except Output Sheet. Below
'For loop will loop through all the sheets.
For i = 1 To ThisWorkbook.Worksheets.Count
    Set ws = ThisWorkbook.Sheets(i)    'This statement will assing the sheet to a variable
        If ws.Name <> "Output" Then  ' This If will check if the sheet is not Output.
            lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row  ' this statement will calculate the last row with data in column A as in the sample data was in column A.
            lastCol = Worksheets("Output").Cells(1, Columns.Count).End(xlToLeft).Column  ' this statement will check the last column with data in Output Sheet.
            If i = 1 Then ' if this is first sheet
                ws.Range("A1:A" & lastRow).Copy Worksheets("Output").Cells(1, lastCol) ' than copy and paste data to column A of Output sheet or
            Else
                ws.Range("A1:A" & lastRow).Copy Worksheets("Output").Cells(1, lastCol + 1) ' copy and paste data to column after the first column.
            End If
        End If
   
Next i
Application.ScreenUpdating = True
End Sub

If you still feel any specific problem than write back.

Regards,