Copy paste Rename based on cell value

Solved/Closed

Excel

Josephnb Posts 2 Registration date Tuesday November 15, 2016 Status Member Last seen November 16, 2016 - Nov 15, 2016 at 03:40 PM
Josephnb - Nov 21, 2016 at 08:28 AM

Hello

I have a worksheet named template. I need a VBA code which would copy "template" automatically into a new worksheet and rename the new worksheet based on the value from two cells in "template".

Thanks

Excel/vba copy sheet and rename based on cell value
Rename computer cmd - Guide
Rename lg tv - Guide
Based on the values in cells b77:b81, what function can automatically return the value in cell c77? ✓ - Excel Forum
Mark sheet in excel - Guide
Number to words in excel formula without vba - Guide

2 responses

Answer 1 / 2

yg_be Posts 23184 Registration date Sunday June 8, 2008 Status Contributor Last seen September 15, 2024 5
Nov 15, 2016 at 04:19 PM

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.

Answer 2 / 2

Josephnb Posts 2 Registration date Tuesday November 15, 2016 Status Member Last seen November 16, 2016
Nov 16, 2016 at 08:16 AM

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.

yg_be Posts 23184 Registration date Sunday June 8, 2008 Status Contributor Last seen September 15, 2024 5
Nov 16, 2016 at 10:12 AM

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.

Joe
Nov 17, 2016 at 01:56 PM

1) you are correct with your assumption for item 2
2) I've done what you have suggested with 3 different macros but I still can not get the new page, which is renamed based on my code above to open to the new page. When I run the Macro, it copies, paste and renames just will not open to it.

yg_be Posts 23184 Registration date Sunday June 8, 2008 Status Contributor Last seen September 15, 2024 5
Nov 17, 2016 at 02:15 PM

Do you expect that

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

' 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.

yg_be Posts 23184 Registration date Sunday June 8, 2008 Status Contributor Last seen September 15, 2024 5
Nov 21, 2016 at 02:25 AM

Does that work now?

Josephnb
Nov 21, 2016 at 08:28 AM

Great thank you. It's the "wh" that I was missing.

Thanks for the help!

Similar discussions

VBA Code to Copy Worksheet multiple times and rename based on list

Copy Worksheet to another Workbook (unopened)

A macro to create new, copy and name worksheets based on a list

Code to move rows into another sheet based on certain values

Copy rows to other sheets based on value in column

Create new sheets based on Column A data.

Similar discussions

Subscribe To Our Newsletter!