Creating Sheets from Column Using Template Sheet and Copy Cells to Created Sheet

X

xavier12

New Member
Joined
Jun 29, 2021
Messages
17
Hello,

I have a data management I am working on. So far it is great but needs to be tweaked. I have a sheets 'Master', 'Template'. In Master in column A, I have a ID which is generated from three different variables. The ID uses concatenate formula from column B, D, and F as shown in the image attached. Currently I have to manually type in the ID in column A because when I change my code below from
Set shNAMES = wsMASTER.Range("A2:A" & Rows.Count).SpecialCells(xlConstants) to xl(Formulas) (replace xlConstants to xl(Formulas)) it generates the sheets based off ID but also creates an additional template sheet everytime. Anybody know how to fix this?

The code creates new sheets from Master column A and renames the sheet to column A cells using template sheet, and does not create duplicates and displays message.

VBA Code: 
Option Explicit

Sub SheetsFromTemplate()
Dim wsMASTER As Worksheet, wsTEMP As Worksheet, wasVISIBLE As Boolean
Dim shNAMES As Range, Nm As Range

With ThisWorkbook                                               'keep focus in this workbook
    Set wsTEMP = .Sheets("Template")                            'sheet to be copied
    wasVISIBLE = (wsTEMP.Visible = xlSheetVisible)              'check if it's hidden or not
    If Not wasVISIBLE Then wsTEMP.Visible = xlSheetVisible      'make it visible
   
    Set wsMASTER = .Sheets("Master")                            'sheet with names
                                                                'range to find names to be checked
    Set shNAMES = wsMASTER.Range("A2:A" & Rows.Count).SpecialCells(xlConstants)     'or xlFormulas
   
    Application.ScreenUpdating = False                              'speed up macro
    For Each Nm In shNAMES                                          'check one name at a time
        If Not Evaluate("ISREF('" & CStr(Nm.Text) & "'!A1)") Then   'if sheet does not exist...
            wsTEMP.Copy After:=.Sheets(.Sheets.Count)               '...create it from template
            ActiveSheet.Name = CStr(Nm.Text)                        '...rename it
        End If
    Next Nm
   
    wsMASTER.Activate                                           'return to the master sheet
    If Not wasVISIBLE Then wsTEMP.Visible = xlSheetHidden       'hide the template if necessary
    Application.ScreenUpdating = True                           'update screen one time at the end
End With

MsgBox "All sheets created"
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

For this next part I want to create a second macro to run after sheets are created (I do not think it can be all under macro but if possible that would be better) The second macro is to; In Master there is a drop down to select status of ID that can be changed depending on status. I need to be able to copy that status into the new sheets created from the code above and be able to rerun this code to update the ID sheets depending on how master status is changed. Below is picture of template and in B1 would auto copy the ID from Master and B2 would have this status that can be updated from Master into the ID sheet. See pictures attached for Template and an example ID sheet.

Thank you in advance for taking the time to read this and help me out!

Regards,
Xavier
 

Attachments

  • Master.JPG
    Master.JPG
    60.1 KB · Views: 59
  • Template.JPG
    Template.JPG
    26.2 KB · Views: 60
  • ID sheet.JPG
    ID sheet.JPG
    61.7 KB · Views: 60
Sort by date Sort by votes
Is there any way to add a hyperlink to master ID so when many sheets are generated you can click from master and activate that ID worksheet?
 
Upvote 0

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
xavier12 said:
Is there any way to add a hyperlink to master ID so when many sheets are generated you can click from master and activate that ID worksheet?
Click to expand...
Actually scratch this request for now. I have a different problem. I tried to add other fields to copy from master into ID sheets and it does not seem to work properly. Here is the code in module now where I added additional range lines to copy over additional info from master to id sheet:

Sub SheetsFromTemplate()
Dim wsMASTER As Worksheet, wsTEMP As Worksheet, wasVISIBLE As Boolean
Dim shNAMES As Range, Nm As Range
Set wsTEMP = Sheets("Template")
wasVISIBLE = (wsTEMP.Visible = xlSheetVisible)
If Not wasVISIBLE Then wsTEMP.Visible = xlSheetVisible
Set wsMASTER = Sheets("Master")
Set shNAMES = wsMASTER.Range("A2", wsMASTER.Range("A" & Rows.Count).End(xlUp))
Application.ScreenUpdating = False
For Each Nm In shNAMES
If Not Evaluate("ISREF('" & CStr(Nm) & "'!A1)") Then
wsTEMP.Copy After:=Sheets(Sheets.Count)
With ActiveSheet
.Name = CStr(Nm.Text)
.Range("B1") = Nm.Value
.Range("B2") = Nm.Offset(, 10)
.Range("B3") = Nm.Offset(, 8)
.Range("D1") = Nm.Offset(, 7)
End With
End If
Next Nm
wsMASTER.Activate
If Not wasVISIBLE Then wsTEMP.Visible = xlSheetHidden
Application.ScreenUpdating = True
MsgBox "All sheets created"
End Sub

