Share via

I have created the macro below for a time tracker. However I want to know the easiest way to copy the module to other sheets.

Anonymous
2023-07-26T21:49:05+00:00

This is the macro that was created on the sheet "T&M". How do I modify it to copy to another sheet in the same workbook?

Sub Intialize()

Dim iRow As Long

iRow = Sheets("T&M").Range("H" & Application.Rows.Count).End(xlUp).Row + 1

'Code to Validate

If Sheets("T&M").Range("F" & iRow).Value = "" Then

    Sheets("T&M").Range("B" & iRow).Value = Format([Today()], "DD-MMM-YYYY")

    Sheets("T&M").Range("C" & iRow).Value = Application.UserName

End If

End Sub

Sub Start_Time()

Dim iRow As Long

iRow = Sheets("T&M").Range("H" & Application.Rows.Count).End(xlUp).Row + 1

'Code to Validate

If Sheets("T&M").Range("D" & iRow).Value = "" Then

    MsgBox "Please select the Task Name from the drop down.", vbOKOnly + vbInformation, "Task Name Blank"

    Sheets("T&M").Range("D" & iRow).Select

    Exit Sub

ElseIf Sheets("T&M").Range("F" & iRow).Value <> "" Then

    MsgBox "Start Time is aleady captured for the selected Task."

    Exit Sub

Else

    Sheets("T&M").Range("F" & iRow).Value = [Now()]

    Sheets("T&M").Range("F" & iRow).NumberFormat = "hh:mm:ss AM/PM"

End If

End Sub

Sub End_Time()

Dim iRow As Long

iRow = Sheets("T&M").Range("H" & Application.Rows.Count).End(xlUp).Row + 1

'Code to Validate

If Sheets("T&M").Range("F" & iRow).Value = "" Then

    MsgBox "Start Time has not been captured for this task."

    Exit Sub

Else

    Sheets("T&M").Range("G" & iRow).Value = [Now()]

    Sheets("T&M").Range("G" & iRow).NumberFormat = "hh:mm:ss AM/PM"

    Sheets("T&M").Range("H" & iRow).Value = Sheets("T&M").Range("G" & iRow).Value - Sheets("T&M").Range("F" & iRow).Value

    Sheets("T&M").Range("H" & iRow).NumberFormat = "hh:mm:ss"

End If

'Fill the Date and Name in next row

Call Intialize

End Sub

Microsoft 365 and Office | Excel | For home | Other

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments

3 answers

Sort by: Most helpful
  1. Anonymous
    2023-07-27T04:22:29+00:00

    "T&M"

    if you can not run codes on other sheet,how about replace all.data.in sheet "T&M" and run the old codes.

    This way you just move data rather than duplicate vba codes which need to be modified for another sheet?

    Was this answer helpful?

    0 comments No comments
  2. Anonymous
    2023-07-26T22:44:41+00:00

    Hi,

    Thanks for responding. For more context the code was initially created in Module 1. What happens is that whenever I attempt to recreate the sheet, "T&M" and rename it "DAVID" as another sheet in the workbook, I get a run time error 1004 which references the highlighted line in the code (see screenshot).

    Was this answer helpful?

    0 comments No comments
  3. Anonymous
    2023-07-26T22:04:50+00:00

    Hi,

    You could put it in a general module and access it from any sheet. Go to the Visual Basic Editor and choose Insert, Module

    Then cut and paste the code from the sheet to Module1.

    If you only want the code accessible from a few sheets you can copy and paste it into another code sheet, for example from Sheet1 to Sheet3 in the above diagram.

    Was this answer helpful?

    0 comments No comments