[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Code to combine sheets

Posted on 2016-11-04
10
Medium Priority
?
71 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

650 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