How do I copy a worksheet for each day of the year?

[Kaybay]

I'm trying to create a new sheet for each day of the year. It needs to
contain the day and date but I really don't want to copy and paste 2 years of
forms and manually change the date. Please can anybody help.

[Harald Staff]

Hi

Run this littme macro when your master sheet is the active sheet:

Sub MakeYear()
Dim SH As Worksheet
Dim D As Date, Y As Long
Set SH = ActiveSheet
Y = Val(InputBox("Year:"))
If Y < 2000 Then Exit Sub
If Y > 2100 Then Exit Sub
Application.ScreenUpdating = False
For D = DateSerial(Y, 1, 1) To DateSerial(Y, 12, 31)
Application.StatusBar = D
SH.Copy after:=Sheets(Sheets.Count)
ActiveSheet.Range("A1").Value = D
ActiveSheet.Name = Format(D, "mmm dd")
Next
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub

HTH. best wishes Harald

"Kaybay" <Kaybay@discussions.microsoft.com> skrev i melding
news:FEF358D1-EACE-4404-B862-C1757C21C54E@microsoft.com...
> I'm trying to create a new sheet for each day of the year. It needs to
> contain the day and date but I really don't want to copy and paste 2 years
> of
> forms and manually change the date. Please can anybody help.

[Kaybay]

Hi Harold,

It's like magic!
Thank you very much. I really appreciate the help.

Kay

"Harald Staff" wrote:

> Hi
>
> Run this littme macro when your master sheet is the active sheet:
>
> Sub MakeYear()
> Dim SH As Worksheet
> Dim D As Date, Y As Long
> Set SH = ActiveSheet
> Y = Val(InputBox("Year:"))
> If Y < 2000 Then Exit Sub
> If Y > 2100 Then Exit Sub
> Application.ScreenUpdating = False
> For D = DateSerial(Y, 1, 1) To DateSerial(Y, 12, 31)
> Application.StatusBar = D
> SH.Copy after:=Sheets(Sheets.Count)
> ActiveSheet.Range("A1").Value = D
> ActiveSheet.Name = Format(D, "mmm dd")
> Next
> Application.StatusBar = False
> Application.ScreenUpdating = True
> End Sub
>
> HTH. best wishes Harald
>
> "Kaybay" <Kaybay@discussions.microsoft.com> skrev i melding
> news:FEF358D1-EACE-4404-B862-C1757C21C54E@microsoft.com...
> > I'm trying to create a new sheet for each day of the year. It needs to
> > contain the day and date but I really don't want to copy and paste 2 years
> > of
> > forms and manually change the date. Please can anybody help.
>
>
>

[Harald Staff]

Glad it worked for you Kay.
(Your boss will understand that a job like that would take at least 2-3 long
unpaid evenings, and buy you an expensive dinner :-)

Best wishes Harald

"Kaybay" <Kaybay@discussions.microsoft.com> skrev i melding
news:14D4815A-22B6-4E1B-A029-7604B9958E57@microsoft.com...
> Hi Harold,
>
> It's like magic!
> Thank you very much. I really appreciate the help.
>
> Kay
>
> "Harald Staff" wrote:
>
> > Hi
> >
> > Run this littme macro when your master sheet is the active sheet:
> >
> > Sub MakeYear()
> > Dim SH As Worksheet
> > Dim D As Date, Y As Long
> > Set SH = ActiveSheet
> > Y = Val(InputBox("Year:"))
> > If Y < 2000 Then Exit Sub
> > If Y > 2100 Then Exit Sub
> > Application.ScreenUpdating = False
> > For D = DateSerial(Y, 1, 1) To DateSerial(Y, 12, 31)
> > Application.StatusBar = D
> > SH.Copy after:=Sheets(Sheets.Count)
> > ActiveSheet.Range("A1").Value = D
> > ActiveSheet.Name = Format(D, "mmm dd")
> > Next
> > Application.StatusBar = False
> > Application.ScreenUpdating = True
> > End Sub
> >
> > HTH. best wishes Harald
> >
> > "Kaybay" <Kaybay@discussions.microsoft.com> skrev i melding
> > news:FEF358D1-EACE-4404-B862-C1757C21C54E@microsoft.com...
> > > I'm trying to create a new sheet for each day of the year. It needs
to
> > > contain the day and date but I really don't want to copy and paste 2
years
> > > of
> > > forms and manually change the date. Please can anybody help.
> >
> >
> >

