ROBJ
11-29-2017, 07:26 AM
Hello there.
I have the following VBA code. This one creates a new WS at the end of the WB. I need however to copy the current (active) sheet and be able to copy it before a specific sheet. What do I need to edit in the code to be able to copy?
And can the copy to the specific location be solved with a 2nd input box?
Thanking you in advance.
Public Sub AddSheet()
Dim shName As String
Dim shExists As Boolean
Do
shName = InputBox("Please Enter Seet Name")
If shName <> "" Then
shExists = SheetExists(shName)
If Not shExists Then
Worksheets.Add(After:=Worksheets(Worksheets.Count) ).Name = shName
Else
MsgBox "Worksheet " & shName & " already exists", vbOKOnly + vbInformation, "Add Sheet"
End If
End If
Loop Until Not shExists Or shName = ""
End Sub
Private Function SheetExists(ByVal SheetName As String, _
Optional ByVal wb As Workbook)
If wb Is Nothing Then Set wb = ActiveWorkbook
On Error Resume Next
SheetExists = Not wb.Worksheets(SheetName) Is Nothing
End Function
I have the following VBA code. This one creates a new WS at the end of the WB. I need however to copy the current (active) sheet and be able to copy it before a specific sheet. What do I need to edit in the code to be able to copy?
And can the copy to the specific location be solved with a 2nd input box?
Thanking you in advance.
Public Sub AddSheet()
Dim shName As String
Dim shExists As Boolean
Do
shName = InputBox("Please Enter Seet Name")
If shName <> "" Then
shExists = SheetExists(shName)
If Not shExists Then
Worksheets.Add(After:=Worksheets(Worksheets.Count) ).Name = shName
Else
MsgBox "Worksheet " & shName & " already exists", vbOKOnly + vbInformation, "Add Sheet"
End If
End If
Loop Until Not shExists Or shName = ""
End Sub
Private Function SheetExists(ByVal SheetName As String, _
Optional ByVal wb As Workbook)
If wb Is Nothing Then Set wb = ActiveWorkbook
On Error Resume Next
SheetExists = Not wb.Worksheets(SheetName) Is Nothing
End Function