Solved

Code to combine sheets

Posted on 2016-11-04
10
61 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

691 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