Search code examples
vbaexcelworksheet

vba setting wksheet variable

I'm trying to set a variable for a worksheet on a 2nd workbook (Trowing an object error on the last line, stamWs). I've already got a variable for both workbooks. My code looks as follows:

Option Explicit

Sub Kopie()

Application.ScreenUpdating = False

Const StBestand = "Stambestand.xlsm"
Const Competenties = "Competenties.xlsx"

Dim stam, comp As String
Dim PathOnly, ijk, FileOnly As String
Dim ijkWs, stamWs As Worksheets

ijk = ThisWorkbook.FullName
FileOnly = ThisWorkbook.Name
PathOnly = Left(ijk, Len(ijk) - Len(FileOnly))
stam = PathOnly & "\" & StBestand
comp = PathOnly & "\" & Competenties
Set ijkWs = ActiveSheet

    Workbooks.Open stam
Set stamWs = stam.Sheets("stambestand")

I've tried referencing active worksheet, no luck and a host of other references to worksheets/workbook combinations. Your input is greatly appreciated.

Update: Tanks for the input guys. I've changed sheets to sheet in the declaration part and opened the workbook before setting the variable sheet.

Solution

  • You should be setting up a workbook object when you are opening the workbook, and using that to create your worksheet object.

    Dim newWB as WorkBook
Set newWB = Workbooks.Open(stam)
Set stamWs = newWB.WorkSheets("stambestand")