Do you really want to change the ActiveSheet ?
That's what you are probably doing with your modifications...

--
Regards,
Luc.

"Festina Lente"


"Kevin" wrote:

I have code that works using a drop down bax and a paste link in excel... The
code is a follows;

Sub Button9_Click()
' Cost Element Button

'This part of the code clears the other filters
Worksheets("WBS Query").Range("B4,E6,G4,J4,M4").ClearContents



' Sheets("WBS DATA").Select

' Range("R2").Select
' ActiveCell.FormulaR1C1 = "4100"



'This part of the code Copies and pastes the criteria
Worksheets("WBS Data").Range("Q2:T2").Copy
ActiveSheet.Paste Destination:=Worksheets("WBS Data").Range("Q3:T3")

Set Company = Worksheets("WBS Data").Range("R1")
Set Query = Worksheets("WBS Data").Range("WBS_Charges")
Set Criteria = Sheets("WBS Data").Range("r1:R2")

Worksheets("WBS Data").Range("R1").Calculate
Query.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Criteria, _
CopyToRange:=Range("A11:M11"), Unique:=False

'This part of the code copies Q3:T3, pastes it back to Q2:T2 and deletes Q3:T3
Worksheets("WBS Data").Range("Q3:T3").Copy
ActiveSheet.Paste Destination:=Worksheets("WBS Data").Range("Q2:T2")
Worksheets("WBS Data").Range("Q3:T3").Clear

End Sub

This code seems to work fine... But when I try to modify it using;

Sheets("WBS DATA").Select
Range("R2").Select
ActiveCell.FormulaR1C1 = "4100"

it gives me (Run-time error 1004, the extract has a missing or illegal field
name)

I can block out;
Worksheets("WBS Data").Range("R1").Calculate
Query.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Criteria, _
CopyToRange:=Range("A11:M11"), Unique:=False

and force the 4100 into Cell r2, then unblock the code, and Block the
FormulaR1C1 code and it will work... Just not together...

I apploigize for posting this twice, I can't seem to find my first question
in the Seach results...

Thanks in Advance;
Kevin