Hi Rod,

You don't need VBA to do this. You can use a dynamic named range. You can use INDEX and MATCH to find the last value in a column and define it as a dynamic named range.

If you get stuck come back with a sample Excel file so we can give you a specific solution.

Mynda

Thanks Mynda, I'll give it a try.

My situation is that I'm combining data from some 20 external Excel worksbooks into a single sheet using Paste Link, then sorting the white space, then I have a load of pivot charts I want to create based on a dynamic named range. My external sheets are all fixed size (so maybe 100 rows and 20 columns) but not all full of data, so I run a short sort macro to move everything to the top in the new sheet (does that make sense?).  I'm keeping the "shell" of my destination sheet read-only, so that I can repear the process as the data in my source sheets change.

I've discovered that I can't paste link into a table, and paste link doesn't copy formatting.  So I need a way of dymanically creating a new name range (or table) based on my newly sorted data, so that the pivot charts that are in the destination sheet can refresh and update a dashboard.

I didn't realise that you can have a formula in range manager - and that helps a lot!!!

Really appreciate you getting back to me

Rod

Hi Rod,

Sounds like you could be using Power Query to automate this whole process. 

Mynda

Thanks Mynda

Been looking at Power Query for a few hours - what a revelation!!! Thanks 🙂

Glad you like the look of Power Query, Rod. It's a game changer. If you'd like to learn more, please consider my Power Query course.