Combine data from multiple sheets into just one?

Hi sweetness,

If I understand your problem correctly, the following macro should do the trick. This macro will append the data from the selected worksheet to the end of the active worksheet.

Sub MergeSheets()

' Appends data from all the selected worksheets onto the end of the
' active worksheet.

Const NHR = 1 'Number of header rows to not copy from each MWS

Dim MWS As Worksheet 'Worksheet to be merged (appended)
Dim AWS As Worksheet 'Worksheet to which the data are transferred
Dim FAR As Long 'First available row on AWS
Dim LR As Long 'Last row on the MWS sheets

Set AWS = ActiveSheet

For Each MWS In ActiveWindow.SelectedSheets
If Not MWS Is AWS Then
FAR = AWS.UsedRange.Cells(AWS.UsedRange.Cells.Count).Row + 1
LR = MWS.UsedRange.Cells(MWS.UsedRange.Cells.Count).Row
MWS.Range(MWS.Rows(NHR + 1), MWS.Rows(LR)).Copy AWS.Rows(FAR)
End If
Next MWS

End Sub

To install this macro, go to the VBE (keyboard Alt-TMM), insert a new macro module (Alt-IM), and paste the above code into the Code pane. Before running the macro select all the sheets you want to merge (Ctrl-click on tab). The active worksheet will be the one that was active before selecting the others.

Hi,

I also have some data on more than one tab that I would like to be able to use within one pivot table.

Will this solution allow you to exceed the row limit on a given sheet? I've run up against that limit when the number data rows I want to use in a pivot table is more than 65k (I think thats the limit).

Thanks much, Dan

Hi Dan,

Unfortunately there is no way that I know of to extend the 65K limit on rows on a worksheet (nor the 256 column limit). It would be possible to add a few lines of code to my macro to automatically add a new worksheet to continue writing the data to when the first one is filled. But I don't believe a pivot table can span multiple sheets so this solution might not be helpful for you.

Keep Excelling.

Damon

FANTASTIC Solution! Thank you! I'd like to add -- shortcut to select all sheets before you run the Macro:
Right-click a sheet tab, and then click Select All Sheets on the shortcut menu.

-Audrey

Damon Ostrander said:

Hi sweetness,

If I understand your problem correctly, the following macro should do the trick. This macro will append the data from the selected worksheet to the end of the active worksheet.

Sub MergeSheets()

' Appends data from all the selected worksheets onto the end of the
' active worksheet.

Const NHR = 1 'Number of header rows to not copy from each MWS

Dim MWS As Worksheet 'Worksheet to be merged (appended)
Dim AWS As Worksheet 'Worksheet to which the data are transferred
Dim FAR As Long 'First available row on AWS
Dim LR As Long 'Last row on the MWS sheets

Set AWS = ActiveSheet

For Each MWS In ActiveWindow.SelectedSheets
If Not MWS Is AWS Then
FAR = AWS.UsedRange.Cells(AWS.UsedRange.Cells.Count).Row + 1
LR = MWS.UsedRange.Cells(MWS.UsedRange.Cells.Count).Row
MWS.Range(MWS.Rows(NHR + 1), MWS.Rows(LR)).Copy AWS.Rows(FAR)
End If
Next MWS

End Sub

To install this macro, go to the VBE (keyboard Alt-TMM), insert a new macro module (Alt-IM), and paste the above code into the Code pane. Before running the macro select all the sheets you want to merge (Ctrl-click on tab). The active worksheet will be the one that was active before selecting the others.

Click to expand...

Hello Everyone,

I'm reactivating the thread because the bulk of my need is the same although I have a few additional features I'd like to add if possible

first off I'm pretty new on the VBA, so please excuse me if I say nonsense

actually I'd like to combine data from 3 sheets into one, the columns are the same (there are 18 of them) and in the same order, and i want the data to be put following each other

the 3 sheets emanate by way of formulas from one "test" sheet where I will be pasting information from an outside report,

you can find my example file here:
http://www.filedropper.com/testmaterielfixe_1

in an ideal world i'd want the macro to:

on the first data sheet ("retrait1") pull down the formulas from line 2 to as many lines as there are on the "test" sheet. for example if i paste 2000 lines on the "test" sheet, i want the "retrait1" sheet to feature 2000 lines as well

on the second data sheet ("retrait2") pull down the formula from line 2 to as many lines as there are on the "test" sheet. Then eliminate the lines with a 0 in column O (Mes paramètres produit)

