CODEWITHSUNDEEP
excelvbarange
arks
arks

Reputation: 73

Macro only working in one sheet and not the others despite qualifying ranges in the code

I have a button assigned to this macro in my "Output" tab which works well. When I copy it to other tabs it gives me an "runtime error 1004 application-defined or object-defined error" at the "Worksheets("Output").Range("B5"....." line.

It's my understanding I've qualified the sheet by starting with Worksheets("Output"). Furthermore, later in the code (if I exclude these lines) my macro moves from along various sheets just fine when I qualify them with the Sheets("name of sheet") code.

Also, based on my research, it doesn't look like my code is solely under the sheet.

Sub test()

Dim i As Integer
Dim Rows As Integer
Dim Count As Integer
Dim StartTime As Double
Dim SecondsElapsed As Double

StartTime = Timer   'Remember time when macro starts
Application.ScreenUpdating = False


Count = WorksheetFunction.CountA(Sheets("Input").Range("B:B")) - 2 'Subtracts (2) to exclude two headers above data


'Copies Name and latest rates into Columns AG and AH for troubleshooting against any new re-runs

Worksheets("Output").Range("B5", Range("B5").End(xlDown)).Copy
Worksheets("Output").Range("AG5").PasteSpecial xlPasteValuesAndNumberFormats

Sheets("Output").Range("T5", Range("T5").End(xlDown)).Copy
Sheets("Output").Range("AH5").PasteSpecial xlPasteValuesAndNumberFormats
    
Sheets("Output").Range("A6").Activate
Range(ActiveCell, ActiveCell.End(xlDown).End(xlToRight)).Clear

Upvotes: 0

Views: 56

Answers (1)

Tim Williams
Tim Williams

Reputation: 166196

Sometimes a With Block is useful for adding a sheet reference to every Range() call:

    With ThisWorkbook.Worksheets("Output")
        .Range("B5", .Range("B5").End(xlDown)).Copy
        .Range("AG5").PasteSpecial xlPasteValuesAndNumberFormats
    
        .Range("T5", .Range("T5").End(xlDown)).Copy
        .Range("AH5").PasteSpecial xlPasteValuesAndNumberFormats
        
        .Range(.Range("A6"), .Range("A6").End(xlDown).End(xlToRight)).Clear
    End With

Upvotes: 2

Related Questions