ActiveCell.FormulaR1C1
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
|