combining worksheets of same xls into a single worksheet

pavan.polish · #1 03-14-2018, 01:27 AM

Dear Friends,

I have a requirement to combine different worksheets of same xls into a single worksheet.

Assume this can be done using VB script and it will be great if you share the script for the same.

Thanks a lot in advance.

Regards,
Pavan

ArviLaanemets · #2 03-14-2018, 04:56 AM

Is this an one-time project? I.e. you get all data into single worksheet, and that's all. Further you use this workcheet, and previous ones are obsolete. When yes, then the simplest and fastest way will be:
1. Create a worksheet with table headers;
2. Copy data from 1st worksheet (except the header);
3. Paste the copied data into created sheet at 1st empty row;
Repeat p2. - p.3. for all worksheets you want to transfer data at;
When all data is transferred, delete old sheets.

When you want to keep the data entry on separate worksheets, and you want entered data automatically copied to summary sheet, then there is a question: Why?

Logit · #3 03-14-2018, 06:48 PM

.
Paste in Routine Module :

Code:

Option Explicit

Sub Maybe()
    Dim sh As Worksheet
    If Not [ISREF(Combined!A1)] Then
        Sheets.Add(, Sheets(Sheets.Count)).Name = "Combined"
    Else
        Sheets("Combined").UsedRange.EntireColumn.Delete
    End If
    'Sheets("Sheet2").Rows(1).Copy Sheets("Combined").Cells(1, 1)  ' Not needed
    For Each sh In ActiveWorkbook.Sheets
        If sh.Name <> "Combined" Then sh.UsedRange.Offset(1).Copy Sheets("Combined").Cells(Rows.Count, 1).End(xlUp).Offset(1)
    Next sh
End Sub

This macro creates a sheet named "Combined", then copies/pastes all info from each sheet to the new sheet Combined.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Combining multiple worksheets	naeemakhtar	Excel	1	05-11-2017 11:03 PM
How to copy workbooks from different worksheets into one new worksheet	abbani	Excel	3	12-12-2016 04:09 AM
Combining different worksheets into a single worksheet.	jimmy2016	Excel Programming	4	10-15-2016 09:05 AM
Combine values from different worksheets into a single worksheet.	jimmy2016	Excel	2	10-06-2016 09:15 AM
Cells from other worksheets apear on current worksheet	Equilar	Excel	3	05-03-2010 01:40 AM