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
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