+ Reply to Thread
Results 1 to 5 of 5

Set Cell to Copy hidden Worksheet to End & Rename with input box

Hybrid View

  1. #1
    Registered User
    Join Date
    09-06-2022
    Location
    Phoenix, Arizona
    MS-Off Ver
    365
    Posts
    3

    Set Cell to Copy hidden Worksheet to End & Rename with input box

    Good day,
    I'm seeking assistance as I have most of the VBA code written out properly, however when creating a copy of a hidden worksheet the input box is not associating to the new worksheet and continues to want to rename the first copy worksheet. Please see the code below. Any assistance would be amazing!

    Sub Group14_Click()
    '
    ' Group14_Click Macro
    ' Macro recorded
    '
    
    '
        Counter = 0
        myNum = Worksheets("Info").Cells(13, 7)
        For x = 1 To myNum
            Counter = Counter - 1
        Sheets("Recipe Template").Visible = True
        Sheets("Recipe Template").Select
        Sheets("Recipe Template").Copy After:=Sheets(Sheets.Count)
        Sheets("Recipe Template").Select
        ActiveWindow.SelectedSheets.Visible = False
        Next x
      Dim newName As String
     
     newName = InputBox("Rename your Recipe, if you created more than 1 template please rename the remaining manually.", "Theoretical & True Food Cost", "Have a Great day!")
     If newName <> "" Then
        
        On Error Resume Next
        ActiveSheet.Name = newName
        End If
    End Sub

  2. #2
    Spammer
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    1,237

    Re: Set Cell to Copy hidden Worksheet to End & Rename with input box

    Try adding this line...
    ...
        Sheets(Sheets.Count).Select
        ActiveSheet.Name = newName
    ...

  3. #3
    Registered User
    Join Date
    09-06-2022
    Location
    Phoenix, Arizona
    MS-Off Ver
    365
    Posts
    3

    Re: Set Cell to Copy hidden Worksheet to End & Rename with input box

    This worked well! Thank you

  4. #4
    Valued Forum Contributor
    Join Date
    08-08-2022
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2019
    Posts
    1,777

    Re: Set Cell to Copy hidden Worksheet to End & Rename with input box

    Hello. Another way:

    Sub Group14_Click()
    Dim myNum As Integer, x As Integer, newName As String, ws As Worksheet
    
    myNum = Worksheets("Info").Cells(13, 7)
    Sheets("Recipe Template").Visible = True
    For x = 1 To myNum
      Sheets("Recipe Template").Copy After:=Sheets(Sheets.Count)
      If x = 1 Then Set ws = ActiveSheet
    Next x
    Sheets("Recipe Template").Visible = False
     
    newName = InputBox("Rename your Recipe, if you created more than 1 template please rename the remaining manually.", "Theoretical & True Food Cost", "Have a Great day!")
    If newName <> "" Then ws.Name = newName
    End Sub

  5. #5
    Registered User
    Join Date
    09-06-2022
    Location
    Phoenix, Arizona
    MS-Off Ver
    365
    Posts
    3

    Re: Set Cell to Copy hidden Worksheet to End & Rename with input box

    However i decided to utilize this as it helps me learn a bit more about VBA. Thank you very much!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro to copy a hidden worksheet to an existing workbook then rename.
    By namy77 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-23-2017, 12:43 PM
  2. Copy Worksheet and rename Sheet to cell value
    By ECEUK in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-05-2015, 03:10 PM
  3. Copy Master Worksheet and Rename it Using Cell Value from another Worksheet
    By iseman22 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-23-2013, 07:49 AM
  4. Copy worksheet and rename based on cell range
    By jpruffle in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-08-2011, 06:53 AM
  5. copy worksheet that is hidden and rename it using a userfom
    By iscar_marius in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-17-2009, 06:03 AM
  6. [SOLVED] How can I copy sheet, fill in cell from list and rename worksheet?
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-31-2006, 02:50 AM
  7. Copy worksheet, rename, merged cell problem??
    By Simon Lloyd in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-16-2006, 08:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1