Hi All
Having so problems with the following vba code
I need to copy hidden work sheets not all to a new work book and name the new work book as well
I get a Run time 1004 Cannot rename a sheet to the same name as another sheet
the code line in debug are ActiveSheet.Name = x(i)
With .Sheets(x(i))
any help Please and Thanks
Sub Results_Click()
Dim NewName As String, s As String, wb As Workbook, ws As Worksheet, i As Integer, x
s = "Open & Non Pro & Nov Horse & Rookie & Green Horse & Youth & Non Pro & Nrha Green Reiner & Short Stirrups"
x = Split(s, " & ")
If MsgBox("Sheets:" & vbCr & vbCr & s & vbCr & vbCr & "will be copied to a new workbook" & vbCr & vbCr & _
"The sheets will be values only (named ranges, formulas and links removed)" & vbCr & vbCr & _
"Do you want to continue?", vbYesNo, "Create New Workbook") = vbNo Then Exit Sub
NewName = InputBox("Please Enter the name for the new workbook", "New Workbook Name")
Application.ScreenUpdating = False
Workbooks.Add
Set wb = ActiveWorkbook
With wb
For i = 0 To UBound(x)
Set ws = ThisWorkbook.Sheets(x(i))
ws.Cells.Copy
.Sheets.Add after:=Sheets(Sheets.Count):
ActiveSheet.Name = x(i)
With .Sheets(x(i))
.[a1].PasteSpecial Paste:=xlValues
.Cells.PasteSpecial Paste:=xlFormats
.Cells.Hyperlinks.Delete
Application.Goto .[a1]
End With
Next
Application.DisplayAlerts = False
For i = 1 To 1
.Sheets("Sheet" & i).Delete
Next
Application.DisplayAlerts = True
.SaveAs (NewName & ".xls")
End With
ThisWorkbook.Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub
Bookmarks