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

 

[SQUIDD]

what part does it error at.

 

[Norie]

Where do you get the error?

 

[BrianG86]

Sorry, the error happens at the below part of the code:

ws.Cells(lRow, 1).Value = tws.Cells(1, 6).Value

 

[Norie]

That code works just fine for me when I tested in a dummy setup.

Do you have any chart sheets in the workbooks you are working with?

 

[jimrward]

tws is declared as type variant if that helps always helps to declare everything helps in debugging and understandings code

 

[BrianG86]

Norrie, I do not. At the moment they are more or less blank, other than the data thats in them.

jimrward, not sure what that means. Its decalred as a worksheet as is the other worksheet?

 

[Norie]

This declares tws as Variant and tws2 as Worksheet, however I doubt that would have much to do with the error you are getting.

Dim tws, tws2 As Worksheet

 

[jimrward]

In vba if you declare multiple variables on one line you have to follow each variable with its type otherwise it will default to type variant, therefore tws is type variant and tws2 is type worksheet

 

[BrianG86]

ok, so I have changed the way I am declaring them to below:

Dim wb As Workbook, twb As Workbook
Dim ws As Worksheet
Dim tws As Worksheet
Dim tws2 As Worksheet
Dim lRow As Integer

Now I am getting a type mismatch error on the below line:

Set tws = twb.Sheets(1)