Macro that copies a worksheet, renames it and copies data

[Closed]
Report
Posts
1
Registration date
Tuesday July 7, 2015
Status
Member
Last seen
July 7, 2015
-
Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
-
I am trying to create a macro that when I select an option from the dropdown list in cell E3 it creates a copy of another sheet in the workbook.




So selecting OB1 from the list will create a duplicate of the master template worksheet, OB1 MASTER

Selecting OB2 from the list will create a duplicate of the master template worksheet, OB2 MASTER

Selecting OB3 from the list will create a duplicate of the master template worksheet, OB3 MASTER

once it copies and creates the new sheet, I would like it to renames the worksheet based on the corresponding values in the rows. ie. selecting OB2, OB1, or OB3 in cell list E3 renames the new duplicated worksheet based on the values in A3&B3.

When it has created the new worksheet and renamed it, I would like it to copy the data from the corresponding row and paste it into the new sheet in the appropriate cells.

For example

Date from RACEDATES B3 is copied to new sheet into cell C6

Location from RACEDATES C3 is copied to new sheet into cell C7



I have managed to record a macro that when run creates the new work sheet, renames it and copies the required data across but it does not activate when i change data from the drop down list in RACEDATES E3.

This is what the code looks like:

Sub Macro5()
'
' Macro5 Macro
'

'
Sheets("OB1 MASTER").Select
Sheets("OB1 MASTER").Copy After:=Sheets(6)
Sheets("RACEDATES").Select
Range("A3:B3").Select
Selection.Copy
Sheets("OB1 MASTER (2)").Select
Sheets("OB1 MASTER (2)").Name = "SCTC 8 July 2015"
Sheets("RACEDATES").Select
Range("C3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SCTC 8 July 2015").Select
Range("C7:D7").Select
ActiveSheet.Paste
Sheets("RACEDATES").Select
Range("B3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SCTC 8 July 2015").Select
Range("C6:D6").Select
ActiveSheet.Paste
End Sub



I would like to keep adding to the information list in the RACEDATES worksheet and have the macro activate for each new row of data...

I hope this makes sense..

Thanks in advance
ADO

1 reply

Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
486
Hi Ado,

Take a look at the following query:
https://ccm.net/forum/affich-689438-autopopulating-multiple-sheets-from-one-main-sheet#p825486

It shows how a autorun macro looks like, so you can try to make it work for you.

Let me know how it goes.

Best regards,
Trowa