Can I use variables in the Sourcedata from a Dynamic Table?? HELPPPPP

[sergiomimo]

How can i do this code works right, I need to use a variable

Act = InputBox("Today", "Now", Thisday) 'here i need to write: 10513 (sheet name)
Range("AA2").Select

This works OK
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="'10513'!R1C23:R117C23").CreatePivotTable TableDestination:="'[Control LE.xls]10513'!R4C27", TableName:="Tabla dinámica3", DefaultVersion:=xlPivotTableVersion10

This dont...ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="' Act '!R1C23:R117C23").CreatePivotTable TableDestination:="'[Control LE.xls]& Act '!R4C27", TableName:="Tabla dinámica3", DefaultVersion:=xlPivotTableVersion10

 

[anvg]

You have a little mistake with using Act variable.
Please, change
SourceData:="' Act '!R1C23:R117C23").CreatePivotTable
To
SourceData:="'" & Act & "'!R1C23:R117C23").CreatePivotTable
Regards,

 

[sergiomimo]

Ill try in this moment

 

[sergiomimo]

Thanks but what do i write in this part : TableDestination:="'[Control LE.xls]& Act '!R4C27"

 

[anvg]

I am sorry. I did not look at end of your code. It is like
Change
TableDestination:="'[Control LE.xls]& Act '!R4C27"
To
TableDestination:="'[Control LE.xls]" & Act & "'!R4C27"

 

[sergiomimo]

i am sorry. I did not look at end of your code. It is like
change
tabledestination:="'[control le.xls]& act '!r4c27"
to
tabledestination:="'[control le.xls]" & act & "'!r4c27"

thanks, that works perfect....