VBA - creating table by referring to defined name range

[Machds42]

I am attempting to write a macro, that will find the pre-defined name of a range (eg; I have named a1:c5 "Table_1"), then paste all cells into a given range on a different sheet

Cheers Tim

 

[Machds42]

I should qualify by saying that the defined name range is in a cell

i.e. cell a10 will have the name "Table_1" in it, so i was planning on using the contents of the cell as the reference point to the desired table's name

 

[tusharm]

Turn on the macro recorder (Tools | Macro > Record new macro...), select and copy a range of cells, and paste wherever you want to paste them. Turn off the recorder and Excel should give you the necessary code to do what you want. You can then replace the range Excel uses with the name that is in Cell A10 of whichever sheet.

Once done you should also visit
Beyond the macro recorder
http://www.tushar-mehta.com/excel/vba/beyond_the_macro_recorder/index.htm

 

[Machds42]

I have tried this and cant get it to work

The name range can continually change depending on the users preference

For example, the file contains 100 different tables - Table_1 - Table_100

The user has a drop down where they can choose whichever table they wish - a formula is written which refers to this name, and gives you the defined name of the range for that table.

I then need to use this defined name to find the table in the file somewhere, and paste the range.

 

[Machds42]

really desperate for an answer if someone can help.

Would save me days of work

 

[tusharm]

So, what's the code you got through the macro recorder?

I have tried this and cant get it to work

The name range can continually change depending on the users preference

For example, the file contains 100 different tables - Table_1 - Table_100

The user has a drop down where they can choose whichever table they wish - a formula is written which refers to this name, and gives you the defined name of the range for that table.

I then need to use this defined name to find the table in the file somewhere, and paste the range.