Macro to Copy/Move to End a new Worksheet

JaredMcCullough

Well-known Member
Joined
Aug 1, 2011
Messages
516
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.
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

ZAX

Well-known Member
Joined
Jul 5, 2012
Messages
715
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:
Code:
Sub MakeNewTemplate4Sheet2()
Sheets(2).Copy After:=Sheets(Sheets.Count)
End Sub
ZAX
 

JaredMcCullough

Well-known Member
Joined
Aug 1, 2011
Messages
516
Hi Jared,
Try using this:
Code:
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

Well-known Member
Joined
Aug 1, 2011
Messages
516
Hi Jared,
Try using this:
Code:
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

Well-known Member
Joined
Jul 5, 2012
Messages
715

ADVERTISEMENT

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:
Code:
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 :biggrin:
ZAX
 

JaredMcCullough

Well-known Member
Joined
Aug 1, 2011
Messages
516
Try:
Code:
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 :biggrin:
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

Well-known Member
Joined
Apr 11, 2012
Messages
10,111

ADVERTISEMENT

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

JaredMcCullough

Well-known Member
Joined
Aug 1, 2011
Messages
516
Perhaps this:
Code:
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

Well-known Member
Joined
Apr 11, 2012
Messages
10,111
This will also clear the cell colors.
Code:
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

Well-known Member
Joined
Aug 1, 2011
Messages
516
This will also clear the cell colors.
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,517
Messages
5,596,619
Members
414,081
Latest member
Subaru_Steve

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top