PivotTable source file error

[carcanken]

I have some code that will generate a string that I need to use in a macro created pivot table. The string that gets created is Array(Array("'0342'!A1:AZ50", "0342"), Array(Array("'0370'!A1:AZ50", "0370"), Array(Array("'0411'!A1:AZ50", "0411"), Array(Array("'Sheet1'!A1:AZ50", "Sheet1"))
When I run the macro I get the "Cannot open PivotTable source file error" and it will highlight this section of code.
ActiveWorkbook.PivotCaches.Create(SourceType:=xlConsolidation, SourceData:= _
combarray, Version:=xlPivotTableVersion15). _
CreatePivotTable TableDestination:="[master.xlsm]Sheet1!A1", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion15
The string combarray is where I think the issue is but not sure how to correct it.

Thanks for any help

 

[carcanken]

There is no pivot table in workbook. I currently have 3 worksheets that has data that I need to combine into a pivot table. Sometimes there may be more sheets all with same heading rows.
when I run the code below that was generated from a recorded macro I get the error stated above. If I take out combarray and hard code the string it will work. I am needing it to work with out having to hardcode.

combarray= Array(Array("'0342'!A1:AZ50", "0342"), Array(Array("'0370'!A1:AZ50", "0370"), Array(Array("'0411'!A1:AZ50", "0411"), Array(Array("'Sheet1'!A1:AZ50", "Sheet1"))
Range("A1").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlConsolidation, SourceData:= _
combarray, Version:=xlPivotTableVersion15). _
CreatePivotTable TableDestination:="[master.xlsm]Sheet1!A1", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion15
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _
"Count of Value").Position = 1
'Windows("Pivot Table - Oct 2016 actuals-.xlsx").Activate
Range("M34").Select
Application.Left = 38.5
Application.Top = 57.25
ActiveWorkbook.ShowPivotTableFieldList = True
Application.Left = 1555.75
Application.Top = 32.5
Windows("master.xlsm").Activate
ActiveWindow.ScrollRow = 1
Windows("master.xlsm").Activate
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Value")
.Caption = "Sum of Value"
.Function = xlSum
End With
Range("A6").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Page1").Orientation = _
xlHidden
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Page1")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("Page1").Caption = _
"Delivery Order"
ActiveSheet.PivotTables("PivotTable1").PivotFields("Row").Caption = "Employee"

 

[carcanken]

I have done some more digging and noticed that the string combarray was incorrect. it should have been combarray= Array(Array("'0342'!A1:AZ50", "0342"), Array("'0370'!A1:AZ50", "0370"), Array("'0411'!A1:AZ50", "0411")) but I still get the error when it tries to create the pivot table. I am declaring Dim combarray As String just not sure what I am doing incorrectly.
Any help or pointers would be thankful.

 

[SpillerBD]

try changing the PivotTable name reference.
from

PivotTables("PivotTable1").

to

PivotTables("PivotTable11").

 

[carcanken]

I still get an error. Invalid procedure call or argument. Below is the full code that is being used. Its ugly. The first part generates srtstring which comes out as Array(Array("'0342'!A1:AZ50", "0342"), Array("'0370'!A1:AZ50", "0370"), Array("'0411'!A1:AZ50", "0411"))

Sub Macro4()
'
' Macro4 Macro
'
Dim sheetNames() As String
Dim totalSheets As Integer
Dim sheet As Worksheet
Dim i As Integer
Dim strMessage As String
Dim srtstring As String
Dim strMessage2 As String
Dim Range1 As String
Dim combarray As String

' Store the total number of worksheets
' that are in the current workbook
totalSheets = ActiveWorkbook.Worksheets.Count - 1

' Now redimension the dynamic array
ReDim sheetNames(totalSheets)

' Loop through the worksheets to store the names in the array
For i = 1 To totalSheets
sheetNames(i - 1) = ActiveWorkbook.Worksheets(i).Name
Next 'i

' Loop through the array to add the names to a string
'strMessage = "Array(Array("
strMessage = "Array("
Range1 = "!A1:AZ50"
Const singquote As String = "'"
Const quote As String = """"
Const comma As String = ", "

For i = 0 To totalSheets - 1
'strMessage = strMessage & sheetNames(i) & vbCrLf
strMessage2 = strMessage & quote & singquote & sheetNames(i) & singquote & Range1 & quote & comma & quote & sheetNames(i) & quote & ")" & comma
combarray = combarray & strMessage2
Next i
' Display the worksheet names
combarray = Left(combarray, Len(combarray) - 2) & ")"
srtstring = strMessage & combarray
'MsgBox combarray
'MsgBox srtstring

'
Range("A1").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlConsolidation, SourceData:= _
srtstring, Version:=xlPivotTableVersion15). _
CreatePivotTable TableDestination:="'Sheet1'!A1", TableName:= _
"PivotTable11", DefaultVersion:=xlPivotTableVersion15
ActiveSheet.PivotTables("PivotTable11").DataPivotField.PivotItems( _
"Count of Value").Position = 1
Range("M34").Select
Application.Left = 38.5
Application.Top = 57.25
ActiveWorkbook.ShowPivotTableFieldList = True
Application.Left = 1555.75
Application.Top = 32.5
Windows("master.xlsm").Activate
ActiveWindow.ScrollRow = 1
Windows("master.xlsm").Activate
With ActiveSheet.PivotTables("PivotTable11").PivotFields("Count of Value")
.Caption = "Sum of Value"
.Function = xlSum
End With
Range("A6").Select
ActiveSheet.PivotTables("PivotTable11").PivotFields("Page1").Orientation = _
xlHidden
With ActiveSheet.PivotTables("PivotTable11").PivotFields("Page1")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable11").PivotFields("Page1").Caption = _
"Delivery Order"
ActiveSheet.PivotTables("PivotTable11").PivotFields("Row").Caption = "Employee"
End Sub