Solved

How do I get VBA to Create a multiple worksheets using a template from data

Posted on 2015-02-02
11
174 Views
Last Modified: 2016-09-13
Hi experts

I have a VBA script that creates multiple worksheets from a masterdata sheet. (File attached/code below).  I would like the data to be created on a templated worksheet rather than a plain one.

Can this be done?

In my example the MasterData worksheet has all my reporting, the macro reads column A and creates a new sheet for each name change.  You can run the code to see an example.

Also included in the workbook is 'Example with tempate' this is how I want each created sheet to look with the relevant data. Rows 1-10 contain data I'd like on each report.  I would also like the managers name to appear in Cell C5 rather than as the first column.

Can you help?
Thank you


Code
Sub CreateSheets()
Dim ws As Worksheet, sh As Worksheet, rng As Range, cel As Range
    
Application.ScreenUpdating = False
    
Set ws = Worksheets("MasterData")
    
With ws.UsedRange.Columns("A")
    .AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    Set rng = .SpecialCells(xlVisible)
End With
    
ws.ShowAllData
    
For Each cel In rng.Cells
    If Trim(cel.Value) <> "" And cel.Row <> rng.Cells(1).Row Then
        ws.Copy After:=Worksheets(Worksheets.Count)
        Set sh = ActiveSheet
        sh.UsedRange.AutoFilter Field:=sh.Columns("A").Column, Criteria1:="<>" & cel, Operator:=xlAnd
        sh.UsedRange.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        sh.UsedRange.AutoFilter
        sh.Name = cel
        Application.Goto sh.[A1]
    End If
Next cel
    
Application.ScreenUpdating = True
    

End Sub

Open in new window

