I get an Error 1004 when using the copy method to copy a worksheet from one
worbook to another workbook when the worksheet contains a combobox. The
method works fine if the worksheet does not contain a combobox. Anyone have
any ideas as to why this is happening?

Hi Steve,

Would you like to post the VBA code where the error is occurring please?

regards,

OssieMac

"Steve Rosenblum" wrote:

I get an Error 1004 when using the copy method to copy a worksheet from one
worbook to another workbook when the worksheet contains a combobox. The
method works fine if the worksheet does not contain a combobox. Anyone have
any ideas as to why this is happening?

The routine is as follows:

Sub AddWorksheet(Base As String)

On Error GoTo errorhandler
Dim Worksheet As Worksheet
Dim i As Integer

i = 0

For Each Worksheet In DataWorkbook.Worksheets
If Worksheet.Name Like Base + "*" Then
i = i + 1
End If
Next Worksheet
DataWorkbook.Sheets(Base + "_Std").Copy
Befo=DataWorkbook.Sheets("Results")
ActiveSheet.Name = "Equipment " + CStr(i)
ActiveSheet.Visible = True
Exit Sub

errorhandler:
Call MsgBox("Problem with AddWorksheet. You must close Excel and then
reopen the tool to continue", vbCritical + vbOKOnly, "EXCEL ADD WORKSHEET
ERROR")
If DebugFlag Then
MsgBox ("Error in " + "AddWorksheet")
End If
End Sub

Note that Dataworkbook is another Excel workbook different from the Excel
Workbook that contains the code above.

"OssieMac" wrote:

Hi Steve,

Would you like to post the VBA code where the error is occurring please?

regards,

OssieMac

"Steve Rosenblum" wrote:

I get an Error 1004 when using the copy method to copy a worksheet from one
worbook to another workbook when the worksheet contains a combobox. The
method works fine if the worksheet does not contain a combobox. Anyone have
any ideas as to why this is happening?

Hi again Steve,

Your original post said that you wanted to copy from one workbook to
another. The following line of code you posted is to the same workbook:-

DataWorkbook.Sheets(Base + "_Std").Copy _
Befo=DataWorkbook.Sheets("Results")

Perhaps you have been working on trying to find the problem.

However, I have edited you code and made a few changes. Firstly it is not a
good idea to use words like WorkSheet as variables. (The name describes the
type of variable.)

I assumed that "Equipment" is passed to Base when the sub is called. As you
will see, I set up a dummy procedure to call the sub and pass the name to the
procedure.

I have tested the code below and it copies combo boxes with the worksheets.
(Tested with both Forms Combo boxes and ActiveX Combo Boxes) . However, if
you have linked ranges in the combo boxes linked to another worksheet in the
original workbook, after copying, it links those ranges back to the original
workbook. This does not occur if the linked ranges are on the same worksheet
being copied.

It has been tested with xl2007 and and on a separate computer with xl2002
(XP). Edit the "Set wbData =" line of code as per comment if not using
xl2007.

I disabled error handling mainly for the purpose of testing so that I could
see what was failing. Feel free to put it back in because all programs should
have some sort of error handling for the end user so they are not dropped
into the code and have no idea why.

Sub Calling_procedure()
Call AddWorksheet("Equipment")
End Sub


Sub AddWorksheet(Base As String)

'On Error GoTo errorhandler
Dim wbThis As Workbook 'This workbook
Dim wbData As Workbook 'DataWorkbook
Dim ws As Worksheet 'Variable for Each loop
Dim i As Integer 'For/Next loop

Set wbThis = ThisWorkbook

'xlsx extension is xl2007.
'Use xls for earlier versions
Set wbData = Workbooks.Item("DataWorkbook.xlsx")

'Following line needs to = 1 so that value finishes
'as 1 greater than what already exists for new sheet
i = 1

wbData.Activate
For Each ws In Worksheets
If ws.Name Like Base + "*" Then
i = i + 1
End If
Next ws


'Copy sheet from ThisWorkbook to wbData
wbThis.Sheets(Base + "_Std").Copy _
Befo=wbData.Sheets("Results")

ActiveSheet.Name = "Equipment " + CStr(i)
ActiveSheet.Visible = True

'Following line will return to the workbook
'containing this macro if required
'wbThis.Activate

Exit Sub

'errorhandler:
' Call MsgBox("Problem with AddWorksheet. " + _
' "You must close Excel and then reopen " + _
' "the tool to continue", vbCritical + _
' vbOKOnly, "EXCEL ADD WORKSHEET Error ")
' If DebugFlag Then
' MsgBox ("Error in " + "AddWorksheet")
' End If
End Sub


Feel free to get back to me if still having problems.

Regards,

OssieMac

Hi yet again Steve,

I have been wondering if I have misinterpreted your question and that you
meant that you want to copy the worksheet within the same workbook but from a
macro in another workbook. If this is correct then the macro I gave you is
basically the same except that you can inititalize i to zero because it then
counts the Equipment_Std that it is copying as one worksheet and therefore
the number would be correct for adding another sheet name Equipment + i.

The other line of code to change is:-

'Copy sheet in same workbook (wbData)
wbData.Sheets(Base + "_Std").Copy _
Befo=wbData.Sheets("Results")

Regards,

OssieMac



"OssieMac" wrote:

Hi again Steve,

Your original post said that you wanted to copy from one workbook to
another. The following line of code you posted is to the same workbook:-

DataWorkbook.Sheets(Base + "_Std").Copy _
Befo=DataWorkbook.Sheets("Results")

Perhaps you have been working on trying to find the problem.

However, I have edited you code and made a few changes. Firstly it is not a
good idea to use words like WorkSheet as variables. (The name describes the
type of variable.)

I assumed that "Equipment" is passed to Base when the sub is called. As you
will see, I set up a dummy procedure to call the sub and pass the name to the
procedure.

I have tested the code below and it copies combo boxes with the worksheets.
(Tested with both Forms Combo boxes and ActiveX Combo Boxes) . However, if
you have linked ranges in the combo boxes linked to another worksheet in the
original workbook, after copying, it links those ranges back to the original
workbook. This does not occur if the linked ranges are on the same worksheet
being copied.

It has been tested with xl2007 and and on a separate computer with xl2002
(XP). Edit the "Set wbData =" line of code as per comment if not using
xl2007.

I disabled error handling mainly for the purpose of testing so that I could
see what was failing. Feel free to put it back in because all programs should
have some sort of error handling for the end user so they are not dropped
into the code and have no idea why.

Sub Calling_procedure()
Call AddWorksheet("Equipment")
End Sub


Sub AddWorksheet(Base As String)

'On Error GoTo errorhandler
Dim wbThis As Workbook 'This workbook
Dim wbData As Workbook 'DataWorkbook
Dim ws As Worksheet 'Variable for Each loop
Dim i As Integer 'For/Next loop

Set wbThis = ThisWorkbook

'xlsx extension is xl2007.
'Use xls for earlier versions
Set wbData = Workbooks.Item("DataWorkbook.xlsx")

'Following line needs to = 1 so that value finishes
'as 1 greater than what already exists for new sheet
i = 1

wbData.Activate
For Each ws In Worksheets
If ws.Name Like Base + "*" Then
i = i + 1
End If
Next ws


'Copy sheet from ThisWorkbook to wbData
wbThis.Sheets(Base + "_Std").Copy _
Befo=wbData.Sheets("Results")

ActiveSheet.Name = "Equipment " + CStr(i)
ActiveSheet.Visible = True

'Following line will return to the workbook
'containing this macro if required
'wbThis.Activate

Exit Sub

'errorhandler:
' Call MsgBox("Problem with AddWorksheet. " + _
' "You must close Excel and then reopen " + _
' "the tool to continue", vbCritical + _
' vbOKOnly, "EXCEL ADD WORKSHEET Error ")
' If DebugFlag Then
' MsgBox ("Error in " + "AddWorksheet")
' End If
End Sub


Feel free to get back to me if still having problems.

Regards,

OssieMac

OssieMac,

I appreciate your suggestions however the problem still exists. Let me be
more explicit.

First - your last post got it right. the worksheets are in the same
workbook but the macro that I am running is in a different workbook.

Second - your code and my original code run fine on many machines. There
are only a few machines that I have seen where it fails.

It seems to fail on only XP machines. To be more specific, one of the
machines that it fails on is Windows XP v. 5.1 SP2 running MS Excel 2003 -
SP2.


I was hoping that I could find a more general solution that would not be
operating system / software version dependent. That was why I didn't include
this information in my prior posts.

If you have any insight/thoughts I would appreciate hearing them.

Steve

"OssieMac" wrote:

Hi yet again Steve,

I have been wondering if I have misinterpreted your question and that you
meant that you want to copy the worksheet within the same workbook but from a
macro in another workbook. If this is correct then the macro I gave you is
basically the same except that you can inititalize i to zero because it then
counts the Equipment_Std that it is copying as one worksheet and therefore
the number would be correct for adding another sheet name Equipment + i.

The other line of code to change is:-

'Copy sheet in same workbook (wbData)
wbData.Sheets(Base + "_Std").Copy _
Befo=wbData.Sheets("Results")

Regards,

OssieMac



"OssieMac" wrote:

Hi again Steve,

Your original post said that you wanted to copy from one workbook to
another. The following line of code you posted is to the same workbook:-

DataWorkbook.Sheets(Base + "_Std").Copy _
Befo=DataWorkbook.Sheets("Results")

Perhaps you have been working on trying to find the problem.

However, I have edited you code and made a few changes. Firstly it is not a
good idea to use words like WorkSheet as variables. (The name describes the
type of variable.)

I assumed that "Equipment" is passed to Base when the sub is called. As you
will see, I set up a dummy procedure to call the sub and pass the name to the
procedure.

I have tested the code below and it copies combo boxes with the worksheets.
(Tested with both Forms Combo boxes and ActiveX Combo Boxes) . However, if
you have linked ranges in the combo boxes linked to another worksheet in the
original workbook, after copying, it links those ranges back to the original
workbook. This does not occur if the linked ranges are on the same worksheet
being copied.

It has been tested with xl2007 and and on a separate computer with xl2002
(XP). Edit the "Set wbData =" line of code as per comment if not using
xl2007.

I disabled error handling mainly for the purpose of testing so that I could
see what was failing. Feel free to put it back in because all programs should
have some sort of error handling for the end user so they are not dropped
into the code and have no idea why.

Sub Calling_procedure()
Call AddWorksheet("Equipment")
End Sub


Sub AddWorksheet(Base As String)

'On Error GoTo errorhandler
Dim wbThis As Workbook 'This workbook
Dim wbData As Workbook 'DataWorkbook
Dim ws As Worksheet 'Variable for Each loop
Dim i As Integer 'For/Next loop

Set wbThis = ThisWorkbook

'xlsx extension is xl2007.
'Use xls for earlier versions
Set wbData = Workbooks.Item("DataWorkbook.xlsx")

'Following line needs to = 1 so that value finishes
'as 1 greater than what already exists for new sheet
i = 1

wbData.Activate
For Each ws In Worksheets
If ws.Name Like Base + "*" Then
i = i + 1
End If
Next ws


'Copy sheet from ThisWorkbook to wbData
wbThis.Sheets(Base + "_Std").Copy _
Befo=wbData.Sheets("Results")

ActiveSheet.Name = "Equipment " + CStr(i)
ActiveSheet.Visible = True

'Following line will return to the workbook
'containing this macro if required
'wbThis.Activate

Exit Sub

'errorhandler:
' Call MsgBox("Problem with AddWorksheet. " + _
' "You must close Excel and then reopen " + _
' "the tool to continue", vbCritical + _
' vbOKOnly, "EXCEL ADD WORKSHEET Error ")
' If DebugFlag Then
' MsgBox ("Error in " + "AddWorksheet")
' End If
End Sub


Feel free to get back to me if still having problems.

Regards,

OssieMac

Hi Steve,

I have been out of the picture for a couple of days and hence the late
reply. The code runs successfully on Windows Vista with xl2007 and Windows XP
with xl 2002 so I am sorry but cannot offer any more help.

Regards,

OssieMac

"Steve Rosenblum" wrote:

OssieMac,

I appreciate your suggestions however the problem still exists. Let me be
more explicit.

First - your last post got it right. the worksheets are in the same
workbook but the macro that I am running is in a different workbook.

Second - your code and my original code run fine on many machines. There
are only a few machines that I have seen where it fails.

It seems to fail on only XP machines. To be more specific, one of the
machines that it fails on is Windows XP v. 5.1 SP2 running MS Excel 2003 -
SP2.


I was hoping that I could find a more general solution that would not be
operating system / software version dependent. That was why I didn't include
this information in my prior posts.

If you have any insight/thoughts I would appreciate hearing them.

Steve

"OssieMac" wrote:

Hi yet again Steve,

I have been wondering if I have misinterpreted your question and that you
meant that you want to copy the worksheet within the same workbook but from a
macro in another workbook. If this is correct then the macro I gave you is
basically the same except that you can inititalize i to zero because it then
counts the Equipment_Std that it is copying as one worksheet and therefore
the number would be correct for adding another sheet name Equipment + i.

The other line of code to change is:-

'Copy sheet in same workbook (wbData)
wbData.Sheets(Base + "_Std").Copy _
Befo=wbData.Sheets("Results")

Regards,

OssieMac



"OssieMac" wrote:

Hi again Steve,

Your original post said that you wanted to copy from one workbook to
another. The following line of code you posted is to the same workbook:-

DataWorkbook.Sheets(Base + "_Std").Copy _
Befo=DataWorkbook.Sheets("Results")

Perhaps you have been working on trying to find the problem.

However, I have edited you code and made a few changes. Firstly it is not a
good idea to use words like WorkSheet as variables. (The name describes the
type of variable.)

I assumed that "Equipment" is passed to Base when the sub is called. As you
will see, I set up a dummy procedure to call the sub and pass the name to the
procedure.

I have tested the code below and it copies combo boxes with the worksheets.
(Tested with both Forms Combo boxes and ActiveX Combo Boxes) . However, if
you have linked ranges in the combo boxes linked to another worksheet in the
original workbook, after copying, it links those ranges back to the original
workbook. This does not occur if the linked ranges are on the same worksheet
being copied.

It has been tested with xl2007 and and on a separate computer with xl2002
(XP). Edit the "Set wbData =" line of code as per comment if not using
xl2007.

I disabled error handling mainly for the purpose of testing so that I could
see what was failing. Feel free to put it back in because all programs should
have some sort of error handling for the end user so they are not dropped
into the code and have no idea why.

Sub Calling_procedure()
Call AddWorksheet("Equipment")
End Sub


Sub AddWorksheet(Base As String)

'On Error GoTo errorhandler
Dim wbThis As Workbook 'This workbook
Dim wbData As Workbook 'DataWorkbook
Dim ws As Worksheet 'Variable for Each loop
Dim i As Integer 'For/Next loop

Set wbThis = ThisWorkbook

'xlsx extension is xl2007.
'Use xls for earlier versions
Set wbData = Workbooks.Item("DataWorkbook.xlsx")

'Following line needs to = 1 so that value finishes
'as 1 greater than what already exists for new sheet
i = 1

wbData.Activate
For Each ws In Worksheets
If ws.Name Like Base + "*" Then
i = i + 1
End If
Next ws


'Copy sheet from ThisWorkbook to wbData
wbThis.Sheets(Base + "_Std").Copy _
Befo=wbData.Sheets("Results")

ActiveSheet.Name = "Equipment " + CStr(i)
ActiveSheet.Visible = True

'Following line will return to the workbook
'containing this macro if required
'wbThis.Activate

Exit Sub

'errorhandler:
' Call MsgBox("Problem with AddWorksheet. " + _
' "You must close Excel and then reopen " + _
' "the tool to continue", vbCritical + _
' vbOKOnly, "EXCEL ADD WORKSHEET Error ")
' If DebugFlag Then
' MsgBox ("Error in " + "AddWorksheet")
' End If
End Sub


Feel free to get back to me if still having problems.

Regards,

OssieMac

Hi Steve,

I had a problem posting this reply so it is possible you might get it twice.

I have been out of the picture for a couple of days and hence the late
reply. The code runs successfully on Windows Vista with xl2007 and Windows XP
SP2 and xl 2002 so I am sorry but I don't think that I can offer any more
help.

Regards,

OssieMac

"Steve Rosenblum" wrote:

OssieMac,

I appreciate your suggestions however the problem still exists. Let me be
more explicit.

First - your last post got it right. the worksheets are in the same
workbook but the macro that I am running is in a different workbook.

Second - your code and my original code run fine on many machines. There
are only a few machines that I have seen where it fails.

It seems to fail on only XP machines. To be more specific, one of the
machines that it fails on is Windows XP v. 5.1 SP2 running MS Excel 2003 -
SP2.


I was hoping that I could find a more general solution that would not be
operating system / software version dependent. That was why I didn't include
this information in my prior posts.

If you have any insight/thoughts I would appreciate hearing them.

Steve

"OssieMac" wrote:

Hi yet again Steve,

I have been wondering if I have misinterpreted your question and that you
meant that you want to copy the worksheet within the same workbook but from a
macro in another workbook. If this is correct then the macro I gave you is
basically the same except that you can inititalize i to zero because it then
counts the Equipment_Std that it is copying as one worksheet and therefore
the number would be correct for adding another sheet name Equipment + i.

The other line of code to change is:-

'Copy sheet in same workbook (wbData)
wbData.Sheets(Base + "_Std").Copy _
Befo=wbData.Sheets("Results")

Regards,

OssieMac



"OssieMac" wrote:

Hi again Steve,

Your original post said that you wanted to copy from one workbook to
another. The following line of code you posted is to the same workbook:-

DataWorkbook.Sheets(Base + "_Std").Copy _
Befo=DataWorkbook.Sheets("Results")

Perhaps you have been working on trying to find the problem.

However, I have edited you code and made a few changes. Firstly it is not a
good idea to use words like WorkSheet as variables. (The name describes the
type of variable.)

I assumed that "Equipment" is passed to Base when the sub is called. As you
will see, I set up a dummy procedure to call the sub and pass the name to the
procedure.

I have tested the code below and it copies combo boxes with the worksheets.
(Tested with both Forms Combo boxes and ActiveX Combo Boxes) . However, if
you have linked ranges in the combo boxes linked to another worksheet in the
original workbook, after copying, it links those ranges back to the original
workbook. This does not occur if the linked ranges are on the same worksheet
being copied.

It has been tested with xl2007 and and on a separate computer with xl2002
(XP). Edit the "Set wbData =" line of code as per comment if not using
xl2007.

I disabled error handling mainly for the purpose of testing so that I could
see what was failing. Feel free to put it back in because all programs should
have some sort of error handling for the end user so they are not dropped
into the code and have no idea why.

Sub Calling_procedure()
Call AddWorksheet("Equipment")
End Sub


Sub AddWorksheet(Base As String)

'On Error GoTo errorhandler
Dim wbThis As Workbook 'This workbook
Dim wbData As Workbook 'DataWorkbook
Dim ws As Worksheet 'Variable for Each loop
Dim i As Integer 'For/Next loop

Set wbThis = ThisWorkbook

'xlsx extension is xl2007.
'Use xls for earlier versions
Set wbData = Workbooks.Item("DataWorkbook.xlsx")

'Following line needs to = 1 so that value finishes
'as 1 greater than what already exists for new sheet
i = 1

wbData.Activate
For Each ws In Worksheets
If ws.Name Like Base + "*" Then
i = i + 1
End If
Next ws


'Copy sheet from ThisWorkbook to wbData
wbThis.Sheets(Base + "_Std").Copy _
Befo=wbData.Sheets("Results")

ActiveSheet.Name = "Equipment " + CStr(i)
ActiveSheet.Visible = True

'Following line will return to the workbook
'containing this macro if required
'wbThis.Activate

Exit Sub

'errorhandler:
' Call MsgBox("Problem with AddWorksheet. " + _
' "You must close Excel and then reopen " + _
' "the tool to continue", vbCritical + _
' vbOKOnly, "EXCEL ADD WORKSHEET Error ")
' If DebugFlag Then
' MsgBox ("Error in " + "AddWorksheet")
' End If
End Sub


Feel free to get back to me if still having problems.

Regards,

OssieMac

you might want to take a look he
http://support.microsoft.com/kb/210684


OssieMac wrote:
Hi Steve,

I had a problem posting this reply so it is possible you might get it twice.

I have been out of the picture for a couple of days and hence the late
reply. The code runs successfully on Windows Vista with xl2007 and Windows XP
SP2 and xl 2002 so I am sorry but I don't think that I can offer any more
help.

Regards,

OssieMac

"Steve Rosenblum" wrote:

OssieMac,

I appreciate your suggestions however the problem still exists. Let me be
more explicit.

First - your last post got it right. the worksheets are in the same
workbook but the macro that I am running is in a different workbook.

Second - your code and my original code run fine on many machines. There
are only a few machines that I have seen where it fails.

It seems to fail on only XP machines. To be more specific, one of the
machines that it fails on is Windows XP v. 5.1 SP2 running MS Excel 2003 -
SP2.


I was hoping that I could find a more general solution that would not be
operating system / software version dependent. That was why I didn't include
this information in my prior posts.

If you have any insight/thoughts I would appreciate hearing them.

Steve

"OssieMac" wrote:

Hi yet again Steve,

I have been wondering if I have misinterpreted your question and that you
meant that you want to copy the worksheet within the same workbook but from a
macro in another workbook. If this is correct then the macro I gave you is
basically the same except that you can inititalize i to zero because it then
counts the Equipment_Std that it is copying as one worksheet and therefore
the number would be correct for adding another sheet name Equipment + i.

The other line of code to change is:-

'Copy sheet in same workbook (wbData)
wbData.Sheets(Base + "_Std").Copy _
Befo=wbData.Sheets("Results")

Regards,

OssieMac



"OssieMac" wrote:

Hi again Steve,

Your original post said that you wanted to copy from one workbook to
another. The following line of code you posted is to the same workbook:-

DataWorkbook.Sheets(Base + "_Std").Copy _
Befo=DataWorkbook.Sheets("Results")

Perhaps you have been working on trying to find the problem.

However, I have edited you code and made a few changes. Firstly it is not a
good idea to use words like WorkSheet as variables. (The name describes the
type of variable.)

I assumed that "Equipment" is passed to Base when the sub is called. As you
will see, I set up a dummy procedure to call the sub and pass the name to the
procedure.

I have tested the code below and it copies combo boxes with the worksheets.
(Tested with both Forms Combo boxes and ActiveX Combo Boxes) . However, if
you have linked ranges in the combo boxes linked to another worksheet in the
original workbook, after copying, it links those ranges back to the original
workbook. This does not occur if the linked ranges are on the same worksheet
being copied.

It has been tested with xl2007 and and on a separate computer with xl2002
(XP). Edit the "Set wbData =" line of code as per comment if not using
xl2007.

I disabled error handling mainly for the purpose of testing so that I could
see what was failing. Feel free to put it back in because all programs should
have some sort of error handling for the end user so they are not dropped
into the code and have no idea why.

Sub Calling_procedure()
Call AddWorksheet("Equipment")
End Sub


Sub AddWorksheet(Base As String)

'On Error GoTo errorhandler
Dim wbThis As Workbook 'This workbook
Dim wbData As Workbook 'DataWorkbook
Dim ws As Worksheet 'Variable for Each loop
Dim i As Integer 'For/Next loop

Set wbThis = ThisWorkbook

'xlsx extension is xl2007.
'Use xls for earlier versions
Set wbData = Workbooks.Item("DataWorkbook.xlsx")

'Following line needs to = 1 so that value finishes
'as 1 greater than what already exists for new sheet
i = 1

wbData.Activate
For Each ws In Worksheets
If ws.Name Like Base + "*" Then
i = i + 1
End If
Next ws


'Copy sheet from ThisWorkbook to wbData
wbThis.Sheets(Base + "_Std").Copy _
Befo=wbData.Sheets("Results")

ActiveSheet.Name = "Equipment " + CStr(i)
ActiveSheet.Visible = True

'Following line will return to the workbook
'containing this macro if required
'wbThis.Activate

Exit Sub

'errorhandler:
' Call MsgBox("Problem with AddWorksheet. " + _
' "You must close Excel and then reopen " + _
' "the tool to continue", vbCritical + _
' vbOKOnly, "EXCEL ADD WORKSHEET Error ")
' If DebugFlag Then
' MsgBox ("Error in " + "AddWorksheet")
' End If
End Sub


Feel free to get back to me if still having problems.

Regards,

OssieMac

Luca,

Thank you for your reference. That article explained the reason for a
different problem that we have experienced. However, the current problem
seems to only occur on a few machines and only for those worksheets that have
combobox objects in them. The problem occurs at any attempt to
programatically copy worksheets with those characteristics. Have any ideas?

"Luca Brasi" wrote:

you might want to take a look he
http://support.microsoft.com/kb/210684


OssieMac wrote:
Hi Steve,

I had a problem posting this reply so it is possible you might get it twice.

I have been out of the picture for a couple of days and hence the late
reply. The code runs successfully on Windows Vista with xl2007 and Windows XP
SP2 and xl 2002 so I am sorry but I don't think that I can offer any more
help.

Regards,

OssieMac

"Steve Rosenblum" wrote:

OssieMac,

I appreciate your suggestions however the problem still exists. Let me be
more explicit.

First - your last post got it right. the worksheets are in the same
workbook but the macro that I am running is in a different workbook.

Second - your code and my original code run fine on many machines. There
are only a few machines that I have seen where it fails.

It seems to fail on only XP machines. To be more specific, one of the
machines that it fails on is Windows XP v. 5.1 SP2 running MS Excel 2003 -
SP2.


I was hoping that I could find a more general solution that would not be
operating system / software version dependent. That was why I didn't include
this information in my prior posts.

If you have any insight/thoughts I would appreciate hearing them.

Steve

"OssieMac" wrote:

Hi yet again Steve,

I have been wondering if I have misinterpreted your question and that you
meant that you want to copy the worksheet within the same workbook but from a
macro in another workbook. If this is correct then the macro I gave you is
basically the same except that you can inititalize i to zero because it then
counts the Equipment_Std that it is copying as one worksheet and therefore
the number would be correct for adding another sheet name Equipment + i.

The other line of code to change is:-

'Copy sheet in same workbook (wbData)
wbData.Sheets(Base + "_Std").Copy _
Befo=wbData.Sheets("Results")

Regards,

OssieMac



"OssieMac" wrote:

Hi again Steve,

Your original post said that you wanted to copy from one workbook to
another. The following line of code you posted is to the same workbook:-

DataWorkbook.Sheets(Base + "_Std").Copy _
Befo=DataWorkbook.Sheets("Results")

Perhaps you have been working on trying to find the problem.

However, I have edited you code and made a few changes. Firstly it is not a
good idea to use words like WorkSheet as variables. (The name describes the
type of variable.)

I assumed that "Equipment" is passed to Base when the sub is called. As you
will see, I set up a dummy procedure to call the sub and pass the name to the
procedure.

I have tested the code below and it copies combo boxes with the worksheets.
(Tested with both Forms Combo boxes and ActiveX Combo Boxes) . However, if
you have linked ranges in the combo boxes linked to another worksheet in the
original workbook, after copying, it links those ranges back to the original
workbook. This does not occur if the linked ranges are on the same worksheet
being copied.

It has been tested with xl2007 and and on a separate computer with xl2002
(XP). Edit the "Set wbData =" line of code as per comment if not using
xl2007.

I disabled error handling mainly for the purpose of testing so that I could
see what was failing. Feel free to put it back in because all programs should
have some sort of error handling for the end user so they are not dropped
into the code and have no idea why.

Sub Calling_procedure()
Call AddWorksheet("Equipment")
End Sub


Sub AddWorksheet(Base As String)

'On Error GoTo errorhandler
Dim wbThis As Workbook 'This workbook
Dim wbData As Workbook 'DataWorkbook
Dim ws As Worksheet 'Variable for Each loop
Dim i As Integer 'For/Next loop

Set wbThis = ThisWorkbook

'xlsx extension is xl2007.
'Use xls for earlier versions
Set wbData = Workbooks.Item("DataWorkbook.xlsx")

'Following line needs to = 1 so that value finishes
'as 1 greater than what already exists for new sheet
i = 1

wbData.Activate
For Each ws In Worksheets
If ws.Name Like Base + "*" Then
i = i + 1
End If
Next ws


'Copy sheet from ThisWorkbook to wbData
wbThis.Sheets(Base + "_Std").Copy _
Befo=wbData.Sheets("Results")

ActiveSheet.Name = "Equipment " + CStr(i)
ActiveSheet.Visible = True

'Following line will return to the workbook
'containing this macro if required
'wbThis.Activate

Exit Sub

'errorhandler:
' Call MsgBox("Problem with AddWorksheet. " + _
' "You must close Excel and then reopen " + _
' "the tool to continue", vbCritical + _
' vbOKOnly, "EXCEL ADD WORKSHEET Error ")
' If DebugFlag Then
' MsgBox ("Error in " + "AddWorksheet")
' End If
End Sub


Feel free to get back to me if still having problems.

Regards,

OssieMac

I once run into troubles when trying to copy a sheet with objects into
first position of the workbook. Copying it into any other position
worked fine. That was strange, too. But I guess this error occurred on
all machines tested.

First of all I would install the latest patches for Office 2003 (SP3 I
guess?). There might be a problem with your version of Fm20.dll.

You are sure that the ".Copy"-line raises the error, right?

What if you create the workbook with the sheet to be copied (completely,
including the combo boxes) on a machine that shows this error?

Can you copy the sheet on such a machine manually?

Do you catch any events from these combo boxes? Which? What if you
remove the whole code from the sheet (for testing purpose only, of course)?

Also watch the names of the combo boxes. I never understood this
completely, but sometimes when copying a sheet, the names of the copied
controls get automatically changed.

As workaround, instead of copying the sheet you probably could create a
new sheet and copying the cell range with all objects? Or insert a new
sheet based on a template file to have the code behind it ready?



Steve Rosenblum wrote:
Luca,

Thank you for your reference. That article explained the reason for a
different problem that we have experienced. However, the current problem
seems to only occur on a few machines and only for those worksheets that have
combobox objects in them. The problem occurs at any attempt to
programatically copy worksheets with those characteristics. Have any ideas?

"Luca Brasi" wrote:

you might want to take a look he
http://support.microsoft.com/kb/210684


OssieMac wrote:
Hi Steve,

I had a problem posting this reply so it is possible you might get it twice.

I have been out of the picture for a couple of days and hence the late
reply. The code runs successfully on Windows Vista with xl2007 and Windows XP
SP2 and xl 2002 so I am sorry but I don't think that I can offer any more
help.

Regards,

OssieMac

"Steve Rosenblum" wrote:

OssieMac,

I appreciate your suggestions however the problem still exists. Let me be
more explicit.

First - your last post got it right. the worksheets are in the same
workbook but the macro that I am running is in a different workbook.

Second - your code and my original code run fine on many machines. There
are only a few machines that I have seen where it fails.

It seems to fail on only XP machines. To be more specific, one of the
machines that it fails on is Windows XP v. 5.1 SP2 running MS Excel 2003 -
SP2.


I was hoping that I could find a more general solution that would not be
operating system / software version dependent. That was why I didn't include
this information in my prior posts.

If you have any insight/thoughts I would appreciate hearing them.

Steve

"OssieMac" wrote:

Hi yet again Steve,

I have been wondering if I have misinterpreted your question and that you
meant that you want to copy the worksheet within the same workbook but from a
macro in another workbook. If this is correct then the macro I gave you is
basically the same except that you can inititalize i to zero because it then
counts the Equipment_Std that it is copying as one worksheet and therefore
the number would be correct for adding another sheet name Equipment + i.

The other line of code to change is:-

'Copy sheet in same workbook (wbData)
wbData.Sheets(Base + "_Std").Copy _
Befo=wbData.Sheets("Results")

Regards,

OssieMac



"OssieMac" wrote:

Hi again Steve,

Your original post said that you wanted to copy from one workbook to
another. The following line of code you posted is to the same workbook:-

DataWorkbook.Sheets(Base + "_Std").Copy _
Befo=DataWorkbook.Sheets("Results")

Perhaps you have been working on trying to find the problem.

However, I have edited you code and made a few changes. Firstly it is not a
good idea to use words like WorkSheet as variables. (The name describes the
type of variable.)

I assumed that "Equipment" is passed to Base when the sub is called. As you
will see, I set up a dummy procedure to call the sub and pass the name to the
procedure.

I have tested the code below and it copies combo boxes with the worksheets.
(Tested with both Forms Combo boxes and ActiveX Combo Boxes) . However, if
you have linked ranges in the combo boxes linked to another worksheet in the
original workbook, after copying, it links those ranges back to the original
workbook. This does not occur if the linked ranges are on the same worksheet
being copied.

It has been tested with xl2007 and and on a separate computer with xl2002
(XP). Edit the "Set wbData =" line of code as per comment if not using
xl2007.

I disabled error handling mainly for the purpose of testing so that I could
see what was failing. Feel free to put it back in because all programs should
have some sort of error handling for the end user so they are not dropped
into the code and have no idea why.

Sub Calling_procedure()
Call AddWorksheet("Equipment")
End Sub


Sub AddWorksheet(Base As String)

'On Error GoTo errorhandler
Dim wbThis As Workbook 'This workbook
Dim wbData As Workbook 'DataWorkbook
Dim ws As Worksheet 'Variable for Each loop
Dim i As Integer 'For/Next loop

Set wbThis = ThisWorkbook

'xlsx extension is xl2007.
'Use xls for earlier versions
Set wbData = Workbooks.Item("DataWorkbook.xlsx")

'Following line needs to = 1 so that value finishes
'as 1 greater than what already exists for new sheet
i = 1

wbData.Activate
For Each ws In Worksheets
If ws.Name Like Base + "*" Then
i = i + 1
End If
Next ws


'Copy sheet from ThisWorkbook to wbData
wbThis.Sheets(Base + "_Std").Copy _
Befo=wbData.Sheets("Results")

ActiveSheet.Name = "Equipment " + CStr(i)
ActiveSheet.Visible = True

'Following line will return to the workbook
'containing this macro if required
'wbThis.Activate

Exit Sub

'errorhandler:
' Call MsgBox("Problem with AddWorksheet. " + _
' "You must close Excel and then reopen " + _
' "the tool to continue", vbCritical + _
' vbOKOnly, "EXCEL ADD WORKSHEET Error ")
' If DebugFlag Then
' MsgBox ("Error in " + "AddWorksheet")
' End If
End Sub


Feel free to get back to me if still having problems.

Regards,

OssieMac