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