Excel - Easy VBA: Revisited
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.