[David]

That is just brilliant!
What would I have to change to do a particular month instead of a whole year?

David

"Harald Staff" wrote:

> Hi
>
> Run this littme macro when your master sheet is the active sheet:
>
> Sub MakeYear()
> Dim SH As Worksheet
> Dim D As Date, Y As Long
> Set SH = ActiveSheet
> Y = Val(InputBox("Year:"))
> If Y < 2000 Then Exit Sub
> If Y > 2100 Then Exit Sub
> Application.ScreenUpdating = False
> For D = DateSerial(Y, 1, 1) To DateSerial(Y, 12, 31)
> Application.StatusBar = D
> SH.Copy after:=Sheets(Sheets.Count)
> ActiveSheet.Range("A1").Value = D
> ActiveSheet.Name = Format(D, "mmm dd")
> Next
> Application.StatusBar = False
> Application.ScreenUpdating = True
> End Sub
>
> HTH. best wishes Harald
>
> "Kaybay" <Kaybay@discussions.microsoft.com> skrev i melding
> news:FEF358D1-EACE-4404-B862-C1757C21C54E@microsoft.com...
> > I'm trying to create a new sheet for each day of the year. It needs to
> > contain the day and date but I really don't want to copy and paste 2 years
> > of
> > forms and manually change the date. Please can anybody help.
>
>
>

[David]

This is the code I am using, I just can't figure out how to get the new
worksheets to be the same as the first worksheet:

Sub CreateWorksheetsByDate()

Dim myDate As Variant
Dim iCtr As Long
Dim myStr As String
Dim testwks As Worksheet
Dim SH As Worksheet
Set SH = ActiveSheet
myDate = InputBox(Prompt:="Enter the first day of the Month you want to
Create", _
Default:=Format(Date, "mm/dd/yy"))
'Default:=Format(Date, "mmmm dd, yyyy"))
If IsDate(myDate) = False Then
MsgBox "Please try later"
Exit Sub
End If

Application.ScreenUpdating = False

myDate = CDate(myDate)

For iCtr = DateSerial(Year(myDate), Month(myDate), 1) _
To DateSerial(Year(myDate), Month(myDate) + 1, 0)
Select Case Weekday(iCtr)
'Case Is = vbSunday, vbSaturday (Does all days, remove ' does
only weekdays)
'do nothing
Case Else
'myStr = Format(iCtr, "yyyy_mm_dd_dddd")
myStr = Format(iCtr, "dddd mm-dd")
Set testwks = Nothing
On Error Resume Next
Set testwks = Worksheets(myStr)
On Error GoTo 0

If testwks Is Nothing Then
Set testwks _
= Worksheets.Add(after:=Worksheets(Worksheets.Count))
testwks.Name = myStr
End If
End Select
Next iCtr

Worksheets("Setup").Activate

Application.ScreenUpdating = True


End Sub

Thanks!

"Harald Staff" wrote:

> Glad it worked for you Kay.
> (Your boss will understand that a job like that would take at least 2-3 long
> unpaid evenings, and buy you an expensive dinner :-)
>
> Best wishes Harald
>
> "Kaybay" <Kaybay@discussions.microsoft.com> skrev i melding
> news:14D4815A-22B6-4E1B-A029-7604B9958E57@microsoft.com...
> > Hi Harold,
> >
> > It's like magic!
> > Thank you very much. I really appreciate the help.
> >
> > Kay
> >
> > "Harald Staff" wrote:
> >
> > > Hi
> > >
> > > Run this littme macro when your master sheet is the active sheet:
> > >
> > > Sub MakeYear()
> > > Dim SH As Worksheet
> > > Dim D As Date, Y As Long
> > > Set SH = ActiveSheet
> > > Y = Val(InputBox("Year:"))
> > > If Y < 2000 Then Exit Sub
> > > If Y > 2100 Then Exit Sub
> > > Application.ScreenUpdating = False
> > > For D = DateSerial(Y, 1, 1) To DateSerial(Y, 12, 31)
> > > Application.StatusBar = D
> > > SH.Copy after:=Sheets(Sheets.Count)
> > > ActiveSheet.Range("A1").Value = D
> > > ActiveSheet.Name = Format(D, "mmm dd")
> > > Next
> > > Application.StatusBar = False
> > > Application.ScreenUpdating = True
> > > End Sub
> > >
> > > HTH. best wishes Harald
> > >
> > > "Kaybay" <Kaybay@discussions.microsoft.com> skrev i melding
> > > news:FEF358D1-EACE-4404-B862-C1757C21C54E@microsoft.com...
> > > > I'm trying to create a new sheet for each day of the year. It needs
> to
> > > > contain the day and date but I really don't want to copy and paste 2
> years
> > > > of
> > > > forms and manually change the date. Please can anybody help.
> > >
> > >
> > >
>
>
>

