Change code so that Dynamic ranges include sheet name

**dcgrove** · 06-02-2011, 12:06 PM

Hello, I am using the code below to create dynamic named ranges for a table of data. This code works wonderfully if there is only one data table in a workbook, however, if you use it on more than one data table it creates issues. The code creates a two named ranges ,"lrow" and "lcol" to count the last row and column of the worksheet. What I would like it to do is create those named ranges with the worksheet name appended to the front. So if your data table was on a worksheet named "data" the name of the two ranges would be "datalrow" and "datalcol". Modifying it in this way would allow you to use it on as many data tables you need in the same workbook.

Please Login or Register  to view this content.

Thanks for any help you can provide!
Clayton Grove

**MarvinP** · 06-02-2011, 12:17 PM

Hi dcgrove,

I recorded a macro and created two names. The first was a Workbook name and the second was a WorkSheet name. Then I stopped recording and looked at the recorded macro. This gave me the answer you are looking for.

When you code

Please Login or Register  to view this content.

it adds a workbook name. If you want to add a worksheet name you need to use

Please Login or Register  to view this content.

**dcgrove** · 06-02-2011, 12:21 PM

Marvin, thanks for the suggestion! The worksheet name will not always be "data". I used that name as an example. The macro will need to be able to determine the name of the worksheet it is being run on and append that name to the front of "lcol" or "lrow". Unfortunately, that is not something I know how to write.

**MarvinP** · 06-02-2011, 12:27 PM

Hi,
Simply grab the sheet name in a string like

Please Login or Register  to view this content.

Then use that in the code like

Please Login or Register  to view this content.

does that help?

If it isn't the ActiveSheet and you know it is the 4th sheet tab you can use

Please Login or Register  to view this content.

**dcgrove** · 06-02-2011, 01:44 PM

Ok, I have been playing around and have been able to get the "lcol" and "lrow" names to be named with the sheet name appended, as well as the rest of the named ranges. However, I am not sure how to edit the lines below so that it uses the sheetname&lcol or sheetname&lrow ranges in the formulas. Below is my updated code and an example workbook that I ran the code on so you can see the named ranges it is creating.

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,shtname & lrow)

Please Login or Register  to view this content.

**dcgrove** · 06-02-2011, 03:51 PM

Ok, I fiddled with this some more and figured out how to accomplish what I need. This is the code I am using now.

Please Login or Register  to view this content.

Now I just need to figure out how to determine if the sheet name contains spaces so I can use the replace function to replace the spaces with and underscore.