VBA Error - 'Object doesn't support this property or method' - HELP???

[BrianG86]

Hi,

I have a workbook that I have users fill out and submit to a master file that holds all of the data.

I am getting the above error, but cannot figure out what I am doing wrong. Can anyone help?

Cheers

My code:

Sub CallLogger()
 
Dim wb As Workbook, twb As Workbook
Dim ws As Worksheet
Dim tws, tws2 As Worksheet
Dim lRow As Long
 
           ' On Error GoTo errhandle
           
 
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Set twb = ThisWorkbook
Set tws = twb.Sheets(1)
Set tws2 = twb.Sheets(2)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
 
                    Application.ScreenUpdating = False
 
Set wb = Workbooks.Open("S:\JL\Common\Model Office Pilot 2016\Model Office Data.xlsx", UpdateLinks:=3, WriteResPassword:="Insight1", IgnoreReadonlyRecommended:=True)
If wb.ReadOnly Then
wb.Close False
MsgBox "The spreadsheet is busy, please try again."
Exit Sub
Else
Set ws = wb.Sheets(1)
 
End If
 
 
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
 
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
lRow = ws.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
 
 
 
    ws.Cells(lRow, 1).Value = tws.Cells(1, 6).Value
    ws.Cells(lRow, 2).Value = tws.Cells(7, 6).Value
    ws.Cells(lRow, 3).Value = tws.Cells(9, 6).Value
    ws.Cells(lRow, 4).Value = tws.Cells(11, 6).Value
    ws.Cells(lRow, 5).Value = tws.Cells(13, 6).Value
    ws.Cells(lRow, 6).Value = tws.Cells(15, 6).Value
    ws.Cells(lRow, 7).Value = tws.Cells(17, 6).Value
    ws.Cells(lRow, 8).Value = Application.UserName
 
Application.Wait (Now + TimeValue("0:00:01"))
 
        Application.CutCopyMode = False
        wb.Close True
 
 
 
 
 
                    Application.ScreenUpdating = True
       
MsgBox "Your entry has been submitted"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
tws.Cells(7, 6).ClearContents
tws.Cells(9, 6).ClearContents
tws.Cells(11, 6).ClearContents
tws.Cells(13, 6).ClearContents
tws.Cells(15, 6).ClearContents
tws.Cells(17, 6).ClearContents
 
Exit Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
errhandle:
If Not wb Is Nothing Then
wb.Close False
Else
End If
MsgBox "There has been an error, please try again"
 
End Sub

 

[BrianG86]

Hi,

I have fixed it, but I don't know why it wasn't working in the first place.

I noticed that when setting the other sheets as variables it worked fine, so I changed the below line:

Set tws = twb.Sheets(1)

to

Set tws = twb.Sheets("Sheet1")

But don't really understand why it works now, when it didn't a minute ago?

 

[Norie]

That suggests to me that the first sheet in ThisWorkbook is not a worksheet.

I thought it might be a chart sheet as that would be the most likely but there are other types of sheet, eg dialog sheets.

What do you get if you type this in the immediate window?

? TypeName(ThisWorkbook.Sheets(1))

 

[BrianG86]

Ahhhh....

So, yes. It was a chart. Turns out there was a hidden sheet that I wasn't aware of.

Thanks