[David]

Got it! This is what I did:
Sub MakeMonth()
Dim SH As Worksheet
Dim myDate As Variant
Dim D As Date, Y As Long
Set SH = ActiveSheet

myDate = InputBox(Prompt:="Enter the first day of the Month you want to
Create", _
Default:=Format(Date, "mm/dd/yy"))
'If Y < 2000 Then Exit Sub
'If Y > 2100 Then Exit Sub
Application.ScreenUpdating = False
myDate = CDate(myDate)

'For D = DateSerial(Y, 1, 1) To DateSerial(Y, 12, 31)
For iCtr = DateSerial(Year(myDate), Month(myDate), 1) To
DateSerial(Year(myDate), Month(myDate) + 1, 0)
Application.StatusBar = D
SH.Copy after:=Sheets(Sheets.Count)
'ActiveSheet.Range("A1").Value = D
ActiveSheet.Name = Format(iCtr, "dddd mm-dd")
Next
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub

Thanks again for the initial code!

"Harald Staff" wrote:

> Glad it worked for you Kay.
> (Your boss will understand that a job like that would take at least 2-3 long
> unpaid evenings, and buy you an expensive dinner :-)
>
> Best wishes Harald
>
> "Kaybay" <Kaybay@discussions.microsoft.com> skrev i melding
> news:14D4815A-22B6-4E1B-A029-7604B9958E57@microsoft.com...
> > Hi Harold,
> >
> > It's like magic!
> > Thank you very much. I really appreciate the help.
> >
> > Kay
> >
> > "Harald Staff" wrote:
> >
> > > Hi
> > >
> > > Run this littme macro when your master sheet is the active sheet:
> > >
> > > Sub MakeYear()
> > > Dim SH As Worksheet
> > > Dim D As Date, Y As Long
> > > Set SH = ActiveSheet
> > > Y = Val(InputBox("Year:"))
> > > If Y < 2000 Then Exit Sub
> > > If Y > 2100 Then Exit Sub
> > > Application.ScreenUpdating = False
> > > For D = DateSerial(Y, 1, 1) To DateSerial(Y, 12, 31)
> > > Application.StatusBar = D
> > > SH.Copy after:=Sheets(Sheets.Count)
> > > ActiveSheet.Range("A1").Value = D
> > > ActiveSheet.Name = Format(D, "mmm dd")
> > > Next
> > > Application.StatusBar = False
> > > Application.ScreenUpdating = True
> > > End Sub
> > >
> > > HTH. best wishes Harald
> > >
> > > "Kaybay" <Kaybay@discussions.microsoft.com> skrev i melding
> > > news:FEF358D1-EACE-4404-B862-C1757C21C54E@microsoft.com...
> > > > I'm trying to create a new sheet for each day of the year. It needs
> to
> > > > contain the day and date but I really don't want to copy and paste 2
> years
> > > > of
> > > > forms and manually change the date. Please can anybody help.
> > >
> > >
> > >
>
>
>

[dnusz]

I have used this help to create a worksheet for each day. Now my issue that I need a worksheet that can grow and update. We collect data from 60 oil wells. That list is not static. We continually add wells. So, how can I add wells and have them update going forward on future worksheets w/o have to copy/paste 100s of times.

[arlu1201]

Welcome to the forum, dnusz.

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.