Using a macro assigned to a button to create a copy of the form in new worksheet.


New Member
Jan 1, 2021
Office Version
  1. 365
  1. Windows
Hi all,

Basically what I need is a macro that will copy all the data from one worksheet into another worksheet and prompt the user to name the new worksheet and then clear the existing original worksheet.

I work for a cement company and I need to record all the silo levels at various times in the day. I will need users to enter this data into a form I've created several times per day. I'd like for them to be able to enter the data and then click a button which will copy said data into a new worksheet which they can then be prompted to name (where they will enter date & shift) and then revert them back to the original form which will clear and become a fresh new template for the next user.

I have looked on here and found the following code which does everything I need apart from clearing the original data. I've tried tweaking it myself and creating separate macros to run concurrently but I always end up with either two sets of the same data or two blank forms!

Sub NewSheet()
Dim NewName As String, msg As String, Ans As Long
NewName = InputBox("Enter the name of the new sheet")
If NewName = "" Then Exit Sub
If SheetExists(NewName) Then
msg = "A sheet with the name " & NewName & " aleady exists."
msg = msg & vbCrLf & vbCrLf & "Answer Yes to replace the existing sheet, or No to exit and start over."
Ans = MsgBox(msg, vbYesNo)
If Ans = vbYes Then
With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With
Exit Sub
End If
End If
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = NewName
With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With
End Sub
Function SheetExists(shName As String) As Boolean
SheetExists = False
For Each sh In ActiveWorkbook.Sheets
If sh.Name = shName Then
SheetExists = True
Exit For
End If
Next sh
End Function

I'm wondering if anyone can help with this last step? A lot of the line ops here have never even turned a computer on so I'm trying to make this process for them as simple as possible! :)

Attached is the form I will be wanting to use. Most cells have drop down options for the users. As I have it set up now when I click save it will copy the form to a new worksheet and rename it and then by clicking 'new' it will clear the form. I'm just hoping I can do both these processes together by clicking on one button.

Many thanks in advance.



  • Form.png
    29.9 KB · Views: 24

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.


Well-known Member
Jul 3, 2012
Office Version
  1. 2019
  1. Windows
try following code & see if helps

VBA Code:
Sub ClearCells()
    Dim rng As Range, cell As Range
    Set rng = Range("G3,B7,E7,G7,B8,E8,G8,B9,E9,G9,B11,E11,G10,B12,E12,G11,B14,E14,G12," & _
    For Each cell In rng.Cells
       If cell.MergeCells Then cell.MergeArea.ClearContents Else cell.ClearContents
End Sub

call it at end of your copy code - adjust ranges as required

Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Latest member

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
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 "".
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