400 Error in VBA when trying just to select a cell!

phil152003

Board Regular
Joined
Mar 11, 2011
Messages
89
Hi, Below is some code I've been working with. Basically, my macro does the following:
  1. Selects the first option in the page filter of a pivot table
  2. If the results of the pivot table aren't empy then:
  3. Change the filter of a second pivot table (on a different sheet) to match the first filter
  4. Copy these two sheets containing the pivot tables and paste value them into separate sheets (the version that we send out only shows the paste value version so they can't touch the formulas etc)
  5. Save as a file name containing the value in the pivot table filter.
However, after it has paste valued all the data in, it is leaving the entire sheet selected, and this doesn't look great for distribution. All I want to do, is after each paste value on the sheet they will see, is select cell A1 on each sheet to unselect the entire sheet.

I thought I'd done this with the code below, but for some reason it is giving me an error 400 upoon trying to perform the simple task of selecting cell A1! If I remove the bolded code though, the macro works perfectly, the file just doens't look as good.

Code:

Code:
Sub CycleFilter()
 
    Dim p As PivotItem
    With Sheets("Client Detail LIVE").PivotTables("PivotTable1").PageFields("CM Name")
        For Each p In .PivotItems
             .CurrentPage = "" & p & ""
             If WorksheetFunction.CountA(Sheets("Client Detail LIVE").Range("A7:A65536")) <> 0 Then
                Worksheets("Product Details LIVE").PivotTables("PivotTable1").PageFields(1).CurrentPage = "" & p & ""
 
                Sheets("Product Details LIVE").Cells.Copy
                Sheets("Product Details").Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
 
                Sheets("Product Details LIVE").Cells.Copy
                Sheets("Product Details").Cells.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
 
                [B]Sheets("Product Details").Cells(1, 1).Select[/B]
 
                Sheets("Client Detail LIVE").Cells.Copy
                Sheets("Client Detail").Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
 
                Sheets("Client Detail LIVE").Cells.Copy
                Sheets("Client Detail").Cells.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
 
                [B]Sheets("Client Detail").Cells(1, 1).Select[/B]
 
                Sheets("Instructions").Activate
 
                ActiveWorkbook.SaveCopyAs Filename:="G:\Groups\New Business\Portfolio Management Matrix\New Macro Generated PMMs\Commercial\PMM - " & p & ".xls"
            End If
        Next p
    End With
End Sub

If you could shed any light on why this error is occuring that would be great!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
You would need to select the sheet then the cell or

Code:
Application.Goto Sheets("Product Details").Cells(1, 1)
 
Upvote 0
if the sheet "Product Details" is not the currently active sheet, then you cannot select a cell on that sheet.
Must first select the sheet, then select the cell.

But, there is really no need to select the cell on the sheet anyway, other than for appearance's sake.
 
Upvote 0
Thanks both for your help,, works like a treat now!

I have to admit, even though this was probably a stupid error to make, I'm still very much a novice at vba. The code I've come up with is just a mish mash compilation of various other pieces of code I've stolen from other forum posts, modified so that they work in my spreadsheet :)

Once again, thanks for your quick responses!
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top