on the third data sheet ("retrait3") pull down the formula from line 2 to as many lines as there are on the "test" sheet. Then eliminate the lines with a 0 in column O (Mes paramètres produit)

once this is done, that's when I'd like to combine the data from the three "retrait" sheets, either in the "retrait1" sheet, or on a new "final" sheet

do you think it's possible?

many thanks in advance
<table style="width: 93px; height: 54px;" border="0" cellpadding="0" cellspacing="0"><tbody><tr height="20"><td class="xl63" style="height: 15pt; width: 48pt;" height="20" width="64">
</td> </tr></tbody></table>

Excellent solution. Working like a charm for me. Thanks a ton.

Damon Ostrander said:

Hi sweetness,

If I understand your problem correctly, the following macro should do the trick. This macro will append the data from the selected worksheet to the end of the active worksheet.

Sub MergeSheets()

' Appends data from all the selected worksheets onto the end of the
' active worksheet.

Const NHR = 1 'Number of header rows to not copy from each MWS

Dim MWS As Worksheet 'Worksheet to be merged (appended)
Dim AWS As Worksheet 'Worksheet to which the data are transferred
Dim FAR As Long 'First available row on AWS
Dim LR As Long 'Last row on the MWS sheets

Set AWS = ActiveSheet

For Each MWS In ActiveWindow.SelectedSheets
If Not MWS Is AWS Then
FAR = AWS.UsedRange.Cells(AWS.UsedRange.Cells.Count).Row + 1
LR = MWS.UsedRange.Cells(MWS.UsedRange.Cells.Count).Row
MWS.Range(MWS.Rows(NHR + 1), MWS.Rows(LR)).Copy AWS.Rows(FAR)
End If
Next MWS

End Sub

To install this macro, go to the VBE (keyboard Alt-TMM), insert a new macro module (Alt-IM), and paste the above code into the Code pane. Before running the macro select all the sheets you want to merge (Ctrl-click on tab). The active worksheet will be the one that was active before selecting the others.

Click to expand...

Love this solution to combine the data on various sheets.

May I know how I can set the properties of the master sheet to reflect any change in the other worksheets (amendments or additions)
and also make the master sheet read only?

All the sheets have only text data (no formulae)

Thank you

Damon Ostrander said:

Hi sweetness,

If I understand your problem correctly, the following macro should do the trick. This macro will append the data from the selected worksheet to the end of the active worksheet.

Sub MergeSheets()

' Appends data from all the selected worksheets onto the end of the
' active worksheet.

Const NHR = 1 'Number of header rows to not copy from each MWS

Dim MWS As Worksheet 'Worksheet to be merged (appended)
Dim AWS As Worksheet 'Worksheet to which the data are transferred
Dim FAR As Long 'First available row on AWS
Dim LR As Long 'Last row on the MWS sheets

Set AWS = ActiveSheet

For Each MWS In ActiveWindow.SelectedSheets
If Not MWS Is AWS Then
FAR = AWS.UsedRange.Cells(AWS.UsedRange.Cells.Count).Row + 1
LR = MWS.UsedRange.Cells(MWS.UsedRange.Cells.Count).Row
MWS.Range(MWS.Rows(NHR + 1), MWS.Rows(LR)).Copy AWS.Rows(FAR)
End If
Next MWS

End Sub

To install this macro, go to the VBE (keyboard Alt-TMM), insert a new macro module (Alt-IM), and paste the above code into the Code pane. Before running the macro select all the sheets you want to merge (Ctrl-click on tab). The active worksheet will be the one that was active before selecting the others.

Click to expand...

Download and use this excel macro file to automated merge multiple files sheets into one master. Works with 2010, 07 and 03.
Merge Excel Sheets 2007, 2010 & 2003

Replicating yearly sheet to monthly sheet

Hi ,


I am working on a enquiry tracking sheet which has tabs Jan to Dec and Yearly enquiry list


Could anyone kindly help me to figure out the following


Yearly sheet (master sheet)is the main sheet and is filled with data as and when enquiries come in. The data from here has to be replicated/copied into individual months Tabs,-Jan, Feb etc automatically and be seen as shown in sample. The data in the yearly sheet needs to remain as it is after transfer. Kindly let me know if its possible. if yes does it reflect updated data in monthly tabs if yearly sheet is updated.


The sample is uploaded here


https://www.dropbox.com/s/x8spcw15uq08vkl/sample_Enquiry_tracker.xls