Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts

    Name Worksheet based on Cell entry

    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!

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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.

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts
    No, TestData will not be the only worksheet.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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:\BEKDocs\Excel"   '*** 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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. The Following User Says Thank You to RetiredGeek For This Useful Post:

    MOSTATE (2014-09-03)

  6. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 646 Times in 590 Posts
    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()
    
    '*** Copy Single Sheet to New Workbook ***
    
       Dim zCurrName  As String
       Dim zDrvPath   As String
       Dim zAddToName As String
       
       zCurrName = ActiveWorkbook.Name
       zDrvPath = "G:\BEKDocs\Excel"  '*** Change to your information ***
       Sheets("TestData").Activate
       'zAddToName = [D4].Value
       zAddToName = Month([D4]) & Day([D4]) & Year([D4])
       
       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()
    Maud
    Last edited by Maudibe; 2014-09-02 at 20:08.

  7. The Following User Says Thank You to Maudibe For This Useful Post:

    MOSTATE (2014-09-03)

  8. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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 /.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #7
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts
    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!

  10. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    Last edited by RetiredGeek; 2014-09-03 at 09:14.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. #9
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts
    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!

  12. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. The Following User Says Thank You to RetiredGeek For This Useful Post:

    MOSTATE (2014-09-03)

  14. #11
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts
    Works Great...Thanks so much!

  15. #12
    Star Lounger
    Join Date
    Feb 2009
    Location
    Kings Park, Victoria, Australia
    Posts
    90
    Thanks
    15
    Thanked 5 Times in 5 Posts
    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
    Maria
    Simmo7
    Victoria, Australia

  16. The Following User Says Thank You to simmo7 For This Useful Post:

    XPDiHard (2014-09-09)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •