Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Range Name Macro
Learning from this comunity, I am using dynamic range names when importing
data through our ODBC connection, and then creating a pivot table that uses the range name. I works great. In fact, I create new applications like this frequently; so much so that I've created a macro to automate the creation of the dynamic range name. The problem I run into is when the SHEET name has a space in it. If there is no space in the name when I create it, everything works fine. How can I adjust this macro to accomodate a space in the sheet name. Example, If the sheet name is 'MyData', then it works fine. But if it is 'My Data' then the macro doesn't work. Keep in mind that I've just cobbled this together so it could be totally wrong. Dim ws As Worksheet Dim DataName As String Dim Formula As String On Error Resume Next Set ws = ActiveSheet Formula = "=Offset(" & ws.Name & "!R1C1, 0, 0, CountA(" & ws.Name & "!C1), CountA(" & ws.Name & "!R1))" DataName = Application.InputBox("What do you want to call this range of Data? NOTE: Assumes that your database starts in cell A1 of the active worksheet.", "Name your data", "Database" & ActiveWorkbook.Names.Count + 1) ActiveWorkbook.Names.Add Name:=DataName, RefersToR1C1:=Formula End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Range Name Macro
Jonathan,
Use single quote marks around the ws name: Formula = "=Offset('" & ws.Name & "'!R1C1, 0, 0, CountA('" & ws.Name & "'!C1), CountA('" & ws.Name & "'!R1))" HTH, Bernie MS Excel MVP "Jonathan Cooper" wrote in message ... Learning from this comunity, I am using dynamic range names when importing data through our ODBC connection, and then creating a pivot table that uses the range name. I works great. In fact, I create new applications like this frequently; so much so that I've created a macro to automate the creation of the dynamic range name. The problem I run into is when the SHEET name has a space in it. If there is no space in the name when I create it, everything works fine. How can I adjust this macro to accomodate a space in the sheet name. Example, If the sheet name is 'MyData', then it works fine. But if it is 'My Data' then the macro doesn't work. Keep in mind that I've just cobbled this together so it could be totally wrong. Dim ws As Worksheet Dim DataName As String Dim Formula As String On Error Resume Next Set ws = ActiveSheet Formula = "=Offset(" & ws.Name & "!R1C1, 0, 0, CountA(" & ws.Name & "!C1), CountA(" & ws.Name & "!R1))" DataName = Application.InputBox("What do you want to call this range of Data? NOTE: Assumes that your database starts in cell A1 of the active worksheet.", "Name your data", "Database" & ActiveWorkbook.Names.Count + 1) ActiveWorkbook.Names.Add Name:=DataName, RefersToR1C1:=Formula End Sub |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Range Name Macro
Will that work for both ways? When there is no space, and when there is?
"Bernie Deitrick" wrote: Jonathan, Use single quote marks around the ws name: Formula = "=Offset('" & ws.Name & "'!R1C1, 0, 0, CountA('" & ws.Name & "'!C1), CountA('" & ws.Name & "'!R1))" HTH, Bernie MS Excel MVP "Jonathan Cooper" wrote in message ... Learning from this comunity, I am using dynamic range names when importing data through our ODBC connection, and then creating a pivot table that uses the range name. I works great. In fact, I create new applications like this frequently; so much so that I've created a macro to automate the creation of the dynamic range name. The problem I run into is when the SHEET name has a space in it. If there is no space in the name when I create it, everything works fine. How can I adjust this macro to accomodate a space in the sheet name. Example, If the sheet name is 'MyData', then it works fine. But if it is 'My Data' then the macro doesn't work. Keep in mind that I've just cobbled this together so it could be totally wrong. Dim ws As Worksheet Dim DataName As String Dim Formula As String On Error Resume Next Set ws = ActiveSheet Formula = "=Offset(" & ws.Name & "!R1C1, 0, 0, CountA(" & ws.Name & "!C1), CountA(" & ws.Name & "!R1))" DataName = Application.InputBox("What do you want to call this range of Data? NOTE: Assumes that your database starts in cell A1 of the active worksheet.", "Name your data", "Database" & ActiveWorkbook.Names.Count + 1) ActiveWorkbook.Names.Add Name:=DataName, RefersToR1C1:=Formula End Sub |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Range Name Macro
I answered my own question. IT WORKS! Thank you.
"Bernie Deitrick" wrote: Jonathan, Use single quote marks around the ws name: Formula = "=Offset('" & ws.Name & "'!R1C1, 0, 0, CountA('" & ws.Name & "'!C1), CountA('" & ws.Name & "'!R1))" HTH, Bernie MS Excel MVP "Jonathan Cooper" wrote in message ... Learning from this comunity, I am using dynamic range names when importing data through our ODBC connection, and then creating a pivot table that uses the range name. I works great. In fact, I create new applications like this frequently; so much so that I've created a macro to automate the creation of the dynamic range name. The problem I run into is when the SHEET name has a space in it. If there is no space in the name when I create it, everything works fine. How can I adjust this macro to accomodate a space in the sheet name. Example, If the sheet name is 'MyData', then it works fine. But if it is 'My Data' then the macro doesn't work. Keep in mind that I've just cobbled this together so it could be totally wrong. Dim ws As Worksheet Dim DataName As String Dim Formula As String On Error Resume Next Set ws = ActiveSheet Formula = "=Offset(" & ws.Name & "!R1C1, 0, 0, CountA(" & ws.Name & "!C1), CountA(" & ws.Name & "!R1))" DataName = Application.InputBox("What do you want to call this range of Data? NOTE: Assumes that your database starts in cell A1 of the active worksheet.", "Name your data", "Database" & ActiveWorkbook.Names.Count + 1) ActiveWorkbook.Names.Add Name:=DataName, RefersToR1C1:=Formula End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro print range not dynamic like I wanted it to be | Excel Worksheet Functions | |||
macro code to get dynamic range | Excel Discussion (Misc queries) | |||
Dynamic range for autofill macro | Excel Discussion (Misc queries) | |||
How do I create a dynamic range in a macro | Excel Worksheet Functions | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel |