macro to create named ranges based on a header row.

**dcgrove** · 10-14-2009, 03:41 PM

hello, I need a macro that can set up some named ranges using the text in the header row and the sheet name. The header row will always be in row one, but the number of columns will be dynamic. The amount of rows in the range will need to be dynamic also. To clarify, every used cell stating in row 2 in a column will be the named range with the sheetname+text in row one of the column the actual name.

Thanks!
Clayton

**Leith Ross** · 10-14-2009, 06:11 PM

Hello Clayton,

This will create named ranges based on the column headers and rows used in each column. It can be run later if there are any changes to ranges and the named ranges will be updated.

Please Login or Register  to view this content.

**dcgrove** · 10-15-2009, 09:50 AM

Leith, the macro is failing to this error:

\1

On this line:

Please Login or Register  to view this content.

Any ideas why? I have uploaded the test sheet I tried it out on.

I am running the code from my personal.xlsb workbook.

Thanks!

**Leith Ross** · 10-15-2009, 03:54 PM

Hello Clayton,

Problem was that a named range has to start with either a letter or underscore character. So, I switched the order around. The Header appears first then then worksheet name. I used periods instead of underscores to replace the spaces in the worksheet name. The header and worksheet are separated by an underscore. The change has been made to the attached workbook.

Please Login or Register  to view this content.

**dcgrove** · 10-16-2009, 10:01 AM

Leith, this worked great!

Thanks!

**tetreama** · 08-07-2012, 05:43 PM

This is very helpful thread. However, looking at the example file, all the ranges have quotations marks (" ") around them. For example one range refers to "09 28 2009'!$I$2". Do you know how I can change the code to get rid of the quotation marks?

Thanks for your help.

**Cutter** · 08-07-2012, 08:07 PM

Hello tetreama, and welcome to the forum.

Unfortunately you have inadvertently broken one of the forum rules. Please read the following and make the necessary change. Thanks.

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.