Combine data from multiple sheets into just one?

[34sweetness]

I have a workbook comprised of 15 sheets of data. The columns of data on each sheet are the same. The rows contain dated information where the sheets are split into different time frames.

I would like to combine all this data into one sheet so that I can sort and graph different items for the entire date period covered by all 15 sheets.

Is there a simple way to accomplish this?

Thanks in advance, because I know this site always comes through for me!

 

[excelbee2013]

Re: Replicating yearly sheet to monthly sheet

For Merging Sheets users can use this macro to merge Excel Sheets.

Hi Rajks, Yes you can do it using references but any deletion of rows can change whole data. You can run a macro each time you update or add new data to yearly sheet. Just check the month column and check with count of existing rows in both yearly sheet and monthly sheet. E.g. If count of rows for January in yearly sheet are greater than count of rows in monthly sheet then add rows to monthly sheet. If you need macro code, write me here back.

Reg,
Excel Bee

 

[liaites]

@Damon Ostrander
Hello!
I have the same problem, I tried your solution and it works, but I would also like the merged data to retain their link to the original worksheets, i.e. if any changes happen to the data in the original worksheets, these changes will automatically appear in the merged worksheet. Do you have any idea how I could do that?!

 

[Damon Ostrander]

Hi liaites,

Sorry for my slow reply. Here's a version of my original macro that retains the links as you requested. I retained links even to empty cells within the range of interest. Not sure if you wanted so let me know if you just wanted links to the cells that contain data and I'll modify the code.

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
   Dim MWSR    As Range 'Range to copy from MWS to AWS
   
   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
         Set MWSR = MWS.Range(MWS.Rows(NHR + 1), MWS.Rows(LR))
         'limit range to cells within the usedrange
         Set MWSR = Application.Intersect(MWSR, MWS.UsedRange)
         MWSR.Copy
         AWS.Select     'Select AWS to cause de-select of MWS before paste
         'Range(Rows(FAR), Rows(FAR + LR)).Select
         Range(Cells(FAR, 1), Cells(FAR + MWSR.Rows.Count - 1, MWSR.Columns.Count)).Select
         ActiveSheet.Paste Link:=True
      End If
   Next MWS

End Sub

Let me know if any problems.

Damon

 

[live21]

Re: Replicating yearly sheet to monthly sheet

Thanks a ton. Worked like a charm for me.

 

[lcgts]

Re: Replicating yearly sheet to monthly sheet

Hello Damon Ostrander,

I echo everyone else's praises. Your timeless macro works great.

I have a minor request -- can you add an extra column that holds the worksheet tab names where the rows came from? It would be a great help.

Thanks in advance.

 

[dur91dance]

Hello!
THANK YOU FOR THIS CODE, works perfectly! I have only one issue: in the source tabs, there are rows populated with data validation and formulas, but "empty". I want the macro to copy only the populated rows. Could you please help me?

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.