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

[LiamSutty]

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

 

[dmt32]

Hi
try following code & see if helps

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