RT Error 438

[Guest]

I've had some vba code that's been working fine, but something is making it
bomb now. I changed the reference to sheets by referring to the "code sheet
name", but I thought that was working fine, but in my final testing, I'm
having problems.
Here's the code:
Sub CapturePlumberData()
Dim wbSum As Workbook, wbData As Workbook
Dim sht
Workbooks.Open FileName:=Sheet1.Range("B8").Value
Set wbSum = ActiveWorkbook
Set wbData = ThisWorkbook
Dim iDate As Date, iValue
Dim lastcol As Long, xV As Long, xR As Long, xC As Long

Msg = "Are you Ready to Post Your Data to the Consolidated Plumber File?"
Ans = MsgBox(Msg, vbYesNo)
If Ans = vbNo Then Exit Sub

' get source data from open sheet
With wbData.Sheet51

I get the error on the last line above. Any ideas?

Thanks!

 

[Guest]

David,

Has the name of the workbook or sheet object changed?

 

[Guest]

Yes..the sheet object was wbdata.sheets(2)

David,

Has the name of the workbook or sheet object changed?

 

[Guest]

Does Sheet51 exist as an object in your IDE?

 

[Guest]

I'm not sure what you mean by IDE.
When in VBA, I changed all the sheet names in the sheet properties. Sheet51
is the property name for Sheet2. Hope that helps.
I'm not sure the With command will refer to these names. I've put the code
back and it's working again.

 

[Guest]

David,

Feel free to email me your spreadsheet, I will inspect.

 

[Guest]

Thanks for the offer...but I have to get this emailed out to 140 locations
today. I'll just use the original code. Thx for the help!

 

[Tom Ogilvy]

thisworkbook.Sheet1.Name

doesn't work, so no reason to believe that your approach will work.

The good news is the Sheet51 will always refer to the sheets in
Thisworkbook.

Sub CapturePlumberData()
Dim wbSum As Workbook, wbData As Workbook
Dim sht
Workbooks.Open FileName:=Sheet1.Range("B8").Value
Set wbSum = ActiveWorkbook
Set wbData = ThisWorkbook
Dim iDate As Date, iValue
Dim lastcol As Long, xV As Long, xR As Long, xC As Long

Msg = "Are you Ready to Post Your Data to the Consolidated Plumber File?"
Ans = MsgBox(Msg, vbYesNo)
If Ans = vbNo Then Exit Sub

' get source data from open sheet
With Sheet51



Using With Sheet51 should work fine if that is actually the code name.