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!

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!

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!

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!

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!

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!