Creating new Sheets based on a list on another Sheet

[Iwillexcel]

Hello,
I am trying to make my life a little easier. Rather than manually changing 52 sheets every year, I was looking for a way to have a macro create a new sheet and name it based on the week in a list on a "Data" sheet. I have a "Master" sheet of what each sheet should look like and be formatted like. I found a Macro that works to create a new sheet and name it but I need one more thing and I am not savvy enough to figure it out yet. I need the date in L2:O3 on the newly created sheet to reflect the date of the selected cell to create the new sheet. Or if there is an easier way that I am missing I am open to suggestions too.

This is the "Master" Sheet that I want the rest of the sheets to look like, but with the correct date in L2:O3

Here is my "Data" sheet. I want the new Sheets to reflect the dates in "L3:L55"

This is the Macro I found that will create a new sheet with the name of the cell I have selected.

Sub CreateSheets()
'Dimension variables and declare data types
Dim rng As Range
Dim cell As Range
'Enable error handling
On Error GoTo Errorhandling
'Show inputbox to user and prompt for a cell range
Set rng = Application.InputBox(Prompt:="Select cell range:", _
Title:="Create sheets", _
Default:=Selection.Address, Type:=8)
'Iterate through cells in selected cell range
For Each cell In rng
'Check if cell is not empty
If cell <> "" Then
'Insert worksheet and name the worksheet based on cell value
Worksheets("Master").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = cell.Value
End If
'Continue with next cell in cell range
Next cell
'Go here if an error occurs
Errorhandling:
'Stop macro
End Sub

 

[hajiali]

Add

If cell <> "" Then
'Insert worksheet and name the worksheet based on cell value
Worksheets("Master").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = cell.Value
Activesheet.range(“L2”)= cell.value
End If

That’s assuming the value in cells L3:L55 in the Data sheet are dates if not you can try

Activesheet.range(“L2”)=cell.value.offset(,-1)

 

[Iwillexcel]

Add

If cell <> "" Then
'Insert worksheet and name the worksheet based on cell value
Worksheets("Master").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = cell.Value
Activesheet.range(“L2”)= cell.value
End If

That’s assuming the value in cells L3:L55 in the Data sheet are dates if not you can try

Activesheet.range(“L2”)=cell.value.offset(,-1)

I tried using both codes and have not gotten any results. Nothing seems to happen in cell L2.

I appreciate you trying to help but hopefully you can tell me what I am still doing wrong lol. Thanks

 

[hajiali]

sorry replace this

Activesheet.range(“L2”)=cell.value.offset(,-1)

with this

Activesheet.range(“L2”)=cell.offset(,-1)

 

[hajiali]

this will be your code

Sub CreateSheets()
'Dimension variables and declare data types
Dim rng As Range
Dim cell As Range
'Enable error handling
On Error GoTo Errorhandling
'Show inputbox to user and prompt for a cell range
Set rng = Application.InputBox(Prompt:="Select cell range:", _
Title:="Create sheets", _
Default:=Selection.Address, Type:=8)
'Iterate through cells in selected cell range
For Each cell In rng
'Check if cell is not empty
If cell <> "" Then
'Insert worksheet and name the worksheet based on cell value
Worksheets("Master").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = cell.Value
ActiveSheet.Range("L2") = cell.Offset(, -1)
End If
'Continue with next cell in cell range
Next cell
'Go here if an error occurs
Errorhandling:
'Stop macro
End Sub

 

[Iwillexcel]

Perfect! Thank you so much for your help!

 

[hajiali]

Glad I could help.