Copy WS to new WB and use original WB sheet names

Prevost

Board Regular
Joined
Jan 23, 2014
Messages
198
Hi,
I am trying to copy worksheets with specific names to a new workbook. I want to copy the worksheet from a master template workbook. My problem is that it creates a correctly named worksheet in a new workbook and then searches that new workbook for the sheet template name (which does not exist) and then errors. I didn't even know that this code would copy to a new workbook...but that worked out well! Here's my code. Any help is appreciated as I cannot find the answer online!

Thanks

Code:
Option Explicit
Public WONo As String, Connection As String, SheetName As String
Public WOQty As Integer

Public Function WorksheetExists(WSName As String) As Boolean
On Error Resume Next
WorksheetExists = Worksheets(WSName).Name = WSName
On Error GoTo 0
End Function

Private Sub UserForm_Initialize()
    Dim WS As Worksheet
    Dim WB As Workbook
    Set WB = ActiveWorkbook
        For Each WS In WB.Worksheets
            ComboBox1.AddItem WS.Name
        Next WS
End Sub

Private Sub OK_Click()
    Dim i As Integer
    i = 1
        Connection = ComboBox1.Value
            For i = 1 To WOQty
                If WorksheetExists(Connection) Then
                    SheetName = WONo & "-" & i
                    Sheets(Connection).Copy
                    ActiveSheet.Name = SheetName
                    i = i + 1
                Else
                    MsgBox ("Invalid Connection Type")
                End If
            Next
End Sub
 
I don't really understand this problem but as a note it can be easier in some cases to copy an entire workbook then remove the unwanted sheets (vs.copying the wanted sheets to a new workbook).
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top