Create new sheet based on cell & select new sheet

rplohocky

Active Member
Joined
Sep 25, 2005
Messages
292
Office Version
  1. 365
Platform
  1. Windows
Hello,
I found this VBA code and it works great but I would like to add 2 additional steps.
VBA Code:
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!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Maybe this...
VBA Code:
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
 
Upvote 0
Maybe this...
VBA Code:
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.
 
Upvote 0
VBA Code:
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
 
Upvote 0
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?
 
Upvote 0
Code works fine for me...
It clears the range AFTER the new sheet has been named, so it doesn't matter
 
Upvote 0
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.
 
Upvote 0
VBA Code:
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
 
Upvote 0
Michael M,
This version is perfect! Thanks for your help!
 
Upvote 0
Glad to help..sorry for the confusion...?
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

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
Back
Top