Variable to select range to copy and paste to

Hello -- Looking for VBA code to read a variable (say, in cell A1), which corresponds to one of 30-40 range names on the same sheet, then copy that range and paste-special-values into another range on another sheet, also corresponding to that same variable. Any ideas? Thanks....

Hi

See how this goes

Please Login or Register  to view this content.

I couldn't be sure if the ranges were the same size, so I've assumed that the range name on the data sheet was multiple cell, while the range name on the output sheet was a single cell.


rylo

Rylo -- Thanks for responding. I need to describe in more detail...

My program brings a few 1000 lines of data into columns a-e of a spreadsheet (say, sheet 1). The data is then operated on and summarized (vlookups and other functions) into a 5x80 range (say cols f-j) which constitutes an update of one of several (say 30) existing data sets which are also in 5x80 named ranges. The data brought in has a "tag" or identifier (say in cell a1) that has the name of the named range which is to be updated. The VBA has to read cell a1 and copy the 5x80 update in cols f-j and paste-special-values into the appropriate named range (assume they are all on sheet 2 of the workbook).

Hope this helps... and thanks.

Roy...thanks for responding.

Pivot table could do the analysis, but that part I know how to do (but I'm not using a pivot table). Data from any one of many (say 30) scenarios is brought into cols a-e of a sheet, operated on and summarized into a 5x80 named range (which is always in the same place, say cols f-j). That constitutes an update of one of my 30 named ranges (each a different scenario), which are already established and populated. Now I need a macro to select that newly-summarized data (which has a tag (say in cell a1, identifying which of the 30 named ranges for which it is an update) and copy / paste-special-values into the appropriate named range. That is, I need the VBA to direct the paste-special-values into the appropriate named range being updated.

Thanks...

Roy -- That worked fine... I just needed to tweak it a bit for my purposes.

Tnanks again...