Excel VBA referencing external worksheet using named range


This Content is from Stack Overflow. Question asked by argnz97

I’m fairly new to VBA coding and am trying to use Dim rather than select and activate as I’ve been told the latter slows down your code.

I require my code to open an external workbook (Workbook2) and copy some data and paste it into the existing workbook (Workbook1).

Currently, I’m trying to define the name of a worksheet in Workbook2 by using a named range in Workbook1. That way if the worksheet name changes in Workbook2 one can dynamically do it from a cell in Workbook1. However, I get a “subscript-out-of-range-error” on the final line.

Any assistance would be much appreciated.


Sub PropertyMark()

‘Define Name Ranges as helpers.
Dim NamePropWS1 As String
NamePropWS1 = Range(“NamePropWS1”).Value

‘DefineWorkbooks and Worksheets.
Dim ConsWB As Workbook
Dim ConsWS As Worksheet

Dim PropWB As Workbook
Dim PropWS1 As Worksheet

Set ConsWB = ThisWorkbook

‘Open up Property Portfolio and begin running Code
Workbooks.Open Filename:=Range(“PropertyFileAddress”).Value

Set PropWB = ThisWorkbook
Set PropWS1 = PropWB.Worksheets(NamePropWS1)

End Sub



This question is not yet answered, be the first one who answer using the comment. Later the confirmed answer will be published as the solution.

This Question and Answer are collected from stackoverflow and tested by JTuto community, is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.

people found this article helpful. What about you?