generating sheets based on a list of name in the master sheet and auto fill and copy template sheet

Eduard Ghitan

New Member
Joined
Jun 2, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello all - I hope you can all help me with my work.
I have been looking at 1000 threads regarding this matter and copied 1000 VBAs and tried to tweek them to my needs but sadly no luck :)
I want to put a list of names in the master sheet (A1- A100) and after generate sheets that have the same name as the names in my list.
Also the newly generated sheet has to copy the template sheet and autofill in B1 the same name as in the list of names in the master sheet (A1-A100) and give an automatic registration number in B2.
I added some screen shots of how would the finished product would look like.

Thank you for your amazing help!
 

Attachments

  • 1.jpg
    1.jpg
    94.5 KB · Views: 25
  • 2.jpg
    2.jpg
    93.4 KB · Views: 26
  • 3.jpg
    3.jpg
    91.9 KB · Views: 23
  • 4.jpg
    4.jpg
    83.3 KB · Views: 26

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Hi & welcome to MrExcel.
How about
VBA Code:
Sub EduardGhitan()
   Dim Cl As Range
   
   With Sheets("Master")
      For Each Cl In .Range("A1", .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").Value = Cl.Value
               .Range("B2") = 999 + Cl.Row
            End With
         End If
      Next Cl
   End With
End Sub
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
Sub EduardGhitan()
Dim Cl As Range

With Sheets("Master")
For Each Cl In .Range("A1", .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").Value = Cl.Value
.Range("B2") = 999 + Cl.Row
End With
End If
Next Cl
End With
End Sub

[/CODE]
This code works perfect for a very similar situation I am in. For the line .Range("B2") = 999 + C1.Row Instead of generating a number I need to copy a cell from Master in column B2. The cells are in the same row. See image below of master sheet same as example provided above. Please and thank you!
1624998643157.png
 
Upvote 0
How about
VBA Code:
.Range("B2") = Cl.Offset(, 1).Value
 
Upvote 0
How about
VBA Code:
.Range("B2") = Cl.Offset(, 1).Value
Very nice it worked! Thank you so much!
Only problem I have is that B2 value might change from on track to delayed and vice versa, but when I try to rerun macro it does not update the new value. Thanks!
 
Upvote 0
That's because the macro is designed to create new sheets if they don't already exist & nothing more.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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