dynamically reference the rows and columns as R78C15

R

ryguy7272

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---
 
I

Ivyleaf

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---
Click to expand...

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.
 
R

ryguy7272

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:=xlDatabase, 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:=xlDatabase, 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").AddDataField
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---
 
I

Ivyleaf

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:=xlDatabase, 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:=xlDatabase, 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").AddDataField
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








- Show quoted text -
Click to expand...

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:=xlDatabase,
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:=xlDatabase,
SourceData:= _

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

Cheers,
Ivan.
 
R

ryguy7272

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:=xlDatabase, SourceData:= _
"MergeData").CreatePivotTable TableDestination:="", TableName _
:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

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


--RyGuy
 
I

Ivyleaf

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:=xlDatabase, SourceData:= _
        "MergeData").CreatePivotTable TableDestination:="", TableName _
        :="PivotTable1", DefaultVersion:=xlPivotTableVersion10

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

--RyGuy













- Show quoted text -
Click to expand...

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.
 
R

ryguy7272

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:=xlDatabase, SourceData:= _
Sheets("MergeSheet").Range("A1").CurrentRegion).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").AddDataField
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---
 
I

Ivyleaf

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:=xlDatabase, SourceData:= _
    Sheets("MergeSheet").Range("A1").CurrentRegion).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").AddDataField
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








- Show quoted text -
Click to expand...

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Realign Columns? Imossible? 6
Pivot Table, Variable Inputs 1
how do i dynamically allot rows in excel 3
Insert column and hide columns 6
Text To Columns then insert rows 9
Dynamic allocation of rows 4
Range passed to a function - trying to just move through the range 4
selecting data for a chart based on specific criterie 2

Top