VBA - Copy sheet and rename based on cell range

[Nordicrx8]

Hi all!

I'm trying to automate a process. At the start of a new month, I need to create a new sheet for each day of the month. I'd like to press a button and have the sheet do the work for me.

I found this code and got it to work partially - it creates 30 new tabs, but it just increments the number rather than taking the name from the cell range B2:B32. (Which is a date)

Could anyone assist in altering the code so it renames the sheet for me?

Thank you!!

Sub Create_Daily()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets("Blank")
Set sh2 = Sheets("MTD Performance")
    For Each c In sh2.Range("B2:B32", sh2.Cells(Rows.Count, 2).End(xlUp))
        sh1.Copy After:=Sheets(Sheets.Count)
 
    Next
End Sub

 

[Fluff]

How about

Sub Create_Daily()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets("Blank")
Set sh2 = Sheets("MTD Performance")
    For Each c In sh2.Range("B2", sh2.Cells(Rows.Count, 2).End(xlUp))
        sh1.Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = c.Value
    Next
End Sub

 

[Nordicrx8]

How about

Sub Create_Daily()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets("Blank")
Set sh2 = Sheets("MTD Performance")
    For Each c In sh2.Range("B2", sh2.Cells(Rows.Count, 2).End(xlUp))
        sh1.Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = c.Value
    Next
End Sub

It throws a runtime error and highlights

  ActiveSheet.Name = c.Value

thoughts?

 

[Fluff]

Do you have any blank cells in col B?

 

[Nordicrx8]

Do you have any blank cells in col B?

B2:B32 is each day of the month. There is a blank row, and it repeats the days of the month again for a different department.

I tried editing this to a range, but it still popped an error

 For Each c In sh2.Range("B2:B32", sh2.Cells(Rows.Count, 2).End(xlUp))

 

[Fluff]

In that case use

         For Each c In sh2.Range("B2:B32")

 

[Nordicrx8]

In that case use

         For Each c In sh2.Range("B2:B32")

Still popping an error on activesheet.Name = C.Value ?

Here is current code:

Sub Create_Daily()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets("Blank")
Set sh2 = Sheets("MTD Performance")
    For Each c In sh2.Range("B2:B32")
        sh1.Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = c.Value
    Next
End Sub

thanks for your help on this!

 

[Fluff]

What exactly is in B2:B32?

 

[Nordicrx8]

It's just the days of the month. B2 is a cell that references another cell (=AK23) and the subsequent cells reference the previous cell +1. (=B2+1)

1-Sep

2-Sep

3-Sep

4-Sep

5-Sep

6-Sep

7-Sep

8-Sep

9-Sep

10-Sep

11-Sep

12-Sep

13-Sep

14-Sep

15-Sep

16-Sep

17-Sep

18-Sep

19-Sep

20-Sep

21-Sep

22-Sep

23-Sep

24-Sep

25-Sep

26-Sep

27-Sep

28-Sep

29-Sep

30-Sep

1-Oct

 

[Nordicrx8]

Oh, I got it working! I converted the dates from DD-MM format into the month and day as text, with no hyphen. It worked! thank you so much for your help!