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

LiamSutty

New Member
Joined
Jan 1, 2021
Messages
1
Office Version
  1. 365
Platform
  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
Sheets(NewName).Delete
Else
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.

Liam
 

Attachments

  • Form.png
    Form.png
    29.9 KB · Views: 16

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,382
Office Version
  1. 2019
Platform
  1. Windows
Hi
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," & _
                    "B16,E16,I16,B17,E17,B19,E19,I19,B20,E20,I20,B21,E21,I21,D23,E23,H23")
    For Each cell In rng.Cells
       If cell.MergeCells Then cell.MergeArea.ClearContents Else cell.ClearContents
    Next
End Sub

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

Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,123,017
Messages
5,599,362
Members
414,306
Latest member
Dennis_vdw

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