Thanks again Ivan! You've restored my hope, but it still is not working.
Unless I did something wrong, the offset function didn't see to work. When I
ran the macro, I got a run-time error 1004. The message reads "this command
requires at least two rows of source data. You cannot use the command on a
selection in only one row."

I created this function in E1:
="MergeSheet!R1C1:R"&AA2&"C"&AA1
It reads as follows:
MergeSheet!R1C1:R78C15
I named E1 MergeData, and used this piece of code:
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"MergeData").CreatePivotTable TableDestination:="", TableName _
:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

Still nothing...
I'm stumped again...


--RyGuy


"Ivyleaf" wrote:

On Apr 5, 3:35 am, ryguy7272
wrote:
Thanks Ivan, but I don't think that will work. I think it is more
complicated than that. Here is a line of code.

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"MergeSheet!R1C1:R78C15").CreatePivotTable TableDestination:="",
TableName _
:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

All I need to do is dynamically reference this:
R78C15

The entire code is below:
Sub PivotTableInputs()

Sheets("MergeSheet").Select
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"MergeSheet!R1C1:R78C15").CreatePivotTable TableDestination:="",
TableName _
:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select

With
ActiveSheet.PivotTables("PivotTable1").PivotFields (Sheets("Summary").Range(Â*"C5").Value)
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataFiel d
ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(Sheets("Summary").Range ("C5").Value),
"Count of ", xlCount

With
ActiveSheet.PivotTables("PivotTable1").PivotFields (Sheets("Summary").Range(Â*"C5").Value)
.Orientation = xlRowField
.Position = 1
End With

ActiveSheet.PivotTables("PivotTable1").PivotFields (Sheets("Summary").Range(Â*"C5").Value).AutoSort _
xlDescending, "Count of "

Charts.Add
With ActiveChart.ChartGroups(1)
.Overlap = 100
.GapWidth = 0
.HasSeriesLines = False
.VaryByCategories = False
End With
End Sub

I tested several scenarios, by manually changing that R78C15 part.
This shoudl be the last step; I just have to get this resolved.
Any other thoughts?

Regards,
Ryan---

--
RyGuy



"Ivyleaf" wrote:
On Apr 5, 2:59 am, ryguy7272
wrote:
On a sheet called Summary, I used the counta function to get the number of
columns and the number or rows that are used in MergeSheet. There are 78
rows and 15 columns. However, this number will change constantly. How can I
dynamically reference the rows and columns as R78C15?
"MergeSheet!R1C1:R78C15")

Thanks,
Ryan---

--
RyGuy

Hi Ryan,

From what you have said, I think a dynamic named range would be the
best solution. Go to 'Insert' - 'Name' - 'Define'. Type a name in
the box (MergeData or something) and then this in the 'Refers to:'
box:

=OFFSET(MergeSheet!$A$1,0,0,COUNTA(MergeSheet!$A:$ A),COUNTA(MergeSheet!
$1:$1))

Whenever you want to reference the data, just use the name of the
range in the formula. For example =SUM(INDEX(MergeData,,2)) would give
you the sum of the numbers in column 2 of the range.

Cheers,
Ivan.- Hide quoted text -

- Show quoted text -

Hi RyGuy,

I actually still think the dynamic range will work for you. If you add
the name like I suggested, all you would have to change your code to
would be:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:= _
"MergeData").CreatePivotTable TableDestination:="", TableName
_
:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

That should be it, Excel will know what range you are talking about.
Better still, if you add data to your table, all you will have to do
to your PivotTable is right click on it and hit 'Refresh' and it
should pick up the new data.

Alternatively if you still don't like that idea, you could use
CurrentRegion in your macro as long as you don't have any other data
butting up against your data table:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:= _

Sheets("MergeSheet").Range("A1").CurrentRegion).Cr eatePivotTable _
TableDestination:="", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10

Cheers,
Ivan.