Macro to Copy/Move to End a new Worksheet

[JaredMcCullough]

Looking for some help creating a Macro to initiate a New Worksheet cycle. Preferably looking to use a VBA Command button to initiate the process. What I am wanting it to do is copy the second "Worksheet" in the series (ex. Sheet2) and Move to End of worksheets thus creating a copy of the second worksheet as a new template to work on. This is the first sequence of events in the over all goal I just figured it would be best to start one at a time.

 

[mumps]

Sub CopySheet()
    Application.ScreenUpdating = False
        Sheets("B").Copy After:=Sheets(Sheets.Count)
        With ActiveSheet
            .UsedRange.Offset(1, 0).ClearContents
            .UsedRange.Offset(1, 0).Interior.ColorIndex = xlNone
            .UsedRange.Offset(1, 0).Borders.LineStyle = xlNone
        End With
    Application.ScreenUpdating = True
End Sub

 

[JaredMcCullough]

End Sub[/code][/QUOTE]

Ok now for the relation to the LCSimmons project. In the process of creating the new worksheet by using the "Command Button" I would like it to stimulate an inputbox that renames the worksheet to the users keyed in response. Essentially this would work as such. You hit the command button to insert new worksheet along with the new worksheet being created which we have already achieved. The user as well receives a dialog box to input a name for the worksheet. He/She inputs the desired name and the worksheet name is then changed.

 

[mumps]

Hi Jared. The macro we were working with didn't insert a new sheet, it copied an existing sheet named "B". Do you want to insert a new sheet or copy sheet "B" and re-name it?

 

[JaredMcCullough]

Hi Jared. The macro we were working with didn't insert a new sheet, it copied an existing sheet named "B". Do you want to insert a new sheet or copy sheet "B" and re-name it?

I apologize poor terminology. How it works is fine now basically what I was trying to accomplish was to insert a "new" sheet with the same header as the others but by using the copy/move to end function as it is designed now it offers the desired end result. But yes now my goal is to rename the copied sheet.

 

[mumps]

How about this:

Sub CopySheet()
    Application.ScreenUpdating = False
    Dim sName As String
    sName = InputBox("Please enter a name for the sheet.")
        Sheets("B").Copy After:=Sheets(Sheets.Count)
        With ActiveSheet
            .Name = sName
            .UsedRange.Offset(1, 0).ClearContents
            .UsedRange.Offset(1, 0).Interior.ColorIndex = xlNone
            .UsedRange.Offset(1, 0).Borders.LineStyle = xlNone
        End With
    Application.ScreenUpdating = True
End Sub

 

[JaredMcCullough]

How about this:

Sub CopySheet()
    Application.ScreenUpdating = False
    Dim sName As String
    sName = InputBox("Please enter a name for the sheet.")
        Sheets("B").Copy After:=Sheets(Sheets.Count)
        With ActiveSheet
            .Name = sName
            .UsedRange.Offset(1, 0).ClearContents
            .UsedRange.Offset(1, 0).Interior.ColorIndex = xlNone
            .UsedRange.Offset(1, 0).Borders.LineStyle = xlNone
        End With
    Application.ScreenUpdating = True
End Sub

Almost done and I appreciate all your help. Now the final step. I have a column listing all the worksheet "Names" in "Sheet2" under column "M". This was typically done manually but I was wondering if when a work sheet was added if it could put this name into the next available row in column M. The next step is beside the listing of worksheet name in Column "M" in Column "N" of worksheet 2 there is a code that I use for another part of the worksheet. This code is a 3 Letter code entered to represent the worksheet name. Example work sheet name in column "M" is "Crude Unit" the code in column "N" is "CDU". I was wanting the same input box to as well ask for a 3 letter code which then will be translated into column "N" beside the name of the worksheet which is translated into "M". (In the next available row) I hope this makes some sense.

 

[JaredMcCullough]

Another way to look at it would be. Input Box asks for 2 things "Worksheet Name" and "Code". The worksheet is renamed after the entered "Worksheet Name". The "Worksheet Name" is translated to the next available row in column "M" of "Sheet2" and the code is translated beside it in column "N" of "Sheet2".

 

[mumps]

Try:

Sub CopySheet()
    Application.ScreenUpdating = False
    Dim sName As String
    Dim sCode As String
    sName = InputBox("Please enter a name for the sheet.")
    sCode = InputBox("Please enter a code for the sheet.")
    Sheets("B").Copy After:=Sheets(Sheets.Count)
    With ActiveSheet
        .Name = sName
        .UsedRange.Offset(1, 0).ClearContents
        .UsedRange.Offset(1, 0).Interior.ColorIndex = xlNone
        .UsedRange.Offset(1, 0).Borders.LineStyle = xlNone
    End With
    Sheets("Sheet2").Cells(Rows.Count, "M").End(xlUp).Offset(1, 0) = sName
    Sheets("Sheet2").Cells(Rows.Count, "N").End(xlUp).Offset(1, 0) = sCode
    Application.ScreenUpdating = True
End Sub

 

[JaredMcCullough]

Thank you so much mumps....I now have 2 final questions. First when using an input box is it possible to instead of having 2 input boxes combine them into one? And my second question is that is there a way that if I rename "Sheet2" it will adjust in the code or is this something that will always have to be done manually in code?

 

[mumps]

You need two input boxes because each response has to be assigned to a different variable. You'll have to change the sheet name manually.