vba - Using variables in pivot data source -

vba - Using variables in pivot data source -

i replace:

c:\users\test_h\downloads\new folder\[book1.xlsm] , sheet1 variable code below:

activesheet.pivottables("pivottable1").changepivotcache activeworkbook. _ pivotcaches.create(sourcetype:=xldatabase, sourcedata:= _ "c:\users\test_h\downloads\new folder\[book1.xlsm]sheet1!r1c1:r30c14", version _ :=xlpivottableversion14)

i've tried, cannot work:

sub test(sht string, path_name string) activesheet.pivottables("pivottable1").changepivotcache activeworkbook. _ pivotcaches.create(sourcetype:=xldatabase, sourcedata:= _ path_name & sht & !r1c1:r30c14", version _ :=xlpivottableversion14) end sub

you having syntax issues: missing quotation , breaking lines within object reference. these errors caught compiler before run of code. also, utilize cells() reference instead of r1c1 address reference. plus, combining string reference workbook , worksheet range address.

therefore, consider using declared variables (and sure uninitialize):

dim wkb workbook, wks worksheet dim sourcerng range dim newcache pivotcache set wkb = workbooks.open("c:\users\test_h\downloads\new folder\book1.xlsm") set wks = wkb.sheets("sheet1") set sourcerng = wks.range(cells(1, 1), cells(30, 14)) set newcache = activeworkbook.pivotcaches.create( _ sourcetype:=xldatabase, _ sourcedata:=sourcerng, _ version:=xlpivottableversion14) activesheet.pivottables("pivottable1").changepivotcache newcache set wkb = nil set wks = nil set sourcerng = nil set newcache = nil

usually readability helps break arguments different lines. of course of study personal preference.

vba