dynamically reference the rows and columns as R78C15
 

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

dynamically reference the rows and columns as R78C15
 

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.

dynamically reference the rows and columns as R78C15
 

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.

dynamically reference the rows and columns as R78C15
 

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.

dynamically reference the rows and columns as R78C15
 

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.

dynamically reference the rows and columns as R78C15
 

On Apr 5, 5:01*am, ryguy7272
wrote:
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.- Hide quoted text -

- Show quoted text -

Hi RyGuy,

What you are basically telling Excel to do is to create a PivotTable
simply from Cell E1. Even though there is the text of a range
description in there, Excel won't understand that. If you go back to
my first post and add the named range as described there, you should
be fine. The actual code of your macro looks fine so you shouldn't
have to touch that. Just redefie the name 'MergeData" to refer to:
=OFFSET(MergeSheet!$A$1,0,0,COUNTA(MergeSheet!$A:$ A),COUNTA(MergeSheet!
$1:$1)) and you should be fine. This is all that's wrong so far as I
can see.

Cheers,
Ivan.

dynamically reference the rows and columns as R78C15
 

Thanks so much for the help Ivan!! Current region seemed to do the trick. I
went with this:
Sub PivotTableInputs()
Sheets("MergeSheet").Select
Cells.Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
Sheets("MergeSheet").Range("A1").CurrentRegion).Cr eatePivotTable _
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 Wit
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've used Named Ranges numerous times before and never encountered issues
like I did today. Ugh!! I'm certainly no Pivot Table expert, but I thought
it would be a little easier to define those inputs. Oh well...

Thanks again for everything!!

Regards,
Ryan---

--
RyGuy


"Ivyleaf" wrote:

On Apr 5, 5:01 am, ryguy7272
wrote:
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.- Hide quoted text -

- Show quoted text -

Hi RyGuy,

What you are basically telling Excel to do is to create a PivotTable
simply from Cell E1. Even though there is the text of a range
description in there, Excel won't understand that. If you go back to
my first post and add the named range as described there, you should
be fine. The actual code of your macro looks fine so you shouldn't
have to touch that. Just redefie the name 'MergeData" to refer to:
=OFFSET(MergeSheet!$A$1,0,0,COUNTA(MergeSheet!$A:$ A),COUNTA(MergeSheet!
$1:$1)) and you should be fine. This is all that's wrong so far as I
can see.

Cheers,
Ivan.

dynamically reference the rows and columns as R78C15
 

On Apr 5, 7:21*am, ryguy7272
wrote:
Thanks so much for the help Ivan!! *Current region seemed to do the trick. *I
went with this:
Sub PivotTableInputs()
Sheets("MergeSheet").Select
* * Cells.Select
* * ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
* * Sheets("MergeSheet").Range("A1").CurrentRegion).Cr eatePivotTable _
* * 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've used Named Ranges numerous times before and never encountered issues
like I did today. *Ugh!! *I'm certainly no Pivot Table expert, but I thought
it would be a little easier to define those inputs. *Oh well...

Thanks again for everything!!

Regards,
Ryan---

--
RyGuy



"Ivyleaf" wrote:
On Apr 5, 5:01 am, ryguy7272
wrote:
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.- Hide quoted text -

- Show quoted text -

Hi RyGuy,

What you are basically telling Excel to do is to create a PivotTable
simply from Cell E1. Even though there is the text of a range
description in there, Excel won't understand that. If you go back to
my first post and add the named range as described there, you should
be fine. The actual code of your macro looks fine so you shouldn't
have to touch that. Just redefie the name 'MergeData" to refer to:
=OFFSET(MergeSheet!$A$1,0,0,COUNTA(MergeSheet!$A:$ A),COUNTA(MergeSheet!
$1:$1)) and you should be fine. This is all that's wrong so far as I
can see.

Cheers,
Ivan.- Hide quoted text -

- Show quoted text -

Hi RyGuy,

Glad to hear you got it working. I agree that although dynamic named
ranges are handy, the can be tricky to get right. Many's the time I
have left out a '$' or something and it has given unpredictable
results.

Cheers,
Ivan.