Solved

Code to combine sheets

Posted on 2016-11-04
10
58 Views
Last Modified: 2016-11-05
Hello Experts,

I have been exploring ways to combine the 8 sheets in the attached file.
I have tried Power Query and there are too many steps involved and not sure how I can easily change the data source.

I am now looking for code to combine the sheets to one consolidated sheet and I would be doing this on a recurring basis.  
There is extraneous data outside of the table (top and bottom) and not so sure how VBA code can be used to only combine what is inside the tables.   To facilitate any coding, I can say that the tables start at row 7 for each separate sheet (meaning rows 1-6 for each sheet are not needed).

Column names are the same for each sheet

thank you
grateful for your help.
EE_combineSheets.xlsx
0
Comment
Question by:pdvsa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 41873740
Are you looking for totals for each sheet or a single total for all sheets?
0
 

Author Comment

by:pdvsa
ID: 41873756
Hi, I am not looking for totals...only the raw data.   I will create a separate pivot for the totals.
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 41873793
Try this macro
Sub combinesheets()
    Dim ws As Worksheet
    Dim tws As Worksheet
    Dim headerdone As Boolean
    Dim sr As Range
    Dim tr As Range
    headerdone = False
    Application.DisplayAlerts = False
        On Error Resume Next
            ActiveWorkbook.Worksheets("Combined").Delete
        On Error GoTo 0
    Application.DisplayAlerts = False
    Set tws = ActiveWorkbook.Worksheets.Add
    tws.Name = "Combined"
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Combined" Then
            If Not headerdone Then
                ws.Range("A6:A7").EntireRow.Resize(, tws.Columns.Count - 1).Copy tws.Range("B6")
                tws.Range("B7").Copy tws.Range("A7")
                tws.Range("A7") = "Sheet name"
                headerdone = True
            End If
            Set sr = ws.Range("A8:A" & ws.Range("A8").End(xlDown).Row).Resize(, tws.Columns.Count - 1)
            Set tr = tws.Range("A" & Rows.Count).End(xlUp).Offset(1)
            sr.Copy tr.Offset(, 1)
            tr.Resize(sr.Rows.Count) = ws.Name
        End If
    Next ws
End Sub
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:pdvsa
ID: 41873811
Thank you
I am not in front of the computer

Does the code copy each sheet starting at row 8?  If i read correctly, it copies the header for the first sheet in row 7 then for each sheet after that its only copying data.

Thank you once again for the expert assistance
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 41873839
Header from rows 6 and 7. Rest is same.
0
 

Author Closing Comment

by:pdvsa
ID: 41873974
Perfect!  I wish i could do that.  Thank you again for the help.
0
 

Author Comment

by:pdvsa
ID: 41875162
Hi Saqib, i am going to ask another question because i now need sheet names.  Plan to post in a few minutes.
0
 

Author Comment

by:pdvsa
ID: 41875550
Saqib, i just posted the question.  I accidentally posted jn VBA only and just modified the category to include excel.  Thank you
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 41875593
The sheet names are already there in column A
0
 

Author Comment

by:pdvsa
ID: 41875851
OK I have tested and I can work with the code the way it stands.  I can filter for the sheets I do not need.  It returns the correct answer.

 One thing I would like to request a modification:
 Paste Values.  Is this possible?  AS of right now, the code is copying the formulas and after copying, the formulas are referencing an incorrect cell.  

 Thank you so much for the help.  
 Very nice.
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question