Can Macro Recorder pick up Defined Names?

[smiley3141]

I recorded a Macro and it functions properly. The problem is that I had given a name to every range that the Macro performs an action on, but the Macro recorder still referred to ranges by their Cell names (for example "D5:D8"). Is there some way to make the Macro Recorder use the Defined Names instead of having to go in and change them all, or do I need to change them manually?

I appreciate any help you have for me.

 

[AlphaFrog]

When you record the macro, instead of clicking on the cells, select from the menu Edit\ Goto\ Your_Named_Range. Then your named range will be recorded in your macro.

Or you can select Your_Named_Range from the name drop-down list to the left of the formula bar.

 

[smiley3141]

Thank you AlphaFrog, for your help. Once I have done that, how do I perform an action on the Goto Reference?

On my spreadsheet page Column J is named "Database_Comments". When I used the drop down list to select this name and then tried to autofit the column width, the macro recorder came up with the following:

Application.Goto Reference:="Database_Comments"
Columns("J:J").EntireColumn.AutoFit

How can I get the autofit to work without reference to "J:J"? I have tried putting "Database_Comments" in the second line above but have not been able to make that work.

 

[wigi]

Range("Database_Comments").EntireColumn.AutoFit

 

[smiley3141]

Thanks. That is what I tried at first when I entered the Range names for all the cell selections I did but did not have any luck.

It seems to be working now. Must have mistyped it before. Thank you for your help.

Now that the problem is solved, do you know how I could activate (or select) the go to reference, so I do not have to go back and type in the names later?

 

[wigi]

Now that the problem is solved, do you know how I could activate (or select) the go to reference, so I do not have to go back and type in the names later?

Maybe I miss something here, but is that so much of a problem?

In fact, the macro recorder is there to help you, not to "write" code. You being the developer/programmer - write the code.

You can for instance copy my line of code and paste it a number of times. Then, replace the named range with something else. The macro will continue to work

 

[smiley3141]

You are right Wigi. I am the programmer, and the Recorder is there to help, not to write the code. It is not too difficult to manually enter the names. I was asking because I want to improve efficiency (kind of like learning keyboard shortcuts) and thought there might be a way to do this simple task automatically. If there is not, that is fine. If there is, however, I don't want to waste all the time manually inputting names.

 

[wigi]

You are right Wigi. I am the programmer, and the Recorder is there to help, not to write the code. It is not too difficult to manually enter the names. I was asking because I want to improve efficiency (kind of like learning keyboard shortcuts) and thought there might be a way to do this simple task automatically. If there is not, that is fine. If there is, however, I don't want to waste all the time manually inputting names.

Run this maco, look in the Immediate window (press Ctrl-G) and then the shortcuts Ctrl-C and Ctrl-V will be the only ones needed...

Sub tst()
For Each nm In ThisWorkbook.Names
    Debug.Print "Range(""" & nm.Name & """).EntireColumn.AutoFit"
Next
End Sub

 

[smiley3141]

Thank you, Wigi and AlphaFrog for you help.