Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts

    Sheet 2 is same format as sheet 1 without copy/paste special/formats

    Code:
    Option Explicit
     
    Sub add_Sheet_name()
        Dim sShtName As String
        
         Sheets("SAVER").Select
         
        sShtName = Sheet4.Cells(3, 1).Value
         
        If Not WksExists(sShtName) Then Worksheets.Add After:=Worksheets(Worksheets.Count)
         
        ActiveSheet.Name = sShtName
        ActiveSheet.Range("A1:Z150").Value = Sheets("SAVER").Range("A1:Z150").Value
        ActiveSheet.Range("A1:Z150").Format = Sheets("SAVER").Range("A1:Z150").Format
        
    End Sub
    Function WksExists(wksName As String) As Boolean
        On Error Resume Next
        WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
    End Function


    The above code, ( not mine ) will add a new sheet and name the sheet according to a cell range
    in Sheets(SAVER). Usually the date being yyyymmdd-"reference code"
    However, the new sheet is blank and the way this code is writen, no matter what sheet name it is, it will look for the sheet's original number. In this case it's Sheet4, even if I do re-name it "SAVER".
    In the VBA Project Window it shows "Sheet 4 ( SAVER )" and in Cell A3 is where the new sheet gets it's
    reference to name it.
    No problems there, I have worked around it, some extra coding but it will do for now.

    However, I need to fill the new blank re-named worksheet with the new data, and then it's finally saved.
    The workbook is in fact "storage" for later use if need be. ( it's not important right now )

    What's important is to be able to read it if need be.
    The method I use is with the following code to transfer the data to the new worksheet, but the new worksheet has no format.
    So I figured, logic would assume if
    Code:
    ActiveSheet.Range("A1:Z150").Value = Sheets("SAVER").Range("A1:Z150").Value
    Does work, why then does not the following code work to not only equal the Value between
    A1:Z150, but also the Format ?

    Code:
    ActiveSheet.Range("A1:Z150").Format = Sheets("SAVER").Range("A1:Z150").Format
    How can I transfer the format from sheet SAVER to the newly named worksheet ?


    Thanks in advance

    XP

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    XP,

    Instead of adding a new worksheet, copy the existing sheet then just select the entire new sheet and use selection.clearcontents. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    Thanks for the reply,

    RG.


    The macro cannot Copy and Paste the format because it does not "know" the name of the worksheet
    with it's new name, to Select that Sheet.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    XP,

    This worked as I think you wanted?
    Code:
    Option Explicit
    
    Sub add_Sheet_name()
    
        Dim sShtName As String
        
         Sheets("SAVER").Select
         
        sShtName = Sheet2.Cells(3, 1).Value  '** Changed to Sheet 2 for test ***
         
        If Not WksExists(sShtName) Then Sheets("SAVER").Copy After:=Sheets(Sheets.Count)
         
        ActiveSheet.Name = sShtName
        Cells.Select
        Selection.ClearContents
        [A1].Select
        ActiveSheet.Range("A1:Z150").Value = Sheets("SAVER").Range("A1:Z150").Value
        
    End Sub  'add_Sheet_name
    My Test Workbook: XPDiHards New Sheet.xlsm
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts

    Reference by position

    The macro cannot Copy and Paste the format because it does not "know" the name of the worksheet
    with it's new name, to Select that Sheet.
    XP,
    Another way is to select the sheet by its position. If you create the sheet and put it in front you can always reference it as Sheet(1). Then you can give it a name or do what you want with it.

    Code:
    ActiveSheet.Copy Before:=Sheets(1)
    Sheets(1).Select
    Cells.Select
    Selection.ClearContents
    Last edited by Maudibe; 2013-04-10 at 23:05.

  6. #6
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    RG
    Thanks again,

    It worked perfectly for over 25 test runs.
    I was tweaking things as I tested, then all of sudden it stopped working.
    There were no code changes or sheet(4) changes whatsoever.
    I copied your code again and started from scratch in the test workbooks, same again for no obvious reason.

    I have uploaded the 2 workbooks to show how this is suppose to work.
    It's referenced in C:\ ( folder name ) so for your own tests it may have to be changed.

    Simply open workbook named: SOURCE-TEST.XLS and Press the macro button.
    Everything else is automatic, supposedly. ( both workbooks have to be in the same folder/location.

    It errors precisely at:
    Code:
    If Not WksExists(sShtName)
    Compile Error, Sub or Function not Defined.

    I regard this as solved, because it's working in the main Worbooks.
    But stopped working in the test workbooks.

    Thanks Maudibe, I'll test your suggestions to.

    XP
    Attached Files Attached Files

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    XP,

    The error is due to the fact that the function WksExists does NOT exist in either of the files! You had it in your original post but when I sent back my code I didn't include it since I didn't change it. You must have copied my code and then completely replaced yours instead of just the one Sub. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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