Related:
- Excel macro copy sheet and rename based on cell value
- Excel vba copy sheet and rename based on cell value - Best answers
- Excel vba copy worksheet and rename - Best answers
- Excel vba copy sheet and rename based on list ✓ - Forum - Excel
- How To Create and Name Worksheets Based on a List via an Excel Macro - How-To - Excel
- Excel macro to send email automatically based on cell value - Forum - Programming
- Excel macro active sheet ✓ - Forum - Excel
- Excel macro to create new workbook based on value in cells ✓ - Forum - Excel
2 replies
yg_be
- Posts
- 13771
- Registration date
- Sunday June 8, 2008
- Status
- Contributor
- Last seen
- January 5, 2021
The easiest way to achieve that is to start recording a new macro, then manually do the operations (creating a new worksheet, copying template to the new worksheed, renaming the new worksheet.
Once you have that macro, you will easily (perhaps with some assisitance from this forum) adapt it according to your needs.
Once you have that macro, you will easily (perhaps with some assisitance from this forum) adapt it according to your needs.
Josephnb
- Posts
- 2
- Registration date
- Tuesday November 15, 2016
- Status
- Member
- Last seen
- November 16, 2016
Sorry, should have added the code
Sub Copyrenameworksheet()
Dim ws As Worksheet
Set wh = Worksheets(ActiveSheet.Name)
ActiveSheet.copy After:=Worksheets(Sheets.Count)
If wh.Range("i9").Value <> "" Then
ActiveSheet.Name = wh.Range("i9").Value
End If
'go back to original sheet and delete cell content. Original sheet being used as the template
Wh.Activate
Range("D11:F11, H11:J11").Select
selection.ClearContents
'Would like to go to the new sheet here
Wh.range("I9").Activate
End Sub
-------------------------
Problem:
1) i would like the code to run automatically when cell D11 & H11 are not blank. The code works but I need to click a button
2) I would like the"new" worksheet to open and be automatically renamed based on cell I9. The code renames the sheet but does not open the "new" worksheet.
3) I would like cells D11 & H11 in the "template" to clear once the "new worksheet is created and opened.
Sub Copyrenameworksheet()
Dim ws As Worksheet
Set wh = Worksheets(ActiveSheet.Name)
ActiveSheet.copy After:=Worksheets(Sheets.Count)
If wh.Range("i9").Value <> "" Then
ActiveSheet.Name = wh.Range("i9").Value
End If
'go back to original sheet and delete cell content. Original sheet being used as the template
Wh.Activate
Range("D11:F11, H11:J11").Select
selection.ClearContents
'Would like to go to the new sheet here
Wh.range("I9").Activate
End Sub
-------------------------
Problem:
1) i would like the code to run automatically when cell D11 & H11 are not blank. The code works but I need to click a button
2) I would like the"new" worksheet to open and be automatically renamed based on cell I9. The code renames the sheet but does not open the "new" worksheet.
3) I would like cells D11 & H11 in the "template" to clear once the "new worksheet is created and opened.
yg_be
- Posts
- 13771
- Registration date
- Sunday June 8, 2008
- Status
- Contributor
- Last seen
- January 5, 2021
For items 2, I assume you want the "new" worksheet to be created automatically, is that right?
For items 2 & 3 : start recording a new macro, then manually do the operations you want to automate, then stop recording. Then have a look into the code of the recorded macro, and enhance your macro accordingly.
For items 2 & 3 : start recording a new macro, then manually do the operations you want to automate, then stop recording. Then have a look into the code of the recorded macro, and enhance your macro accordingly.
yg_be
- Posts
- 13771
- Registration date
- Sunday June 8, 2008
- Status
- Contributor
- Last seen
- January 5, 2021
Do you expect that
There are various ways to correct that. Here is my suggestion :
Replace your code :
by
Suggestion : avoid using select and activate in VBA.
Wh.range("I9").Activate will activate your new sheet? It actually activates the cell I9 in the sheet corresponding to the variable wh, hence in your template sheet.
There are various ways to correct that. Here is my suggestion :
Replace your code :
'go back to original sheet and delete cell content. Original sheet being used as the template
Wh.Activate
Range("D11:F11, H11:J11").Select
selection.ClearContents
'Would like to go to the new sheet here
Wh.range("I9").Activate
by
' delete cell content in original sheet being used as the template
Wh.Range("D11:F11, H11:J11").ClearContents
'nothing to do, we are already in the new sheet here
Suggestion : avoid using select and activate in VBA.
Subscribe To Our Newsletter!
