Set up spreadsheet with reacurring months

  • Thread starter Thread starter RenoRebel
  • Start date Start date
R

RenoRebel

My employer has asked me to set up a daily report template. On the tab
at the bottom of the page that say "sheet1" "sheet 2" etc. would sa
for example: "6-1-04" 6-2-04" etc. As you click on each day it woul
bring up blank report to fill in the daily figures. I have seen this a
another business. At the end of the month a window pops up and asks yo
if you want to set up another month and after you click yes it sets u
the next month. Could anyone please help me?
Thank You
Regards, Steve:confused
 
Here's one way to create new sheets. You can run it on demand (alt-F8). The
macro expects a master worksheet named "blankForm" to exist and it copies that
as a new sheet and then renames it to the date.

(I like yyyy-mm-dd as my worksheet names. So I used that. Change it if you
hate it.)

Option Explicit
Sub testme()

Dim mstrWks As Worksheet
Dim iCtr As Long
Dim myDate As Variant
Dim newWks As Worksheet
Dim myName As String

Set mstrWks = Worksheets("blankForm")

myDate = InputBox(prompt:="Any date in the month")
If Trim(myDate) = "" Then
Exit Sub
End If

If IsDate(myDate) Then
myDate = CDate(myDate)
Else
Exit Sub
End If

Application.ScreenUpdating = False

For iCtr = DateSerial(Year(myDate), Month(myDate), 1) _
To DateSerial(Year(myDate), Month(myDate) + 1, 0)
myName = Format(iCtr, "yyyy-mm-dd")
If WorksheetExists(myName) Then
'do nothing, well, maybe beep!
Beep
Else
mstrWks.Copy _
after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = myName
End If
Next iCtr

Application.ScreenUpdating = True

End Sub
Function WorksheetExists(SheetName As String, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) > 0)
End Function

And there's Data|form built into excel. Try it manually to see if that's
enough.

Select your range and do Data|Form.

If you like what you got, you can add a procedure to the workbook that pops that
up whenever you activate a sheet.

Right click on the excel icon to the left of File (on the worksheet menubar).
Select View code
and paste this into the code window:

Option Explicit
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.Goto Sh.Range("a1"), scroll:=True
Application.DisplayAlerts = False
Sh.ShowDataForm
Application.DisplayAlerts = True
End Sub


Depending on where your data is (I assumed it starts in A1 and is contiguous),
it might even work!
 
Back
Top