And here is the sheet code that I tried to modify to auto update when master changes:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Column <> 8 Then Exit Sub
Sheets(Target.Offset(, -11).Value).Range("B2") = Target
Sheets(Target.Offset(, -9).Value).Range("B3") = Target
Sheets(Target.Offset(, -8).Value).Range("D1") = Target
End Sub

It does not update the sheets when I update the master, and the code creates an additional template sheet everytime now. I had a debug error everytime I run this and it does not like like line .Name = CStr(Nm.Text) but I am guessing because the code does not make sense. Any help?
 
Upvote 0
The SheetsFromTemplate macro is working properly for me. Please explain how it is not working for you.
In the Worksheet_Change macro you posted, the target column is column 8 (Status) which means that it will be triggered when the Status changes. The ID sheet name must always be Sheets(Target.Offset(, -7).Value) and your code as written, will always place the target value which is the Status in B2, B3 and D1. I don't think this is what you want.
It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your Master sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
 
Upvote 0
mumps said:
The SheetsFromTemplate macro is working properly for me. Please explain how it is not working for you.
In the Worksheet_Change macro you posted, the target column is column 8 (Status) which means that it will be triggered when the Status changes. The ID sheet name must always be Sheets(Target.Offset(, -7).Value) and your code as written, will always place the target value which is the Status in B2, B3 and D1. I don't think this is what you want.
It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your Master sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
Click to expand...

Dropbox

www.dropbox.com www.dropbox.com

Thanks for your patience.

With the codes, this workbook should create new sheets from master column A using template and not create duplicate sheet names like the code is currently doing and autofill from master to the created ID sheet columns A, H, I, and K from master. When I go back to master and change a status or ID or Owner, the respective ID sheet should also update.

I also was thinking if there is anyway to hyperlink the sheet in master column A once created so it is easier to activate that ID worksheet when there are many sheets created (or if there is a better way to activate the worksheets?)

Lastly, it would be nice to add a button onto master to create the sheets instead of having to go to macros and pressing run so that way others using the doc would be able to run code easily.

Again thanks for your patience and I hope this is much more clear.

Thanks,
Xavier
 
Upvote 0
Place this macro in a regular module. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top click 'Insert' and then click 'Module'. Copy and paste the macro into the empty code window that opens up.
VBA Code: 
Sub SheetsFromTemplate()
    Application.ScreenUpdating = False
    Dim Cl As Range
    With Sheets("Master")
       For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
          If Not Evaluate("isref('" & Cl.Value & "'!a1)") Then
             Sheets("Template").Copy , Sheets(Sheets.Count)
             With ActiveSheet
                .Name = Cl.Value
                .Range("B1").Resize(3).Value = WorksheetFunction.Transpose(Array(Cl, Cl.Offset(, 10), Cl.Offset(, 8)))
                .Range("C2") = Cl.Offset(, 7).Value
                .Columns.AutoFit
             End With
          End If
       Next Cl
    End With
    Sheets("Master").Activate
    Application.ScreenUpdating = True
End Sub
Copy and paste the following macros into the worksheet code module. Do the following: right click the tab name for your Master sheet and click 'View Code'. Paste the macros into the empty code window that opens up. Close the code window to return to your sheet. Do not change the ID in column A manually. It will change as a result of the formula when you change the values in columns B, D and F. Keep in mind that if you change the values in columns B, D or F, not only will the ID in column A change but the sheet name will change from the old ID to the new ID.
VBA Code: 
Dim oldSheet As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("B:B,D:D,F:F")) Is Nothing Then Exit Sub
    oldSheet = Range("A" & Target.Row).Value
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("A:B,D:D,F:F,H:H,K:K")) Is Nothing Then Exit Sub
    Select Case Target.Column
        Case Is = 2, 4, 6
            Sheets(oldSheet).Name = Range("A" & Target.Row).Value
            Sheets(Range("A" & Target.Row).Value).Range("B1") = Range("A" & Target.Row).Value
        Case Is = 8
            Sheets(Target.Offset(, -7).Value).Range("C2") = Target
        Case Is = 11
            Sheets(Target.Offset(, -10).Value).Range("B2") = Target
    End Select
End Sub
I will work on the other 2 requests.
 
