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.

 

[ZAX]

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.

Hi Jared,
Try using this:

Sub MakeNewTemplate4Sheet2()
Sheets(2).Copy After:=Sheets(Sheets.Count)
End Sub

ZAX

 

[JaredMcCullough]

Hi Jared,
Try using this:

Sub MakeNewTemplate4Sheet2()
Sheets(2).Copy After:=Sheets(Sheets.Count)
End Sub

ZAX

Thank you for the quick reply ZAX. I have a couple questions: Sheet2 is not actually called "Sheet2" its called "B" is that going to make a difference? It didn't seem to when I ran the code but I just want to make sure. My second issue was is there a way to make it once you attach the code to the active button that all you have to do is click the button and it continuously works? As of now from what I saw you have to open the "view macros" and hit run to get it to work I know their is a command for using buttons but its been a while for me.

 

[JaredMcCullough]

Hi Jared,
Try using this:

Sub MakeNewTemplate4Sheet2()
Sheets(2).Copy After:=Sheets(Sheets.Count)
End Sub

ZAX

I was able to fix the command button issue but is it alright if the name of sheet2 is not actually "Sheet2" it seems to work fine as I stated previously. The next step is I would like the button to first initiate the sequence that we have achieved to "Copy" then "Move to End" but now I want it to delete all rows in the newly copied sheet EXCEPT row A. is that plausible?

 

[ZAX]

I was able to fix the command button issue but is it alright if the name of sheet2 is not actually "Sheet2" it seems to work fine as I stated previously. The next step is I would like the button to first initiate the sequence that we have achieved to "Copy" then "Move to End" but now I want it to delete all rows in the newly copied sheet EXCEPT row A. is that plausible?

Try:

Sub MakeNewTemplate4Sheet2()
Sheets(2).Copy After:=Sheets(Sheets.Count)
ActiveSheet.Range("A2", Cells(Rows.Count, Columns.Count)).Clear
End Sub

But try it on a sample data
ZAX

 

[JaredMcCullough]

Try:

Sub MakeNewTemplate4Sheet2()
Sheets(2).Copy After:=Sheets(Sheets.Count)
ActiveSheet.Range("A2", Cells(Rows.Count, Columns.Count)).Clear
End Sub

But try it on a sample data
ZAX

ZAX, I tried it both in a copy of my running file as well as just generating a new excel document and unfortunately there seems to be an error in the code as that is what it states

 

[mumps]

Perhaps this:

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

 

[JaredMcCullough]

Perhaps this:

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

Seems to work decently but some formatting issues where cell colors were not cleared. Maybe this would be a better approach. Instead of copying the worksheet and moving to end would there be a way to use this command button to just "create a new" worksheet and "Move to end" but have this new worksheet copy the header template in Row 1 of Sheet 3 and apply it to the new worksheet??

 

[mumps]

This will also clear the cell colors.

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

 

[JaredMcCullough]

This will also clear the cell colors.

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

Getting Closer now it eliminates all the color and the Header "Row 1" has color. I am assuming this could be accomplished by using the offset? As well the it is copying cell borders to would it be possible to eliminate all borders and color except for that of the Header in "Row 1"? Again thanks for your help