named range from sheet1 to range starting from cell in sheet10

[Decar]

Hi,


I feel kinda stupid for asking this but I cannot find anything that helps on Google and I always get an error when I try the named range.

This one works:

Please Login or Register  to view this content.

But I want to copy a named range from sheet1 to sheet10 Starting from the specified cell.

Please Login or Register  to view this content.

I thought that maybe the named range does not need a sheet in front because it is included

Please Login or Register  to view this content.

but thats not working either. (to add .value did not change it either)


I read that the method of activating and selecting sheet and range --> copy --> activate other sheet paste values would work but I to set range = should be more efficient and better for performance right?

[Leith Ross]

Hello Decar,

Named ranges can be one of two types:Local or workbook. If it is local then the named range must be prefaced with the worksheet name, like sheet10.Range("named_range"). Otherwise, you would use Range(named_range").

Ranges can be a single cell or multiple cells. Your example that works assigns a single cell value to another single cell. If you try to assign multiple cells to each other this way or multiple cells to a single cell, it will fail.

[Decar]

Thanks Leit,


so basically I have to now how big the range is for my named range. My problem is now that my named range is a dynamic range and will always be different. Is there a possibility to get the row size and column size of the named range and then transfer those to another sheet?

my perfect solution would be that the named range gets copied to another sheet

[Decar]

Yay, I got to a solution knowing how the ranges have to be :D

Please Login or Register  to view this content.