Copying and renaming sheets multiple times

[red83]

Hi all
Totally novice at this but need a macro that will achieve the following:
Copy a sheet multiple times (sheet is named 17-11-2024) and rename each copy with the next date in sequence. I.e sheet 2 will be named 18-11-2024, sheet 3 named 19-11-2024 etc
Then, to complicate even more I need B6 cell data from sheet 17-11-2024, carried over into cell B7 on sheet 18-11-2024, and then repeated each day so B6 will always carry into B7 the following day.

I know how to do this sheet by sheet but I'm hoping to do for a couple of months so it'd be a very long winded process and was staring at my screen thinking there's gotta be a quicker more efficient way to do it!

 

[Oaktree]

Welcome to the board!

Certainly possible to do what you described, but I wonder if it's the right approach... after you've been at this for 3 months, do you really want 90+ tabs to navigate? Or hundreds after some more months?

If what you're doing is setting up a form that needs to populate with different days' data, you'll be better off setting up the form's template on one tab and storing the different days' data in a normalized table (i.e. different columns/fields like date, cell #1 input, cell #2 input, etc.) on a second tab. Then, you can have an input for date on your form and use XLOOKUP or similar to pull in the corresponding data from your table.

That approach means you'd only have 2 tabs instead of 90+, and a MUCH better way of finding i.e. how much did Box #1's value change between March and October. You can just look down the column in the data table instead of having to hunt through a bunch of tabs!

 

[Disastrouscoder]

@Oaktree Can you suggest how to do just the first part: "Copy a sheet multiple times (sheet is named 17-11-2024) and rename each copy with the next date in sequence. I.e sheet 2 will be named 18-11-2024, sheet 3 named 19-11-2024 etc". I am trying to do that to set up a meeting dashboard but want to have a common table for all of them so copying makes it easier rather than copy paste each time. Thanks!

 

[Peter_SSs]

Can you suggest how to do just the first part: "Copy a sheet multiple times (sheet is named 17-11-2024) and rename each copy with the next date in sequence. I.e sheet 2 will be named 18-11-2024, sheet 3 named 19-11-2024 etc".

Welcome to the MrExcel board!
Try something like this with a copy of your workbook.

Sub Copy_Sheets()
  Dim i As Long
  
  Const NumberOfCopies As Long = 5  '<- Change to how many copied you want
  
  Application.ScreenUpdating = False
  With Sheets("17-11-2024")
    For i = 1 To NumberOfCopies
      .Copy After:=Sheets(.Index + i - 1)
      Sheets(.Index + i).Name = Format(DateValue(.Name) + i, "dd-mm-yyyy")
    Next i
  End With
  Application.ScreenUpdating = True
End Sub

 

[Disastrouscoder]

Welcome to the MrExcel board!
Try something like this with a copy of your workbook.

Sub Copy_Sheets()
  Dim i As Long
 
  Const NumberOfCopies As Long = 5  '<- Change to how many copied you want
 
  Application.ScreenUpdating = False
  With Sheets("17-11-2024")
    For i = 1 To NumberOfCopies
      .Copy After:=Sheets(.Index + i - 1)
      Sheets(.Index + i).Name = Format(DateValue(.Name) + i, "dd-mm-yyyy")
    Next i
  End With
  Application.ScreenUpdating = True
End Sub

Awesome! This works. Just a quick question: If I want to jump the next date to be 7 days away, so sheet1 is 01-08-2025 and sheet2 is 01-15-2025 and so on, how would we edit the code? Thank you for your help on this!!

 

[Disastrouscoder]

Awesome! This works. Just a quick question: If I want to jump the next date to be 7 days away, so sheet1 is 01-08-2025 and sheet2 is 01-15-2025 and so on, how would we edit the code? Thank you for your help on this!!

Figured it out. Thank you for the code. It worked great!!! I just have to change the default sheet each time I need to make copies unless I want to make them all at once, but otherwise its good. Thanks!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

I just have to change the default sheet each time I need to make copies

Would it be starting from the last sheet in the workbook each time? If so, try a change like this ..

With Sheets("17-11-2024")
With Sheets(Sheets.Count)