Defining dynamic ranges to work across multiple workbooks

[catharsis50]

I would like some help with how to define some dynamic named ranges that would be applicable across multiple workbooks. There are three workbooks that open and all have the same worksheets in them so all naming conventions are true throughout. The ranges are mainly used for copy/paste/dedupe functions, and sometimes copy pasting between the different sheets. All help is appreciated.

Thanks

[raji2678]

I am not sure whether this is what you are looking for..here is the code to update the range
strNameRange = "=Lookups!$A$2:" & ActiveSheet.Cells(rowcnt, 1).Address
ActiveWorkbook.Names("AssignedTo").RefersTo = strNameRange

[catharsis50]

Unfortunately I'm not sure what that does, so I can't say for sure but I don't think so. I'm looking to define a dynamic named range, something like this:
Set Rng1 = Sheets("Karl Dashboard").Range("B3", Range("S3").End(xlDown))

Since the three workbooks I have all have the same worksheet names and ranges within those worksheets I would like to be able to define one named range that I can plug in during my script that will select that range in each workbook. Thanks.

[raji2678]

You can also define a range at Design Time by right-clickign it and selecting a name.

I have successfully used that in my work to create and edit a named range

[catharsis50]

Would that same range work within another workbook though? I need to be able to refer to that same range throughout my code, regardless of what workbook is open. I want to be able to use Rng1 in my code and have it grab the range on Karl Dashboard sheet in all three workbooks. Is that possible?

[raji2678]

Not sure..you have to try. As an alternative, can you read data from other workbooks into the same location, and delete once the task is over?

[catharsis50]

I am not sure whether this is what you are looking for..here is the code to update the range
strNameRange = "=Lookups!$A$2:" & ActiveSheet.Cells(rowcnt, 1).Address
ActiveWorkbook.Names("AssignedTo").RefersTo = strNameRange

Why would you use that instead of defining a range?

[raji2678]

That is used if I want to change the existing range. You can also define the range at design time

[catharsis50]

ok. i'm looking to pre-define these ranges so the macro can just run through nothing needs to be renamed. Just defined in a manor that applies to whatever active workbook is open.

[raji2678]

You can very well do that using the code I sent earlier, when the worksheet is loaded