?
Solved

Code to combine sheets

Posted on 2016-11-04
10
Medium Priority
?
66 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 2000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

770 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