VBA to copy multiple worksheets and then rename them

Hello
The code I'm using below which I got from a post by Kenneth Hobson works great for 3 sheets.
How can I make the code below work to just copy and rename 1 sheet? Ive tried tinkering without sucess.
Also is it possible to do the same as above regardless of whether the sheets that its copying from are hidden or visible
Id be grateful for any help at all


Hello
Sub Copy3Sheets()
Dim ws(1 To 3) As Worksheet, v As Variant, s As String

Set ws(1) = Worksheets("Yearly Lease Order")
Set ws(2) = Worksheets("Yearly Quote Form")
Set ws(3) = Worksheets("Yearly Invoice Form")

For Each v In ws()
s = Worksheets("Costings Main Menu").Range("C4").Value2
s = s & " " & v.Name
If Not WorkSheetExists(s) Then
v.Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = s
End If
Next v

Worksheets("Costings Main Menu").Activate
Range("A1").Select
End Sub


'WorkSheetExists in a workbook:
Function WorkSheetExists(sWorkSheet As String, Optional sWorkbook As String = "") As Boolean
Dim ws As Worksheet, wb As Workbook
On Error GoTo notExists
If sWorkbook = "" Then
Set wb = ActiveWorkbook
Else
Set wb = Workbooks(sWorkbook)
End If
Set ws = wb.Worksheets(sWorkSheet)
WorkSheetExists = True
Exit Function
notExists:
WorkSheetExists = False
End Function

IF you just want to copy one sheet, just use one element in the array.

Obviously you need to change the prefix and sheet name to suit.

Please Login or Register  to view this content.