# Creating new Sheets based on a list on another Sheet



## Iwillexcel (Dec 8, 2022)

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 (Dec 8, 2022)

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 (Dec 14, 2022)

hajiali said:


> Add
> 
> ```
> If cell <> "" Then
> ...




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 (Dec 14, 2022)

sorry replace this

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

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


----------



## hajiali (Dec 14, 2022)

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 (Dec 14, 2022)

Perfect! Thank you so much for your help!


----------



## hajiali (Dec 14, 2022)

Glad I could help.


----------

