How to run macros on a different sheet.

[QueensBlvd]

I have three sheets with the following titles "Total", "Inpt", and "Outpt".

I've got three individual macros that reformat each sheet. However, they only work when I'm on the sheet I'm reformatting. I would like to create one macro to run all of them from the "Total" tab. I've tried Application.Run with no luck. Does anybody have any ideas? Two of my codes are below. Even just trying to run the Inpt macro on the Outpt tab gives me a Run time error 1004. Thanks!

Sub Inpt()Dim Inpt As Worksheet
Set Inpt = ThisWorkbook.Sheets("Inpt")


Dim Days As Integer
Days = InputBox("Days in the last 3 months?")




'SECTION ONE'
Dim x As Long
    For x = 2 To Inpt.UsedRange.Columns.Count
        If Columns(x).Hidden = False Then
        'Hide Second Column'
            Columns(x).Hidden = True
        'Insert Third Column'
            Columns(x + 3).EntireColumn.Insert
        'Copy New Month'
            Range(Cells(33, x + 2), Cells(37, x + 2)).Copy
        'Paste New Month'
            Range(Cells(33, x + 3), Cells(37, x + 3)).PasteSpecial
            Range("A1").Select
        'Clear Contents Total AR and Self Pay'
            Range(Cells(35, x + 3), Cells(36, x + 3)).ClearContents
        'New Month Title'
            Cells(3, x + 3) = MonthName(Month(Date))


'SECTION TWO'
        'Copy Columns'
            Range(Cells(3, x + 6), Cells(33, x + 7)).Copy
        'Paste Columns'
            Cells(3, x + 5).PasteSpecial
            Range("A1").Select
        'Update Formulas'
            Range(Cells(4, x + 5), Cells(33, x + 7)).FormulaR1C1 = "=sum(RC[-6]:RC[-4])"
        'Title New Month'
            Cells(3, x + 7) = MonthName(Month(Date))


'SECTION THREE'
        'Copy Columns'
            Range(Cells(3, x + 10), Cells(33, x + 11)).Copy
        'Paste Columns'
            Range("A1").Select
        'Row 35 Formulas'
            Range(Cells(35, x + 9), Cells(35, x + 11)).FormulaR1C1 = "=R[+2]C[-8] / R[-2]C"
            Range("A1").Select
        'New Month Title'
            Cells(3, x + 11) = MonthName(Month(Date))
        'New Month Formula'
            Range(Cells(4, x + 11), Cells(33, x + 11)).FormulaR1C1 = "=RC[-4] / " & Days & ""
        'Clear Contents'
            Range(Cells(32, x + 5), Cells(32, x + 11)).ClearContents
    Exit For
    End If
    Next x
            
        
            
        
             
End Sub




Sub Outpt()
Dim Outpt As Worksheet
Set Outpt = ThisWorkbook.Sheets("Outpt")


Dim Days As Integer
Days = InputBox("Days in the last 3 months?")




'SECTION ONE'
Dim y As Long
    For y = 2 To Outpt.UsedRange.Columns.Count
        If Columns(y).Hidden = False Then
        'Hide Second Column'
            Columns(y).Hidden = True
        'Insert Third Column'
            Columns(y + 3).EntireColumn.Insert
        'Copy New Month'
            Range(Cells(33, y + 2), Cells(37, y + 2)).Copy
        'Paste New Month'
            Range(Cells(33, y + 3), Cells(37, y + 3)).PasteSpecial
            Range("A1").Select
        'Clear Contents Total AR and Self Pay'
            Range(Cells(35, y + 3), Cells(36, y + 3)).ClearContents
        'New Month Title'
            Cells(3, y + 3) = MonthName(Month(Date))


'SECTION TWO'
        'Copy Columns'
            Range(Cells(3, y + 6), Cells(33, y + 7)).Copy
        'Paste Columns'
            Cells(3, y + 5).PasteSpecial
            Range("A1").Select
        'Update Formulas'
            Range(Cells(4, y + 5), Cells(33, y + 7)).FormulaR1C1 = "=sum(RC[-6]:RC[-4])"
        'Title New Month'
            Cells(3, y + 7) = MonthName(Month(Date))


'SECTION THREE'
        'Copy Columns'
            Range(Cells(3, y + 10), Cells(33, y + 11)).Copy
        'Paste Columns'
            Range("A1").Select
        'Row 35 Formulas'
            Range(Cells(35, y + 9), Cells(35, y + 11)).FormulaR1C1 = "=R[+2]C[-8] / R[-2]C"
            Range("A1").Select
        'New Month Title'
            Cells(3, y + 11) = MonthName(Month(Date))
        'New Month Formula'
            Range(Cells(4, y + 11), Cells(33, y + 11)).FormulaR1C1 = "=RC[-4] / " & Days & ""
        'Clear Contents'
            Range(Cells(32, y + 5), Cells(32, y + 11)).ClearContents
    Exit For
    End If
    Next y
            
        
            
        
             
End Sub

 

[Momentman]

You need to fully qualify your codes, by that I mean, instead of

Range(Cells(33, x + 2), Cells(37, x + 2)).Copy

You should do this

Inpt.Range(Inpt.Cells(33, x + 2), Inpt.Cells(37, x + 2)).Copy

That way it always references cells or ranges on the Inpt sheet even though you are on a different sheet. Do same for the others

 

[QueensBlvd]

Great thanks - I had tried this, but I think I forgot to add the sheet title to the cells value as well.

Thanks!

You need to fully qualify your codes, by that I mean, instead of

Range(Cells(33, x + 2), Cells(37, x + 2)).Copy

You should do this

Inpt.Range(Inpt.Cells(33, x + 2), Inpt.Cells(37, x + 2)).Copy

That way it always references cells or ranges on the Inpt sheet even though you are on a different sheet. Do same for the others

 

[Momentman]

I take it that the issue is resolved?

 

[QueensBlvd]

Yes - thanks for your help!

I take it that the issue is resolved?