VBA Run-Time Error 1004

[pip74205]

I'm trying to fix a macro that removes the formulas on a specific tab. When I run it, I get the following error displayed: "Run-time error '1004': Select method of Range class failed".
Any idea what I am missing? Also, what would I need to add to the end to have the macro end in cell A1 on the "Manager Summary" tab?

Thanks a lot for any help you can provide.

Sub RemoveFormulas()
'
' RemoveFormulas Macro
'
With Worksheets("Report")
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
End With
'
End Sub

 

[KennyGreens]

I imagine the error is for the .Cells(1).Select part and I also assume that you ran this while a different sheet was active. I'm not sure why you are trying to use select there, but the reason you are getting this error is because you simply cannot select a range in an object that isn't active.

Worksheets("Report").Activate

Adding that right above the select line would fix it. If you want to end the macro in cell A1 of a different sheet, then the code would look something like this:

Sub RemoveFormulas()
'
'RemoveFormulas Macro
'
With Worksheets("Report")
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
End With

Worksheets("Manager Summary").Activate
Worksheets("Manager Summary").Cells(1).Select

End Sub

 

[Fluff]

How about

Sub RemoveFormulas()
'
' RemoveFormulas Macro
'
With Worksheets("Report").UsedRange
   .Value = .Value
End With
Sheets("Manager Summary").Select
Range("A1").Select
End Sub

You would have received the error if the Report sheet was not the active sheet when you ran the macro

 

[pip74205]

How about

Sub RemoveFormulas()
'
' RemoveFormulas Macro
'
With Worksheets("Report").UsedRange
   .Value = .Value
End With
Sheets("Manager Summary").Select
Range("A1").Select
End Sub

You would have received the error if the Report sheet was not the active sheet when you ran the macro

Thank you! This removed the run-time error. However, the data on the Report tab was formatted as a table. This macro removed that. Is it possible to keep the data as a table?

 

[Fluff]

How about

Sub RemoveFormulas()
'
' RemoveFormulas Macro
'
With Worksheets("Report").ListObjects("[COLOR=#ff0000]Table2[/COLOR]").DataBodyRange
   .Value = .Value
End With
Sheets("Manager Summary").Select
Range("A1").Select
End Sub

Change table name to suit

 

[pip74205]

You are wonderful! Thank you so much for your help.

 

[Fluff]

You're welcome