Looping named ranges copy and paste

[dummy777]

I wanted to use this to copy named ranges in separate excel sheets. There are atleast 20+ named ranges in the workbook. I set the table with named range so that everytime it changes, I can copy the whole table. tia.

Please Login or Register  to view this content.

[PCI]

Can you attach a short Excel sample file showing what you want as final result.
Note: Of course the scope of the name for a activesheet

[jolivanes]

Each named range to its own sheet?

[dummy777]

I'm not sure if this is possible. I attached a sample table on the sheet1. And I want to loop the macro each time it finds a named range in sheet1, it will copy it in separate sheet in consecutively manner. The table may sometimes dynamic as it may contains only 3 data, 5 data, etc. it is not fixed. If this is not possible, I can also use the dynamic range using row.xlUp function.

Not a fixed own sheet, it may vary. Depending on the data.

[PCI]

each time it finds a named range in sheet1, it will copy it in separate sheet

It means you want to copy only the ranges covered by each name or do you want to copy the names ... take care that ( today) names' scope is workbook.
Do you want to make the copy from sheet1 to all others sheets ??

[jolivanes]

Try

Please Login or Register  to view this content.

[dummy777]

Nicely done! Thank you jolivanes. And the code you provided make the tabs with the named range too!

Yes. I want to copy the ranges of each named range in separate sheet individually.

[jolivanes]

Thank you for letting us know that your happy with the result.
Have a good day.