Solved

Code to combine sheets

Posted on 2016-11-04
10
41 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
  • 6
  • 4
10 Comments
 
LVL 43

Expert Comment

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

Author Comment

by:pdvsa
Comment Utility
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
Comment Utility
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
 

Author Comment

by:pdvsa
Comment Utility
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
Comment Utility
Header from rows 6 and 7. Rest is same.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Closing Comment

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

Author Comment

by:pdvsa
Comment Utility
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
Comment Utility
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
Comment Utility
The sheet names are already there in column A
0
 

Author Comment

by:pdvsa
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

743 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now