Name Worksheet based on Cell entry

Topic

New Reply

I am trying to create a macro that will copy an existing workbook and then rename that copy with Text joined with the contents of a cell address which will be a date. For example my worksheet I want to copy is named TestData. I would like for the macro to copy TestData and rename it NewData and join it with the contents of cell D4. So the end results will be NewData_090214 Any suggestions?

Thanks!

Reply | Quote

Replies

Mostate,

Is Testdata the ONLY sheet in the workbook?

I ask this because if it is it can be done with two file SaveAs commands otherwise it gets a little more complicated but still doable.

:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

No, TestData will not be the only worksheet.

Reply | Quote

Mostate,

Ok, here’s the code:

Code:

Sub CopyShtToNewWorkbook()

'*** Copy Single Sheet to New Workbook ***

   Dim zCurrName  As String
   Dim zDrvPath   As String
   Dim zAddToName As String
   
   zCurrName = ActiveWorkbook.Name
   zDrvPath = "G:BEKDocsExcel"   '*** Change to your information ***
   Sheets("TestData").Activate
   zAddToName = [D4].Value
   
   Sheets("TestData").Copy
   Application.DisplayAlerts = False  '** Prevent Overwrite Warning ***
   With ActiveWorkbook
   '    .ActiveSheet.Name = "RenamedSheet"  '*** Change Name and uncomment line
       .SaveAs Filename:=zDrvPath & "NewData_" & zAddToName & ".xlsm", _
           FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
       .Close
   End With  'ActiveWorkbook
   Application.DisplayAlerts = True

End Sub  'CopyShtToNewWorkbook()

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

RG,

Nicely done! A suggestion if I may. If the date is in the format of mm/dd/yyyy then an error will exist because the name of the file cannot contain a “/”.

Consider this tweak:

Code:

Sub CopyShtToNewWorkbook()

[COLOR=”#008000″]’*** Copy Single Sheet to New Workbook ***[/COLOR]

   Dim zCurrName  As String
   Dim zDrvPath   As String
   Dim zAddToName As String
   
   zCurrName = ActiveWorkbook.Name
   zDrvPath = “G:BEKDocsExcel”  [COLOR=”#008000″]’*** Change to your information ***[/COLOR]
   Sheets(“TestData”).Activate
   [COLOR=”#008000″]’zAddToName = [D4].Value[/COLOR]
  [COLOR=”#0000FF”] zAddToName = Month([D4]) & Day([D4]) & Year([D4])[/COLOR]
   
   Sheets(“TestData”).Copy
   Application.DisplayAlerts = False  ‘** Prevent Overwrite Warning ***
   With ActiveWorkbook
       .ActiveSheet.Name = “RenamedSheet”  [COLOR=”#008000″]’*** Change Name and uncomment line[/COLOR]
       .SaveAs Filename:=zDrvPath & “NewData_” & zAddToName & “.xlsm”, _
           FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
       .Close
   End With  [COLOR=”#008000″]’ActiveWorkbook[/COLOR]
   Application.DisplayAlerts = True

End Sub  [COLOR=”#008000″]’CopyShtToNewWorkbook()[/COLOR]

Maud

Reply | Quote

Just a little point, I find it better to save files with dates using Year-Month-Day – this makes files easier to sort into date order.

Regards,
Maria

Reply | Quote

Maud,

Good Point! I recognized it looked like a date but the OP didn’t specify that it was a date so I kind of ignored it. Nice mod if it does turn out to be a date with formatted /. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

This works great, thanks so much. How would I change the code if I wanted to copy the TestData worksheet with the same naming convention of NewData_090214 (NewData plus the contents of D4 which will be a date format) but keep it in the same workbook?

Thanks!

Reply | Quote

Mostate,

Much simpler code:

Code:

Sub CopyShtToNewSheet()

'*** Copy Single Sheet to New Worksheet in Same Workbook ***

   Dim zAddToName As String
   
   Sheets("TestData").Activate
   zAddToName = Format(Month([d4].Value), "0#") & Format(Day([d4].Value), "0#") & _
                Right(Format(Year([d4].Value), "0#"), 2)
   
   Sheets("TestData").Copy After:=Sheets(Sheets.Count)
   ActiveSheet.Name = "NewData_" & zAddToName
   
End Sub  'CopyShtToNewSheet()

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

I receive a syntax error on the following line of code
zAddToName = Format(Month([D4],”##”) & Format(Day([D4],”##”) & Format(Year([D4],”##”)

The date in D4 is formatted as mm-dd-yy, if that helps.
Thanks!

Reply | Quote

Mostate,

You must have grabbed the code before I made the final change, I got the same error.
Go back up to post 8 and get the code as it now stands. Sorry for the confusion. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Works Great…Thanks so much!

Reply | Quote