CODEWITHSUNDEEP
excelvba
T.Sar
T.Sar

Reputation: 3

Excel VBA: Using variable in place of item in quotation marks

I am trying to copy the contents of a cell in a different workbook, but would like the user to be able to specify which cell to start in by typing the name of the cell into a cell in the current workbook (that the macro code is in).

(By the way, please excuse the elementary nature of this question as well as any obvious mistakes I am making vocabulary-wise; I am new at trying my hand at this!)

I have come up with the following code, but am getting a "Run-time error '91': Object variable or With block variable not set" message.

(Please note that I have also used user input to refer to the different workbook. That part worked.)

Sub OpenWorkBook()

    Dim Src As Workbook
    Set Src = Workbooks.Open(Range("B3"))

    Dim StrtCell As String
    StrtCell = Range("B4")

    Src.Sheets("Sheet1").Range(StrtCell).Copy
    ThisWorkbook.Activate
    Range("A6").PasteSpecial

End Sub

Any assistance is greatly appreciated!

Upvotes: 0

Views: 279

Answers (1)

Tim Williams
Tim Williams

Reputation: 166331

It's best to always qualify your Range() calls with an explicit worksheet object, otherwise they will use whatever happens to be the Activesheet at the time.

Relying on some specific sheet being active when a particular line runs makes your code brittle and difficult to debug.

Sub OpenWorkBook()

    Dim Src As Workbook
    Dim StrtCell As String
    Dim sht as Worksheet

    Set sht = Activesheet

    Set Src = Workbooks.Open(sht.Range("B3"))
    StrtCell = sht.Range("B4")

    Src.Sheets("Sheet1").Range(StrtCell).Copy
    ThisWorkbook.Activate
    sht.Range("A6").PasteSpecial

End Sub

Upvotes: 1

Related Questions