RT Error 438

D

David

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!
 
D

David

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.
 
D

David

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!
 
T

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top