Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2000 formula to dynamically sum adjacent rows and columns whererows inserted/deleted | Excel Worksheet Functions | |||
Rows to Columns on reference | Excel Worksheet Functions | |||
how do i reference multiple rows/columns with one function? | New Users to Excel | |||
how do i reference multiple rows/columns with one function? | New Users to Excel | |||
Incrementing rows (or columns) to a reference in another worksheet | Excel Worksheet Functions |