Solved

Add sheet names, VBA macro consolidate sheets

Posted on 2016-11-04
8
38 Views
Last Modified: 2016-11-06
Hello,

I need to modify the below code to add sheet names.  I have 8 sheets to copy.  Typing from phone...please use generic sheet names and i can edit later.  

Grateful for your kind assistance.

This is a follow up to this question yesterday:   https://www.experts-exchange.com/questions/28980993/Code-to-combine-sheets.html?anchor=a41873839&notificationFollowed=178705137#a41873839


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
Comment
Question by:pdvsa
  • 5
  • 3
8 Comments
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 41875595
This is already listing sheet names in column A of the Combined sheet
0
 

Author Comment

by:pdvsa
ID: 41875605
Ok.  I have yet to test.  In the file, there are many sheets.  In the initial question, i removed the sheets i didnt need.  

If kot mistaken, In the combined sheet, the code combines ALL sheets?
0
 

Author Comment

by:pdvsa
ID: 41875608
Not sure if clear...I only want to combine some of the sheets.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 41875609
Yes, All sheets.

How to know "which" sheets?
0
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.

 

Author Comment

by:pdvsa
ID: 41875627
They would have certain names.  8 of the sheets out of 20 is the split.
0
 

Author Comment

by:pdvsa
ID: 41875850
Saqib,

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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 41875895
This should take of both your requirements.
The 8 sheets may be modified in the case statement
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
        Select Case ws.Name
        Case "SA-Refined Prod", "SCA-Monomer", "SCA - Poly", "ATC-Mono", "ATC-200075", "ATC - Poly", "Middle East - Poly", "Middle East-Mono"
            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.Offset(, 1).Resize(sr.Rows.Count, sr.Columns.Count).Value = sr.Value
            tr.Resize(sr.Rows.Count) = ws.Name
        End Select
    Next ws
End Sub

Open in new window

0
 

Author Comment

by:pdvsa
ID: 41875928
that worked perfectly.  Thank you very much the expert assistance.  Have a good day.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
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…

744 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

12 Experts available now in Live!

Get 1:1 Help Now