NEWBIE Old guy! Need help updating and copying within same sheet. Thanks!

[jtees4]

Hello, I am an older guy, used excel before, still using Excel 2003. I set up one month of a worksheet that tracks my daughters income based on her work hours. I started it this month December 2024. It all works (I admit I am proud), it tells me in both currency and text how many hours she has left to work to meet a certain goal that she can't go over. Now I need to copy the spreadsheet to all the months of 2025 on the same spreadsheet. If I copy and paste I then can manually update the dates plus clear out the inputted data and it works, and truly it's not hard BUT after all this is Excel....I know it can be easily done automatically, and I just don't know how. I'd appreciate any help, and I'll try my best to understand it....thanks!

 

[Logit]

Copy / Paste the following into a regular module :

Option Explicit

Sub CreateSheets()

    Dim Cell    As Range
    Dim RngBeg  As Range
    Dim RngEnd  As Range
    Dim Wks     As Worksheet

        Set RngBeg = Worksheets("Sheet1").Range("A1")
        Set RngEnd = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp)

        ' Exit if the list is empty.
        If RngEnd.Row < RngBeg.Row Then Exit Sub
Application.ScreenUpdating = False
        For Each Cell In Worksheets("Sheet1").Range(RngBeg, RngEnd)
            On Error Resume Next
                ' No error means the worksheet exists.
                Set Wks = Worksheets(Cell.Value)

                ' Add a new worksheet and name it.
                If Err <> 0 Then
                    Set Wks = Worksheets.Add(After:=Worksheets(Worksheets.Count))
                    Wks.Name = Cell.Value
                End If
            On Error GoTo 0
        Next Cell
Application.ScreenUpdating = True
'MakeHeaders
CopyData
End Sub

Sub CopyData()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
On Error GoTo M
Lastrow = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row
Dim ans As String
    For i = 1 To Lastrow
    ans = Sheets("Sheet1").Cells(i, 1).Value
        Sheets("Template").UsedRange.Copy Sheets(ans).Rows(Sheets(ans).Cells(Rows.Count, "A").End(xlUp).Row + 1)
    Next
Application.ScreenUpdating = True

Sheets("Sheet1").Activate
Sheets("Sheet1").Range("A1").Select


Exit Sub

M:
MsgBox "No such sheet as  " & ans & " exist"
Application.ScreenUpdating = True

End Sub

Of course you can use a Command Button attached to the CreateSheets macro to run it.

On a separate sheet named TEMPLATE, create everything you want each month's sheet to have, prior to you entering any data therein. The macro will copy the Template sheet
and transfer that to each month's sheet.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Copy and update automatically days dates on same spreadsheet
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.