Challenging - (at least for me)

[Frank Bacchus]

All:

I have a workbook with two worksheets, a template and a worksheet with a tab label name of 10-02. Each week I create similar worksheets using the template, as the source, in the same workbook and label the tabs consecutively by the date, for the new worksheet name. For example, my first worksheet (as mentioned above) was 10-02. I would create another worksheet and labeled it 10-2, and another called 10-3 etc. They all use the template. Is there a macro I can use to perform this function which would leave out Sat and Sun? See attached.

 

[John Jairo V]

Hi, @Frank Bacchus!

Try this code in a module:

Sub DupSheets()
    Dim sh As Worksheet, i&, mth, yr&
  
    Set sh = Sheets("Template")
  
    mth = Application.InputBox("Put month-year (mm-yyyy)", "mm-yyyy", Format(Now, "mm-yyyy"), Type:=2)
    If mth = False Then Exit Sub
    yr = Split(mth, "-")(1): mth = Split(mth, "-")(0)
  
    With Application
        .ScreenUpdating = False
        For i = 1 To .NetworkDays(DateSerial(yr, mth, 1), DateSerial(yr, mth + 1, 0))
            sh.Copy , Sheets(Sheets.Count)
            ActiveSheet.Name = Format(.WorkDay(DateSerial(yr, mth, 0), i), "mm-dd")
        Next i
      
        Set sh = Nothing
        .ScreenUpdating = True
    End With
End Sub

Blessings!

 

[Frank Bacchus]

Hi, @Frank Bacchus!

Try this code in a module:

Sub DupSheets()
    Dim sh As Worksheet, i&, mth, yr&
 
    Set sh = Sheets("Template")
 
    mth = Application.InputBox("Put month-year (mm-yyyy)", "mm-yyyy", Format(Now, "mm-yyyy"), Type:=2)
    If mth = False Then Exit Sub
    yr = Split(mth, "-")(1): mth = Split(mth, "-")(0)
 
    With Application
        .ScreenUpdating = False
        For i = 1 To .NetworkDays(DateSerial(yr, mth, 1), DateSerial(yr, mth + 1, 0))
            sh.Copy , Sheets(Sheets.Count)
            ActiveSheet.Name = Format(.WorkDay(DateSerial(yr, mth, 0), i), "mm-dd")
        Next i
     
        Set sh = Nothing
        .ScreenUpdating = True
    End With
End Sub

Blessings!

Hi John- Thanks. This will do the job. I appreciate it. One other quick question. I have a cell where that data is entered. But based on the "entered" data I want the user to enter a date in another field. So if in cell A1 the user entered "Waiting on DCS" in cell B1 I want to force them to enter a valid date. How can I do this with a macro..not VB.? Thanks

frank

 

[Hui]

I'm not sure of your reasons but I strongly recommend not segregating your data as you are proposing

I always recommend establishing a data store such as:




This allows you to do easily do Running Totals, Running Averages, MTD, YTD, Life to Date, Between any dates etc reporting.

Once you segregate your data you spend too much time trying to get it back together to do exactly what I suggested above.

 

[Frank Bacchus]

I'm not sure of your reasons but I strongly recommend not segregating your data as you are proposing

I always recommend establishing a data store such as:

View attachment 47202


This allows you to do easily do Running Totals, Running Averages, MTD, YTD, Life to Date, Between any dates etc reporting.

Once you segregate your data you spend too much time trying to get it back together to do exactly what I suggested above.

Thanks, Hui. I am using the form as a both a Data Store and as an input sheet. The ask is a formula to prompt the user for a date only if they entered a specific response in cell A1. So if in A1 the user entered "Waiting on DCS", I need a formula that will require B1 to be populated with a valid date. Let me know if this clarifies it. Thanks.

frank