Create new sheet based on cell & select new sheet

[rplohocky]

Hello,
I found this VBA code and it works great but I would like to add 2 additional steps.

Sub Copyrenameworksheet()
'Updateby Extendoffice
    Dim ws As Worksheet
    Set wh = Worksheets(ActiveSheet.Name)
    ActiveSheet.Copy After:=Worksheets(Sheets.Count)
    If wh.Range("M2").Value <> "" Then
    ActiveSheet.Name = wh.Range("M2").Value
    End If
    wh.Activate
End Sub

This code creates a new tab and names it based on what I have in cell M2. After this is done, I need this to clear the data from cells M2 thru M8 then go to the newly created sheet and land on A14. Can this be done?

Thanks for your help!

 

[Michael M]

Maybe this...

Sub Copyrenameworksheet()
'Updateby Extendoffice
    Dim wh As Worksheet
    Set wh = Worksheets(ActiveSheet.Name)
    wh.Copy After:=Worksheets(Sheets.Count)
    If wh.Range("M2").Value <> "" Then
    ActiveSheet.Name = wh.Range("M2").Value
    End If
    wh.Activate
     Range("M2:M8").Clear
     Range("L14").Select
End Sub

 

[rplohocky]

Maybe this...

Sub Copyrenameworksheet()
'Updateby Extendoffice
    Dim wh As Worksheet
    Set wh = Worksheets(ActiveSheet.Name)
    wh.Copy After:=Worksheets(Sheets.Count)
    If wh.Range("M2").Value <> "" Then
    ActiveSheet.Name = wh.Range("M2").Value
    End If
    wh.Activate
     Range("M2:M8").Clear
     Range("L14").Select
End Sub

Thanks but this did not work. It actually cleared the original page M2 thru M8 but it did not go to the new page. Also I only want to clear the text in cells M2 thru M8 not the formatting.

 

[Michael M]

Sub Copyrenameworksheet()
'Updateby Extendoffice
    Dim wh As Worksheet
    Set wh = Worksheets(ActiveSheet.Name)
    wh.Copy After:=Worksheets(Sheets.Count)
    If wh.Range("M2").Value <> "" Then
    ActiveSheet.Name = wh.Range("M2").Value
    Range("M2:M8").ClearContents
    Range("L14").Select
End If
    wh.Activate
End Sub

 

[rplohocky]

Hey Michael M,
This code seems to error out. Based on what I read in the code, it clears the contents on M2, which is where the name of the new sheets is, then how would it know where to go after its gone?

 

[Michael M]

Code works fine for me...
It clears the range AFTER the new sheet has been named, so it doesn't matter

 

[rplohocky]

Ok I think I may have described what I need it to do wrong. Right now it creates a new sheet and names it from cell M2, then it clears the data in cells M2 thru M8 on the newly created sheet, then goes back to the original sheet and lands on L14. I need it to clear the data on the original sheet not the new one and also have the active page be the newly created one landing on cell L14. What I'm trying to do is use a template sheet that can be used over and over again. So the macro will create a new sheet pulling from cell M2 to name it and then clear the contents on the original one then go to the new sheet and land on L14.

 

[Michael M]

Sub Copyrenameworksheet()
'Updateby Extendoffice
    Dim wh As Worksheet
    Set wh = Worksheets(ActiveSheet.Name)
    wh.Copy After:=Worksheets(Sheets.Count)
    If wh.Range("M2").Value <> "" Then
    ActiveSheet.Name = wh.Range("M2").Value
    wh.Range("M2:M8").ClearContents
    Range("L14").Select
End If
End Sub

 

[rplohocky]

Michael M,
This version is perfect! Thanks for your help!

 

[Michael M]

Glad to help..sorry for the confusion...?