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.