vba setting wksheet variable

Question

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.

Answer 1

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")

Answer 2

You need the Worksheet type not Worksheets.

Dim ijkWs, stamWs As Worksheet

But here unintuitively only stamWs if of type Worksheet, instead always use the form:

Dim ijkWs As Worksheet, stamWs As Worksheet.

This applies to all your Dims.

You need to store the opened workbook then get the sheet, stam is just a string so has no properties so stam.Sheets is not correct.

Dim wb As Workbook
Set wb = Workbooks.Open(stam)
Set stamWs = wb.Sheets("stambestand")

I also think you change to:

stam = PathOnly & StBestand
comp = PathOnly & Competenties

else you will get a double \ an an invalid path if the current book is not saved anywhere.

Answer 3

Instead of playing around with names and using PathOnly variable, just use ThisWorkbook.Path method. Much faster and easier, also eliminates the possibility of an error. Probably you already have a backslash in the end of the PathOnly variable, so your stam variable looks like this - "workbook_path\\Stambestand.xlsm" and that may be the cause of your problems.

Try

stam = ThisWorkbook.Path & "\" & StBestand