TechRepublic member tclere answered my last Excel – Easy VBA question with an indespensible piece of code that I’ve been using to get myself into a lot of trouble with VBA. Fun though!
Now I have several questions:
1) I have code to run the Office Assitant to instruct users how to use an AS/400 Client Access Excel addin. I want to display the Assistant with it’s info at the same time as I execute the addin.
Code:CommandBars(“Client Access”).Controls(“Custom”).Execute
How can I keep the Assistant visible while the user uses the Client Access addin?
2) I recorded a macro to use the Pivot Table Wizard to create an updated copy of a Pivot Table so the user doesn’t have to know how to use the Wizard. The problem is that my data changes row amounts frequently.
Code: ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
“OpenOrders1!R1C1:R7266C72”
The section “R1C1:R7266C72” – Can I change the 7266 number of rows to a variable created by another procedure? If so, how?
3) I have a similar dilemma with an AdvancedFilter procedure.
Code: Range(Cells(1, 1), Cells(lngRowCount, 3)).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range(“L1:L4”), Unique:=True
The Range arguments I tried to make work – they were originally something like Range(“A1:C752”). I get the feeling VBA does not like my variable “lngRowCount” which is created by another procedure and contains a number representing the total rows with data from a certain worksheet. Any way to make this code work with my changing variable?
Let’s see … 150 points per question, plus 50 points to cover taxes and 200 to grow on … that comes to 700.