getting Subtotal method of Range class failed error

[CStumpo]

My macro for sheets VP and VP2 that subtotals results of a query was working fine. The workbook has 2 sheet, VP and VP2 which are subtotaled.
I needed to add a copy function to sheet VP to copy the subtotal lines to the top of the sheet (beginning at row 5). This may or may not be working because I now get the subtotal method of range error and the macro doesn't run. I am not sure why this now doesn't work. Any help would be appreciated. The data is supplied to the file via a query from our financial system.

Sub AddSum()
'
' AddSum Macro
'
Sheets("VP").Select
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(7, 8, 9, 10, 11)
'
Dim b As Range
For Each b In Range("VP")
    If b.Value Like "*Public,John Q. Total*" Then
        Cells(b.Row).EntireRow.Copy
        Cells(b.Row, 5).PasteSpecial _
 Operation:=xlPasteSpecialOperationNone
 End If
 Next b
'
'
Sheets("VP2").Select
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(7, 8, 9, 10, 11)
End Sub

 

[dmt32]

Hi,
try changing this part of your code:

  Cells(b.Row, 5)

to this:

 Cells(5,1)

Dave

 

[CStumpo]

thank you but the same error is returned.

 

[CStumpo]

The original macro was working, maybe by chance. The Code is below. It just subtotaled the two worksheets by the second column.

Sub AddSum()
'
' AddSum Macro
'
Sheets("VP").Select
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(7, 8, 9, 10, 11)
'
Sheets("VP2").Select
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(7, 8, 9, 10, 11)
End Sub

I now have inserted rows at the top of worksheet VP and need to bring the subtotals (column 2) up. Column 2 is persons name. Management wants to see the subtotals at the top as well as in the data. But before I can update the macro for that change - the existing macro isn't working. I tried adding the ranges to the select but no luck. Revised code is below

Sub AddSum()
'
' AddSum Macro
'
'Sheets("VP").Range("VP").Select
ActiveSheet("VP").Range("VP").Select
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(7, 8, 9, 10, 11)
'
'Sheets("VP2").Range("VP_2").Select
Application.Goto(ActiveWorkbook.Sheets("VP2").Range("VP_2")).Select
ActiveSheet("VP2").Range("VP_2").Select
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(7, 8, 9, 10, 11)
End Sub