I have the following two problems:

1. I want to substitute the string "A_030716_cl_max_val_EK" with
variable (filestringL) that contains this string. How can I do tha
(see statement following)?

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:
"A_030716_cl_max_val_EK!R1C1:R74C11").CreatePivotT abl
TableDestination:="", _
TableName:="PivotTable2"
DefaultVersion:=xlPivotTableVersion10

2. I want to substitute in the following statement the 9000 with a
integer that contains the value. Any ideas?

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(Members,
Code
-------------------
s Sheet1'!R2C1:R9000C7,5,0)"

Thank you in advance.
G

--
Message posted from http://www.ExcelForum.com

banavas,

You need to concatinate the variables with the strings
with the "&" character. Like:

Dim MyString as String

Mystring = "A_030716_cl_max_val_EK"

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,_
SourceData:=
Mystring & "!R1C1:R74C11").CreatePivotTable _
TableDestination:="", _
TableName:="PivotTable2", _
DefaultVersion:=xlPivotTableVersion10

Dim MyRow as Integer

MyRow = 9000

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(Members,' _
Code: _
--------------------
s Sheet1'!R2C1:R" & MyRow & "C7,5,0)"


-----Original Message-----
I have the following two problems:

1. I want to substitute the
string "A_030716_cl_max_val_EK" with a
variable (filestringL) that contains this string. How
can I do that
(see statement following)?

ActiveWorkbook.PivotCaches.Add(SourceType:=xlData base,
SourceData:=
"A_030716_cl_max_val_EK!R1C1:R74C11").CreatePivot Table
TableDestination:="", _
TableName:="PivotTable2",
DefaultVersion:=xlPivotTableVersion10

2. I want to substitute in the following statement the
9000 with an
integer that contains the value. Any ideas?

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(Members,'
Code:
--------------------
s Sheet1'!R2C1:R9000C7,5,0)"

Thank you in advance.
G.


---
Message posted from http://www.ExcelForum.com/

.

Dim i as long, sStr as String

sStr = "A_030716_cl_max_val_EK"
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
sStr & "!R1C1:R74C11").CreatePivotTable
TableDestination:="", _
TableName:="PivotTable2",
DefaultVersion:=xlPivotTableVersion10

i = 9000

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(Members,'s Sheet1'!R2C1:R" & i & "C7,5,0)"

--
Regards,
Tom Ogilvy


"banavas " wrote in message
...
I have the following two problems:

1. I want to substitute the string "A_030716_cl_max_val_EK" with a
variable (filestringL) that contains this string. How can I do that
(see statement following)?

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
"A_030716_cl_max_val_EK!R1C1:R74C11").CreatePivotT able
TableDestination:="", _
TableName:="PivotTable2",
DefaultVersion:=xlPivotTableVersion10

2. I want to substitute in the following statement the 9000 with an
integer that contains the value. Any ideas?

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(Members,'
Code:
--------------------
s Sheet1'!R2C1:R9000C7,5,0)"

Thank you in advance.
G.


---
Message posted from http://www.ExcelForum.com/