Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
RT Error 438
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
RT Error 438
Yes..the sheet object was wbdata.sheets(2)
"galimi" wrote: David, Has the name of the workbook or sheet object changed? -- http://HelpExcel.com "David" wrote: 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
RT Error 438
Does Sheet51 exist as an object in your IDE?
-- http://HelpExcel.com "David" wrote: Yes..the sheet object was wbdata.sheets(2) "galimi" wrote: David, Has the name of the workbook or sheet object changed? -- http://HelpExcel.com "David" wrote: 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
RT Error 438
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. "galimi" wrote: Does Sheet51 exist as an object in your IDE? -- http://HelpExcel.com "David" wrote: Yes..the sheet object was wbdata.sheets(2) "galimi" wrote: David, Has the name of the workbook or sheet object changed? -- http://HelpExcel.com "David" wrote: 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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
RT Error 438
David,
Feel free to email me your spreadsheet, I will inspect. -- http://HelpExcel.com "David" wrote: 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. "galimi" wrote: Does Sheet51 exist as an object in your IDE? -- http://HelpExcel.com "David" wrote: Yes..the sheet object was wbdata.sheets(2) "galimi" wrote: David, Has the name of the workbook or sheet object changed? -- http://HelpExcel.com "David" wrote: 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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
RT Error 438
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! "galimi" wrote: David, Feel free to email me your spreadsheet, I will inspect. -- http://HelpExcel.com "David" wrote: 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. "galimi" wrote: Does Sheet51 exist as an object in your IDE? -- http://HelpExcel.com "David" wrote: Yes..the sheet object was wbdata.sheets(2) "galimi" wrote: David, Has the name of the workbook or sheet object changed? -- http://HelpExcel.com "David" wrote: 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! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
RT Error 438
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. -- Regards, Tom Ogilvy "David" wrote in message ... 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! "galimi" wrote: David, Feel free to email me your spreadsheet, I will inspect. -- http://HelpExcel.com "David" wrote: 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. "galimi" wrote: Does Sheet51 exist as an object in your IDE? -- http://HelpExcel.com "David" wrote: Yes..the sheet object was wbdata.sheets(2) "galimi" wrote: David, Has the name of the workbook or sheet object changed? -- http://HelpExcel.com "David" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting instances of found text (Excel error? Or user error?) | Excel Worksheet Functions | |||
Error Handling - On Error GoTo doesn't trap error successfully | Excel Programming | |||
Form Err.Raise error not trapped by entry procedure error handler | Excel Programming | |||
Automation Error, Unknown Error. Error value - 440 | Excel Programming |