ExcelExample.xlsm
0
Comment
Question by:wisemat
[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
  • 5
  • 5
11 Comments
 
LVL 30

Expert Comment

by:gowflow
ID: 40584352
Yes for sure this can be done.

Question:
Will you be creating all these sheets always in the same workbook onto new sheets or you need to have them into a new workbook ?

Also in your template Cell B7 what is this: Blurb goes here ??? it is not in MasterData where do we get it from ??
gowlfow
0
 

Author Comment

by:wisemat
ID: 40584378
Oh that's great news!
All in the same workbook so it's self contained
0
 
LVL 30

Expert Comment

by:gowflow
ID: 40584504
ok here it is.

Sub CreateSheets()
Dim WS As Worksheet, sh As Worksheet, WST As Worksheet
Dim rng As Range, cel As Range
Dim Rmgr As Range, RItem As Range
Dim lRow As Long, MaxRow As Long, I As Long
Dim sMgr As String

Application.ScreenUpdating = False
    
'---> Set Variables
Set WS = Worksheets("MasterData")
MaxRow = WS.Range("A" & WS.Rows.Count).End(xlUp).Row

Set WST = Worksheets("Template")


'---> Sort Master Data by Manager Name
WS.UsedRange.Sort Key1:=WS.Range("A1"), order1:=xlAscending, Header:=xlNo, MatchCase:=False


For I = 1 To MaxRow
    If WS.Cells(I, "A") <> sMgr Then
        WST.Copy After:=Worksheets(Worksheets.Count)
        Set sh = Sheets(Worksheets.Count - 1)
        sh.Visible = xlSheetVisible
        sh.Name = WS.Cells(I, "A")
        sMgr = WS.Cells(I, "A")
        
        '---> Mgr Name
        sh.Cells(5, "C") = sMgr
        lRow = 11
    End If
        
    '---> Get the details
    WS.Range("A" & I & ":G" & I).Copy
    sh.Range("B" & lRow).PasteSpecial (xlPasteValues)
    sh.Range("B" & lRow & ":L" & lRow).Interior.ColorIndex = 0
    lRow = lRow + 1


Next I


Application.ScreenUpdating = True
    
MsgBox "Sheets created successfully.", vbInformation
End Sub

Open in new window


Simply open the attached workbook, enable macros, then from the developer menu choose Macroes and run CreateSheets and check the results.

Let me know if anything need modification.

NOTE
Still, you did not answer my question about Cell B7 : 'Blurb goes here'

gowflow
ExcelExample-V01.xlsm
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:wisemat
ID: 40584615
Hey that's awesome, Just checking it out now, sorry for the delay i was on the underground
0
 

Author Comment

by:wisemat
ID: 40584658
That's almost spot on my friend.

The blurb bit will just be where i can put some template information to the manager, like indicate with a 'x' in the cell to keep or remove the user.

Coud it be so on the generated reports the BossMan columis not there?
So there is the template, the bossman name filled in in C5 and then the data (sans bossman name/row).

Not a deal breaker if you cant.

Thanks
0
 
LVL 30

Expert Comment

by:gowflow
ID: 40584766
Sorry do not understand you request.

can you re-phrase in English ?
gowflow
0
 

Author Comment

by:wisemat
ID: 40584790
In English, I have updated the template
I have removed the 'manager name' column from the reuqired templated result.  I did this because the manager name is in cell C5, it seemed silly to have it repeating over and over in a column down the page.

So i was asking if macro could accomodate that change.  I would be grateful.
ExcelExample-V02.xlsm
0
 
LVL 30

Accepted Solution

by:
gowflow earned 500 total points
ID: 40585650
Here it is with some 'makeup' on final behavior.

Sub CreateSheets()
Dim WS As Worksheet, sh As Worksheet, WST As Worksheet
Dim rng As Range, cel As Range
Dim Rmgr As Range, RItem As Range
Dim lRow As Long, MaxRow As Long, I As Long
Dim sMgr As String

Application.ScreenUpdating = False
    
'---> Set Variables
Set WS = Worksheets("MasterData")
MaxRow = WS.Range("A" & WS.Rows.Count).End(xlUp).Row

Set WST = Worksheets("Template")


'---> Sort Master Data by Manager Name
WS.UsedRange.Sort Key1:=WS.Range("A1"), order1:=xlAscending, Header:=xlNo, MatchCase:=False


For I = 1 To MaxRow
    If WS.Cells(I, "A") <> sMgr Then
        If Not sh Is Nothing Then
            sh.Activate
            sh.Cells(1, 1).Select
        End If
        WST.Copy After:=Worksheets(Worksheets.Count)
        Set sh = Sheets(Worksheets.Count - 1)
        sh.Visible = xlSheetVisible
        sh.Name = WS.Cells(I, "A")
        sMgr = WS.Cells(I, "A")
        
        '---> Mgr Name
        sh.Cells(5, "C") = sMgr
        lRow = 11
    End If
        
    '---> Get the details
    WS.Range("B" & I & ":G" & I).Copy
    sh.Range("B" & lRow).PasteSpecial (xlPasteValues)
    sh.Range("B" & lRow & ":L" & lRow).Interior.ColorIndex = 0
    lRow = lRow + 1


Next I

If Not sh Is Nothing Then
    sh.Activate
    sh.Cells(1, 1).Select
End If
        
WS.Application.CutCopyMode = xlNone
WS.Activate
WS.Cells(1, 1).Select

Application.ScreenUpdating = True
    
MsgBox "Sheets created successfully.", vbInformation
End Sub

Open in new window


Pls check it and let me know.
gowflow
ExcelExample-V02.xlsm
0
 

Author Closing Comment

by:wisemat
ID: 40585769
Thank you gowflow,

The perfect solution, simple to use and 100% of requirements.

Full marks!!!
0
 
LVL 30

Expert Comment

by:gowflow
ID: 40585773
Thank you and glad I could help.
gowflow
0
 

Expert Comment

by:Cheese Club
ID: 41797263
Hi There,

I am wondering - what if the template is from a separate workbook, after running the Macro on MasterData, it generates multiple workbooks with the same template outside the Master Data and they all store under the same folder. What will be changed from the code?

Thank you.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Suggested Solutions

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

710 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