Combined two codes in one

[Jeffreyxx01]

Hello everyone,

I would like to create a macro that copy my "worker" tab and pop a message box that will change the name of the new tab for example: "worker" >> "XXX"

Also I want the macro to clear the contents of the cells I would allocate for my needs for example:

A1 A2 A3 >>> Those are just example,

Can someone help?

Thanks

[/COLOR]Private Sub ResetForm_Click()
'Deletes all user fields


Dim ws As Worksheet
With Worksheets("Worker")
  .Range("D4:F4").ClearContents
  .Range("D7:F10").ClearContents
  .Range("D22:F25").ClearContents
  .Range("D30:F31").ClearContents
  .Range("D35:F35").ClearContents
  .Range("D38:F38").ClearContents
  .Range("D43:F44").ClearContents
End With


End Sub


Sub AddSheets()
Dim NewName As String
Another:
OneMore = False
NewName = InputBox("New Name?")
Sheets.Add
ActiveSheet.Name = NewName
If MsgBox("Do you want to Add another Sheet?", vbYesNo) = 6 Then GoTo Another
End Sub


[COLOR=#333333]
[/COLOR]

 

[mumps]

Try:

Private Sub ResetForm_Click()
    Application.ScreenUpdating = False
    Dim NewName As String
    Worksheets("Worker").Range("D4:F4,D7:F10,D22:F25,D30:F31,D35:F35,D38:F38,D43:F43").ClearContents
Another:
    Sheets("Worker").Copy After:=Sheets(Sheets.Count)
    NewName = InputBox("New Name?")
    If NewName = "" Then
        MsgBox ("You have not entered a name.")
        Application.ScreenUpdating = True
        Exit Sub
    End If
    ActiveSheet.Name = NewName
    If MsgBox("Do you want to Add another Sheet?", vbYesNo) = vbYes Then GoTo Another
    Application.ScreenUpdating = True
End Sub

 

[Jeffreyxx01]

Thanks,
It works well now

 

[mumps]

You are very welcome.

 

[Jeffreyxx01]

Hi mumps,

is it possible to twist the macro when I dont put a name to the new tab it wont create a new tab because when i click on cancel from the message box "new name", it still gives me a new tab,

hope it is clear

Try:

Private Sub ResetForm_Click()
    Application.ScreenUpdating = False
    Dim NewName As String
    Worksheets("Worker").Range("D4:F4,D7:F10,D22:F25,D30:F31,D35:F35,D38:F38,D43:F43").ClearContents
Another:
    Sheets("Worker").Copy After:=Sheets(Sheets.Count)
    NewName = InputBox("New Name?")
    If NewName = "" Then
        MsgBox ("You have not entered a name.")
        Application.ScreenUpdating = True
        Exit Sub
    End If
    ActiveSheet.Name = NewName
    If MsgBox("Do you want to Add another Sheet?", vbYesNo) = vbYes Then GoTo Another
    Application.ScreenUpdating = True
End Sub

 

[mumps]

Give this a try:

Private Sub ResetForm_Click()
    Application.ScreenUpdating = False
    Dim NewName As String
    Worksheets("Worker").Range("D4:F4,D7:F10,D22:F25,D30:F31,D35:F35,D38:F38,D43:F43").ClearContents
Another:
    NewName = InputBox("New Name?")
    If NewName = "" Then
        MsgBox ("You have not entered a name.")
        Application.ScreenUpdating = True
        Exit Sub
    Else
        Sheets("Worker").Copy After:=Sheets(Sheets.Count)
    End If
    ActiveSheet.Name = NewName
    If MsgBox("Do you want to Add another Sheet?", vbYesNo) = vbYes Then GoTo Another
    Application.ScreenUpdating = True
End Sub

 

[Jeffreyxx01]

it did work, thanks a lot

 

[mumps]

My pleasure.

 

[Jeffreyxx01]

Hi again mumps,
there is a problem on the macro, when I type the name of the thing in the message box, this work it is fine but it erase everything from the worker and erase everything on the new tab,
I wanted that the macro copy the data onto the new tab that I name using the message box and after that it erases the data on the worker,

is it possible?

 

[mumps]

Maybe:

Private Sub ResetForm_Click()
    Application.ScreenUpdating = False
    Dim NewName As String
Another:
    NewName = InputBox("New Name?")
    If NewName = "" Then
        MsgBox ("You have not entered a name.")
        Application.ScreenUpdating = True
        Exit Sub
    Else
        Sheets("Worker").Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = NewName
        Sheets("Worker").Range("D4:F4,D7:F10,D22:F25,D30:F31,D35:F35,D38:F38,D43:F43").ClearContents
    End If
    If MsgBox("Do you want to Add another Sheet?", vbYesNo) = vbYes Then GoTo Another
    Application.ScreenUpdating = True
End Sub