Range(K2).Select results in Select method of Range class failed ? [SOLVED]

[Jabba1963]

Hi All,


All I am trying to do is essentially copy'n'paste data between sheets using VBA - something I have written code to do many times before...

only this time I am getting an error on the line:

Range("K2").Select


with the error "Select method of Range class failed"


The sub is in "ASheet" not a module... and invokes when I update/refresh a pivot table in that sheet... the range is NOT part of the pivot table although values in the range to copy do change based on GetPivot functions against that pivot table.


The idea is that the pivot table changes - which updates a range - (all on ASheet) which I then filter on and I copy the filtered range (on ASheet) to a totally different sheet... (AnotherSheet) - all pretty straight forward until this error cropped up and now I am stumped :)

[pre]Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

ActiveSheet.Range("$G$3:$K$100").AutoFilter Field:=3, Criteria1:="TRUE"
Range("G4:K100").Select
Selection.Copy

Sheets("AnotherSheet").Select
Range("K2").Select

ActiveSheet.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

End Sub

[/pre]

Assistance gratefully received

Jabba

 

[NARAYANK991]

Hi Jabba ,


I copied your code , and ran it after replacing the AnotherSheet with the name of a worksheet in my workbook , and I did not get the error you have mentioned.


Can you replace the section of code you have posted with this , and see if you get the same error ?

[pre]

ActiveSheet.Range("$G$3:$K$100").AutoFilter Field:=3, Criteria1:="TRUE"
Range("G4:K100").Select
Selection.Copy

Sheets("AnotherSheet").Range("K2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

[/pre]
Narayan

 

[Jabba1963]

Hi Narayan,


Yes it works many thanks... however the general process fails later in the Another sheet Worksheet Activate routine where I stipulate another cell reference in a similar manner...


This suggests to me that the problem is down to not explicitly stating the sheet in the Range Select statement and Excel getting itself in knots because the routines were all tied up in Sheet specific routines...


So I altered the code to stipulate ActiveSheet.Range("P3").Select directly and this worked...


Where I originally was using 4 lines to do the cut'paste I have now kept your code because it is of course better.


So the lessons learnt for me are:

(i) Be specific - when selecting a range in sheet specific routines

(ii) Why use 4 lines of code when 1 will do


Many thanks and much appreciated

Jabba

 

[Jabba1963]

One final thing... I had a look round the forum and couldn't find it...


How to mark this as SOLVED ? Is this down to me or a forum moderator ?


Thanks again

 

[NARAYANK991]

Hi Jabba ,


Thanks for the feedback.


I have been following the practice of editing the text in the topic title , and including the text [SOLVED] in it.


In case you cannot do this because your 1 hour allowed editing period has elapsed , I will do it.


Narayan