CODEWITHSUNDEEP
excelvba
OliEshmade
OliEshmade

Reputation: 121

How to store a Workbook name as a variable?

I have WorkbookA.xlsm and my code directly references "WorkbookA.xlsm"

When my workbook is distributed, not everybody leaves the name the same and it causes 'subscript out of range' errors.

How can I declare a workbook as a variable or similar? thisworkbook.name?

My product opens a separate workbook, pulls data from it, and then places it into my workbook - I was worried thisworkbook or similar might cause issues if more than one are open.

Upvotes: 0

Views: 12169

Answers (1)

Jean-Pierre Oosthuizen
Jean-Pierre Oosthuizen

Reputation: 2683

This should work

 Option Explicit

 Sub WorkingWithDataFromOtherWorkBook()

      'You can also Dim as Sheet and then set the sheet     
      Dim MasterWorkBook As Workbook
      Dim DataSourceWorkBook As Workbook

      Set MasterWorkBook = ThisWorkbook
      'Set DataSourceWorkBook = Workbooks.Open(Filename:=" Full File Path Here")
      Set DataSourceWorkBook = Workbooks.Open(Filename:="C:\Users\oosthjp\Desktop\Book1.xlsm")

      'Simple copy paste code below
      DataSourceWorkBook.Sheets("SheetReference").Range("SOMERANGE").Copy
      MasterWorkBook.Sheets("SheetReference").Range("SOMERANGE").Paste

      'Whatever other code you have goes here

 End Sub

Upvotes: 1

Related Questions