VBA "Object doesn't support this method" HELP!!

[gb71631]

Here is my code and I have whacked my brain out on this one....

Dim Savepath As Variant, tgr As Workbook, d8form As Workbook, sh As Worksheet

Application.ScreenUpdating = False
Set d8form = ThisWorkbook

If [a39] <> "" And [setup!c13] = False Then
If [d0!f8] = "External" Then
Savepath = [setup!b7] & [d0!t4] & ".xls"
Else
Savepath = [setup!b7] & [d0!t4] & ".xls"
End If
'[setup!c13] = True

Workbooks.Open [setup!b11]
Set tgr = ActiveWorkbook
Set sh = Sheets("TGR - TGW Form")
'Paste 8D Number
tgr.sh.Range("d8") = d8form.Sheets("d0").Range("t4") ' THIS IS WHERE I GET THE ERROR!!!
'ADD HYPERLINK
tgr.sh.Hyperlinks.Add Anchor:=[d8], Address:=Savepath.Font.Size = 11
'Customer
tgr.sh.[d4] = d8form.Sheets("d0").[y11]
'Location
tgr.sh.[i5] = d8form.Sheets("d0").[f13]
'Author
tgr.sh.[i7] = d8form.Sheets("d0").[r19]
'Issue / Description
tgr.sh.[b12] = d8form.Sheets("d2").[a7]
'Part Number
tgr.sh.[d9] = d8form.Sheets("d0").[y13]

End If

[Kenneth Hobs]

Welcome to the forum!

When posting code, please paste between code tabs. Click the # icon on the toolbar to insert code tags.

There is no need to set tgr nor d8form if they are both the ActiveWorkbook.

Before that line, add these two to see which has the problem. Debug.Print puts the result into VBE's Immediate window.


Debug.Print tgr.sh.Range("d8").Address(External:=True)
Debug.Print d8form.Sheets("d0").Range("t4").Address(External:=True)

You might try to Compile before running as I suspect that you have a syntax issue in an object reference.

[Paul_Hossler]

Not tested but see if this works



'Paste 8D Number
sh.Range("d8").Value = Sheets("d0").Range("t4").Value ' THIS IS WHERE I GET THE ERROR!!!

[Paul_Hossler]

There is no need to set tgr nor d8form if they are both the ActiveWorkbook.


Different style, but I find that when I'm juggling 2 or more open workbooks, I do better (i.e. fewer mistakes) if I explicitly set WB objects and explicitly refer to them instead of relying on ThisWorkbook and ActiveWorkbook

[p45cal]

I think Paul's suggestion will work but to avoid ambiguity, the earlier line:
Set sh = Sheets("TGR - TGW Form")
could be:
Set sh = tgr.Sheets("TGR - TGW Form")
if indeed that sheet is in the workbook tgr.
Not tested but see if this works



'Paste 8D Number
sh.Range("d8").Value = Sheets("d0").Range("t4").Value ' THIS IS WHERE I GET THE ERROR!!!