Solved

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

Posted on 2015-02-02
11
135 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
  • 5
  • 5
11 Comments
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
Comment Utility
Oh that's great news!
All in the same workbook so it's self contained
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 

Author Comment

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

Author Comment

by:wisemat
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Sorry do not understand you request.

can you re-phrase in English ?
gowflow
0
 

Author Comment

by:wisemat
Comment Utility
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 29

Accepted Solution

by:
gowflow earned 500 total points
Comment Utility
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
Comment Utility
Thank you gowflow,

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

Full marks!!!
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Thank you and glad I could help.
gowflow
0
 

Expert Comment

by:Cheese Club
Comment Utility
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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

771 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