Upvote 0
Click here to download your file. I have added the button on the Master sheet and have modified the SheetsFromTemplate macro to create the hyperlinks as below:
VBA Code: 
Sub SheetsFromTemplate()
    Application.ScreenUpdating = False
    Dim Cl As Range, ws As Worksheet
    With Sheets("Master")
       For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
          If Not Evaluate("isref('" & Cl.Value & "'!a1)") Then
             Sheets("Template").Copy , Sheets(Sheets.Count)
             With ActiveSheet
                .Name = Cl.Value
                For Each ws In Sheets
                    If Cl.Value = ws.Name Then
                        .Hyperlinks.Add Anchor:=Cl, Address:="", SubAddress:="'" & ws.Name & "'!A1", TextToDisplay:=ws.Name
                    End If
                Next ws
                .Range("B1").Resize(3).Value = WorksheetFunction.Transpose(Array(Cl, Cl.Offset(, 10), Cl.Offset(, 8)))
                .Range("C2") = Cl.Offset(, 7).Value
                .Columns.AutoFit
             End With
          End If
       Next Cl
    End With
    Sheets("Master").Activate
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
mumps said:
Place this macro in a regular module. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top click 'Insert' and then click 'Module'. Copy and paste the macro into the empty code window that opens up.
VBA Code: 
Sub SheetsFromTemplate()
    Application.ScreenUpdating = False
    Dim Cl As Range
    With Sheets("Master")
       For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
          If Not Evaluate("isref('" & Cl.Value & "'!a1)") Then
             Sheets("Template").Copy , Sheets(Sheets.Count)
             With ActiveSheet
                .Name = Cl.Value
                .Range("B1").Resize(3).Value = WorksheetFunction.Transpose(Array(Cl, Cl.Offset(, 10), Cl.Offset(, 8)))
                .Range("C2") = Cl.Offset(, 7).Value
                .Columns.AutoFit
             End With
          End If
       Next Cl
    End With
    Sheets("Master").Activate
    Application.ScreenUpdating = True
End Sub
Copy and paste the following macros into the worksheet code module. Do the following: right click the tab name for your Master sheet and click 'View Code'. Paste the macros into the empty code window that opens up. Close the code window to return to your sheet. Do not change the ID in column A manually. It will change as a result of the formula when you change the values in columns B, D and F. Keep in mind that if you change the values in columns B, D or F, not only will the ID in column A change but the sheet name will change from the old ID to the new ID.
VBA Code: 
Dim oldSheet As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("B:B,D:D,F:F")) Is Nothing Then Exit Sub
    oldSheet = Range("A" & Target.Row).Value
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("A:B,D:D,F:F,H:H,K:K")) Is Nothing Then Exit Sub
    Select Case Target.Column
        Case Is = 2, 4, 6
            Sheets(oldSheet).Name = Range("A" & Target.Row).Value
            Sheets(Range("A" & Target.Row).Value).Range("B1") = Range("A" & Target.Row).Value
        Case Is = 8
            Sheets(Target.Offset(, -7).Value).Range("C2") = Target
        Case Is = 11
            Sheets(Target.Offset(, -10).Value).Range("B2") = Target
    End Select
End Sub
I will work on the other 2 requests.
Click to expand...
This code works amazing on the Workbook Test. Thank you!
Now when I put it into my unsensitized workbook version, I get debug error for line:
If Not Evaluate("isref('" & Cl.Value & "'!a1)") Then
The rows and columns are exact same as workbook test.
Also when I change status or ID from master it is not changing the ID sheet as well. Any ideas?
 
Upvote 0
Can you upload a de-sensitized version of your file? I wouldn't need all the rows, just a dozen or so.
 
Upvote 0
xavier12 said:
This code works amazing on the Workbook Test. Thank you!
Now when I put it into my unsensitized workbook version, I get debug error for line:
If Not Evaluate("isref('" & Cl.Value & "'!a1)") Then
The rows and columns are exact same as workbook test.
Also when I change status or ID from master it is not changing the ID sheet as well. Any ideas?
Click to expand...
The only thing different is columns B, D, and F from master sheet is drived from code sheet using index function, so if I change to Nike to Adidas in Master the respective # auto changes.
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
VBA to create sheets from a template based off a list in the master sheet not working when protected
Replies
4
Views
925
Ibis2653
I
Z
  • Question Question
Vba - create new sheet and copy date into it.
Replies
1
Views
62
zerofeng
Z
M
  • Solved
Create sheet for every day of month using Master template, name each sheet as per day of month. VBA
Replies
4
Views
504
John_w
J
S
  • Question Question
HYPERLINK() function Seems Unable to Jump to Another Sheets Table and Filter
Replies
6
Views
218
Skyybot
Skyybot
E
  • Question Question
VBA - Create a new sheet + hyperlink name
Replies
3
Views
400
excelnewbie990
E

Forum statistics

Threads
1,223,893
Messages
6,175,241
Members
452,622
Latest member
Laura_PinksBTHFT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top