I want to create a Marco that will look at dates in a range, and create a new worksheet for every day for the month. The cells of which the dates get pulled will change value depending on user input elsewhere I've already completed. I have the following figure code working, but I want to set a criteria where it only does it by month. So Ideally, marco runs, it's going to create new sheets for each day of the work week for one month. Below is my attempt with the underline portion is me trying to figure out what I need to do.
Sub AddSheets()
Dim xRg As Excel.Range
Dim wSh As Excel.Worksheet
Dim wBk As Excel.Workbook
Set wSh = Worksheets("Current year")
Set wBk = ActiveWorkbook
Application.ScreenUpdating = False
For Each xRg In wSh.Range("B5:F57")
'For Each xRg>= 1 / 1 / ("A1") And xRg.Value <= 1 / 31 / ("A1")= In wSh.Range("B5:F57")
If xRg.Value >= 1 / 1 / ("A1") And xRg.Value <= 1 / 31 / ("A1") Then
With wBk
'If xRg.Value >= 1 / 1 / ("A1") And xRg.Value <= 1 / 31 / ("A1") Then
.Sheets.Add after:=.Sheets(.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = xRg.Text
If Err.Number = 1004 Then
Debug.Print xRg.Value & " already used as a sheet name"
End If
On Error GoTo 0
End With
Next xRg
Application.ScreenUpdating = True
End Sub
The below is the sheet where it's running on. I figure it all out except creating the criteria to only do it with dates of the month of my choice.
Sub AddSheets()
Dim xRg As Excel.Range
Dim wSh As Excel.Worksheet
Dim wBk As Excel.Workbook
Set wSh = Worksheets("Current year")
Set wBk = ActiveWorkbook
Application.ScreenUpdating = False
For Each xRg In wSh.Range("B5:F57")
'For Each xRg>= 1 / 1 / ("A1") And xRg.Value <= 1 / 31 / ("A1")= In wSh.Range("B5:F57")
If xRg.Value >= 1 / 1 / ("A1") And xRg.Value <= 1 / 31 / ("A1") Then
With wBk
'If xRg.Value >= 1 / 1 / ("A1") And xRg.Value <= 1 / 31 / ("A1") Then
.Sheets.Add after:=.Sheets(.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = xRg.Text
If Err.Number = 1004 Then
Debug.Print xRg.Value & " already used as a sheet name"
End If
On Error GoTo 0
End With
Next xRg
Application.ScreenUpdating = True
End Sub
The below is the sheet where it's running on. I figure it all out except creating the criteria to only do it with dates of the month of my choice.