> I am trying to create a pivot table from multiple arrays in a workbook
(each
> array being in a separate worksheet).

> The code that is created and executed (as the "SourceData") looks like
this
> (I proved it by printing the string before executing it):
> Array(Array("HD Shayvonne!R1C52:R14C53", "Shayvonne"), Array("HD
> Richard!R1C50:R13C51", "Richard"), Array("HD Katie!R1C50:R14C51",
"Katie"))

> It works if I write it out, but if the code builds the string dynamically,
I
> get the following error:
> Run-time error '1004':
> Cannot open PivotTable source file 'Array(Array("HD Shayvonne.xls'.

> Thanks,
> Katie

> Katie

> SourceData doesn't really take a string.  It takes a Variant, which to you
> means that you have to pass it an array, not a string that looks like an
> array.    It kind of sounds like you're trying to pass it a string.  Try
> this

> Dim MySD as Variant

> MySD = Array(Array("HD Shayvonne etc...

> Then you can (I think) set your SourceData to MySD.

> --
>{*filter*} Kusleika
> MVP - Excel
> www.dicks-clicks.com
> Post all replies to the newsgroup.

> > I am trying to create a pivot table from multiple arrays in a workbook
> (each
> > array being in a separate worksheet).

> > The code that is created and executed (as the "SourceData") looks like
> this
> > (I proved it by printing the string before executing it):
> > Array(Array("HD Shayvonne!R1C52:R14C53", "Shayvonne"), Array("HD
> > Richard!R1C50:R13C51", "Richard"), Array("HD Katie!R1C50:R14C51",
> "Katie"))

> > It works if I write it out, but if the code builds the string
dynamically,
> I
> > get the following error:
> > Run-time error '1004':
> > Cannot open PivotTable source file 'Array(Array("HD Shayvonne.xls'.

> > Thanks,
> > Katie

> Thank you for the suggestion.  I changed my array variable to a variant.
> Unfortunately, though, it is still giving the same error message.  Any
other
> ideas?

> Thanks,
> Katie

> > Katie

> > SourceData doesn't really take a string.  It takes a Variant, which to
you
> > means that you have to pass it an array, not a string that looks like an
> > array.    It kind of sounds like you're trying to pass it a string.  Try
> > this

> > Dim MySD as Variant

> > MySD = Array(Array("HD Shayvonne etc...

> > Then you can (I think) set your SourceData to MySD.

> > --
> >{*filter*} Kusleika
> > MVP - Excel
> > www.dicks-clicks.com
> > Post all replies to the newsgroup.

> > > I am trying to create a pivot table from multiple arrays in a workbook
> > (each
> > > array being in a separate worksheet).

> > > The code that is created and executed (as the "SourceData") looks like
> > this
> > > (I proved it by printing the string before executing it):
> > > Array(Array("HD Shayvonne!R1C52:R14C53", "Shayvonne"), Array("HD
> > > Richard!R1C50:R13C51", "Richard"), Array("HD Katie!R1C50:R14C51",
> > "Katie"))

> > > It works if I write it out, but if the code builds the string
> dynamically,
> > I
> > > get the following error:
> > > Run-time error '1004':
> > > Cannot open PivotTable source file 'Array(Array("HD Shayvonne.xls'.

> > > Thanks,
> > > Katie

> Katie

> Just to be clear, don't do this

> MySD = "Array(Array(..."

> do this

> MySD = Array(Array(...

> Note the lack of the quotes around the variable assignment.  I hope I'm
not
> beating that point to death, but I just don't want to overlook something
> simple.

> Tell me exactly what you're doing, ex: manually set up pivot table and
then
> changing, creating pivot table in code, where the data comes from -
external
> or another worksheet.  Post the code you have and I'll play around with
it.

> --
>{*filter*} Kusleika
> MVP - Excel
> www.dicks-clicks.com
> Post all replies to the newsgroup.

> > Thank you for the suggestion.  I changed my array variable to a variant.
> > Unfortunately, though, it is still giving the same error message.  Any
> other
> > ideas?

> > Thanks,
> > Katie

> > > Katie

> > > SourceData doesn't really take a string.  It takes a Variant, which to
> you
> > > means that you have to pass it an array, not a string that looks like
an
> > > array.    It kind of sounds like you're trying to pass it a string.
Try
> > > this

> > > Dim MySD as Variant

> > > MySD = Array(Array("HD Shayvonne etc...

> > > Then you can (I think) set your SourceData to MySD.

> > > --
> > >{*filter*} Kusleika
> > > MVP - Excel
> > > www.dicks-clicks.com
> > > Post all replies to the newsgroup.

> > > > I am trying to create a pivot table from multiple arrays in a
workbook
> > > (each
> > > > array being in a separate worksheet).

> > > > The code that is created and executed (as the "SourceData") looks
like
> > > this
> > > > (I proved it by printing the string before executing it):
> > > > Array(Array("HD Shayvonne!R1C52:R14C53", "Shayvonne"), Array("HD
> > > > Richard!R1C50:R13C51", "Richard"), Array("HD Katie!R1C50:R14C51",
> > > "Katie"))

> > > > It works if I write it out, but if the code builds the string
> > dynamically,
> > > I
> > > > get the following error:
> > > > Run-time error '1004':
> > > > Cannot open PivotTable source file 'Array(Array("HD Shayvonne.xls'.

> > > > Thanks,
> > > > Katie

> Okay, but if I don't use quotes, how do I distinguish between the code
that
> makes the array (ie. Array(Array(...) and the variables that bring in the
> worksheet names, row numbers, column numbers, etc.?  Below is the code
that
> builds the statement, where "wsName" is the name of each worksheet (all
> worksheets are in the same workbook as the code) and "ptDataRow1,"
> "ptDataRow2," & "ptDataCol" are all variables used to define the range of
> data being used in each worksheet...

>     For counter2 = 0 To (counter1 - 1)            '***this loops 3
times***
>         sourceDataStatement(counter2) = "Array(" & Chr$(34) &
> wsName(counter2) & "!R" & ptDataRow1(counter2) & _
>             "C" & ptDataCol(counter2) & ":R" & ptDataRow2(counter2) & "C"
&
> (ptDataCol(counter2) + 1) & Chr$(34) & _
>             ", " & Chr$(34) & Right$(wsName(counter2),
> Len(wsName(counter2)) - 3) & Chr$(34) & ")"
>         MsgBox sourceDataStatement(counter2)
>         Next counter2

>     ptStatement = "Array("

>     For counter2 = 0 To (counter1 - 1)            '***this also loops 3
> times***
>         ptStatement = ptStatement + sourceDataStatement(counter2)
>         If counter2 <> (counter1 - 1) Then
>             ptStatement = ptStatement & ", "
>         Else
>             ptStatement = ptStatement + ")"
>         End If
>         MsgBox ptStatement
>         Next counter2

>     ActiveWorkbook.PivotCaches.Add(SourceType:=xlConsolidation,
SourceData:=
> _
>         ptStatement).CreatePivotTable TableDestination:="",
> TableName:="PivotTable" _
>         & CStr(startDate) & "-" & CStr(endDate)

> --Thank you for all of your help!
> Katie

> > Katie

> > Just to be clear, don't do this

> > MySD = "Array(Array(..."

> > do this

> > MySD = Array(Array(...

> > Note the lack of the quotes around the variable assignment.  I hope I'm
> not
> > beating that point to death, but I just don't want to overlook something
> > simple.

> > Tell me exactly what you're doing, ex: manually set up pivot table and
> then
> > changing, creating pivot table in code, where the data comes from -
> external
> > or another worksheet.  Post the code you have and I'll play around with
> it.

> > --
> >{*filter*} Kusleika
> > MVP - Excel
> > www.dicks-clicks.com
> > Post all replies to the newsgroup.

> > > Thank you for the suggestion.  I changed my array variable to a
variant.
> > > Unfortunately, though, it is still giving the same error message.  Any
> > other
> > > ideas?

> > > Thanks,
> > > Katie

> > > > Katie

> > > > SourceData doesn't really take a string.  It takes a Variant, which
to
> > you
> > > > means that you have to pass it an array, not a string that looks
like
> an
> > > > array.    It kind of sounds like you're trying to pass it a string.
> Try
> > > > this

> > > > Dim MySD as Variant

> > > > MySD = Array(Array("HD Shayvonne etc...

> > > > Then you can (I think) set your SourceData to MySD.

> > > > --
> > > >{*filter*} Kusleika
> > > > MVP - Excel
> > > > www.dicks-clicks.com
> > > > Post all replies to the newsgroup.

> > > > > I am trying to create a pivot table from multiple arrays in a
> workbook
> > > > (each
> > > > > array being in a separate worksheet).

> > > > > The code that is created and executed (as the "SourceData") looks
> like
> > > > this
> > > > > (I proved it by printing the string before executing it):
> > > > > Array(Array("HD Shayvonne!R1C52:R14C53", "Shayvonne"), Array("HD
> > > > > Richard!R1C50:R13C51", "Richard"), Array("HD Katie!R1C50:R14C51",
> > > > "Katie"))

> > > > > It works if I write it out, but if the code builds the string
> > > dynamically,
> > > > I
> > > > > get the following error:
> > > > > Run-time error '1004':
> > > > > Cannot open PivotTable source file 'Array(Array("HD
Shayvonne.xls'.

> > > > > Thanks,
> > > > > Katie

> Katie

> You can't use the Array function in a loop like that.  You need to Dim a
> variable as an array then use the Redim Preserve statement.  I'm sorry I
> can't test this more, but if you still have problems, post back and I will
> be back on line tomorrow.  Look at this code

> Sub pttest2()

> '***This is setup stuff for testing you can ignore
> Dim counter2 As Long
> Dim counter1 As Long
> Dim wsName As Variant
> Dim ptDataCol As Variant
> Dim ptDataRow1 As Variant
> Dim ptDataRow2 As Variant
> Dim ptStatement() As Variant

> counter1 = 3

> wsName = Array("HD Shayvonne", "HD Richard", "HD Katie")
> ptDataCol = Array(1, 1, 1)
> ptDataRow1 = Array(52, 50, 50)
> ptDataRow2 = Array(53, 51, 51)
> '***End of setup stuff for testing

> For counter2 = 0 To (counter1 - 1)
>     ReDim Preserve ptStatement(0 To counter2)

>     ptStatement(counter2) = Array(wsName(counter2) & "!R" & _
>         ptDataRow1(counter2) & "C" & ptDataCol(counter2) & ":R" & _
>         ptDataRow2(counter2) & "C" & (ptDataCol(counter2) + 1), _
>         Right(wsName(counter2), Len(wsName(counter2)) - 3))
> Next counter2

> '***Testing the result - you can ignore below
> For counter2 = 0 To 2
>     Debug.Print ptStatement(counter2)(0), ptStatement(counter2)(1)
> Next counter2

> End Sub

> Now ptStatement is an array that is full of arrays which should be proper
> for the SourceData argument.  Give it a go, post back with questions or
> problems.

> --
>{*filter*} Kusleika
> MVP - Excel
> www.dicks-clicks.com
> Post all replies to the newsgroup.

> > Okay, but if I don't use quotes, how do I distinguish between the code
> that
> > makes the array (ie. Array(Array(...) and the variables that bring in
the
> > worksheet names, row numbers, column numbers, etc.?  Below is the code
> that
> > builds the statement, where "wsName" is the name of each worksheet (all
> > worksheets are in the same workbook as the code) and "ptDataRow1,"
> > "ptDataRow2," & "ptDataCol" are all variables used to define the range
of
> > data being used in each worksheet...

> >     For counter2 = 0 To (counter1 - 1)            '***this loops 3
> times***
> >         sourceDataStatement(counter2) = "Array(" & Chr$(34) &
> > wsName(counter2) & "!R" & ptDataRow1(counter2) & _
> >             "C" & ptDataCol(counter2) & ":R" & ptDataRow2(counter2) &
"C"
> &
> > (ptDataCol(counter2) + 1) & Chr$(34) & _
> >             ", " & Chr$(34) & Right$(wsName(counter2),
> > Len(wsName(counter2)) - 3) & Chr$(34) & ")"
> >         MsgBox sourceDataStatement(counter2)
> >         Next counter2

> >     ptStatement = "Array("

> >     For counter2 = 0 To (counter1 - 1)            '***this also loops 3
> > times***
> >         ptStatement = ptStatement + sourceDataStatement(counter2)
> >         If counter2 <> (counter1 - 1) Then
> >             ptStatement = ptStatement & ", "
> >         Else
> >             ptStatement = ptStatement + ")"
> >         End If
> >         MsgBox ptStatement
> >         Next counter2

> >     ActiveWorkbook.PivotCaches.Add(SourceType:=xlConsolidation,
> SourceData:=
> > _
> >         ptStatement).CreatePivotTable TableDestination:="",
> > TableName:="PivotTable" _
> >         & CStr(startDate) & "-" & CStr(endDate)

> > --Thank you for all of your help!
> > Katie

> > > Katie

> > > Just to be clear, don't do this

> > > MySD = "Array(Array(..."

> > > do this

> > > MySD = Array(Array(...

> > > Note the lack of the quotes around the variable assignment.  I hope
I'm
> > not
> > > beating that point to death, but I just don't want to overlook
something
> > > simple.

> > > Tell me exactly what you're doing, ex: manually set up pivot table and
> > then
> > > changing, creating pivot table in code, where the data comes from -
> > external
> > > or another worksheet.  Post the code you have and I'll play around
with
> > it.

> > > --
> > >{*filter*} Kusleika
> > > MVP - Excel
> > > www.dicks-clicks.com
> > > Post all replies to the newsgroup.

> > > > Thank you for the suggestion.  I changed my array variable to a
> variant.
> > > > Unfortunately, though, it is still giving the same error message.
Any
> > > other
> > > > ideas?

> > > > Thanks,
> > > > Katie

> > > > > Katie

> > > > > SourceData doesn't really take a string.  It takes a Variant,
which
> to
> > > you
> > > > > means that you have to pass it an array, not a string that looks
> like
> > an
> > > > > array.    It kind of sounds like you're trying to pass it a
string.
> > Try
> > > > > this

> > > > > Dim MySD as Variant

> > > > > MySD = Array(Array("HD Shayvonne etc...

> > > > > Then you can (I think) set your SourceData to MySD.

> > > > > --
> > > > >{*filter*} Kusleika
> > > > > MVP - Excel
> > > > > www.dicks-clicks.com
> > > > > Post all replies to the newsgroup.

> > > > > > I am trying to create a pivot table from multiple arrays in a
> > workbook
> > > > > (each
> > > > > > array being in a separate worksheet).

> > > > > > The code that is created and executed (as the "SourceData")
looks
> > like
> > > > > this
> > > > > > (I proved it by printing the string before executing it):
> > > > > > Array(Array("HD Shayvonne!R1C52:R14C53", "Shayvonne"), Array("HD
> > > > > > Richard!R1C50:R13C51", "Richard"), Array("HD

> > > > > "Katie"))

> > > > > > It works if I write it out, but if the code builds the string
> > > > dynamically,
> > > > > I
> > > > > > get the following error:
> > > > > > Run-time error '1004':
> > > > > > Cannot open PivotTable source file 'Array(Array("HD
> Shayvonne.xls'.

> > > > > > Thanks,
> > > > > > Katie