Results 1 to 3 of 3

Thread: Code to copy the active WS before a specific WS in the WB

  1. #1
    Junior Member
    Join Date
    Nov 2017
    Posts
    6
    Rep Power
    0

    Code to copy the active WS before a specific WS in the WB

    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.

    Code:
    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
    Last edited by Admin; 11-29-2017 at 08:22 AM. Reason: code tag added

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    @ Rob,

    Please use code tags while posting code. I have added the code tag for you this time.

    try

    Code:
    Worksheets.Add before:=Worksheets("SpecificSheetName")
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Junior Member
    Join Date
    Nov 2017
    Posts
    6
    Rep Power
    0
    Thank you

    Sorry about that.

    Cheers

    Rob

Similar Threads

  1. Replies: 1
    Last Post: 03-11-2014, 06:03 PM
  2. Replies: 4
    Last Post: 02-04-2014, 05:20 PM
  3. Replies: 8
    Last Post: 07-01-2013, 03:52 PM
  4. Macro to copy data in specific Columns
    By Howardc in forum Excel Help
    Replies: 0
    Last Post: 04-19-2013, 10:42 AM
  5. don't copy filtered data if no active cells
    By xander1981 in forum Excel Help
    Replies: 29
    Last Post: 11-01-2012, 06:47 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •