+ Reply to Thread
Results 1 to 16 of 16

Copying sheet with relative formula named range causes errors.

Hybrid View

  1. #1
    Valued Forum Contributor
    Join Date
    02-26-2010
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003/2007/2010/2016
    Posts
    432

    Question Copying sheet with relative formula named range causes errors.

    Hello!

    I use this copy sheet code and it works great. Occasionally, though, I get an error popping up: "A formula or sheet you want to move or copy contains the name 'ErrorCheck', which already exists on the destination sheet. Do you want to use this version of the name?"

    If I click Yes, then the named range ErrorCheck is used, but it is the sheet ErrorCheck rather than the workbook-wide ErrorCheck.

    Anyone have any ideas how to eliminate this error?

    Private Sub cmbCopySheets_Click()
        Dim c01 As String
        Dim ws As Worksheet
        Dim Ans As VbMsgBoxResult
        On Error GoTo Errhandler
    NAME_AGAIN:
        c01 = InputBox("Please enter new sheet name.")
        If c01 = "" Then Exit Sub
        If SheetExists(c01) Then
                MsgBox "Sheet " & c01 & " already exists.", vbExclamation, "Sheet Error"
                GoTo NAME_AGAIN
        ElseIf Not IsValidSheetName(c01) Then
                MsgBox "Sheet name " & c01 & " is invalid (is blank, too long, or contains reserved characters (/ \ : | ? *).", vbExclamation, "Sheet Error"
                GoTo NAME_AGAIN
        ElseIf Err.Number <> 0 Then
                MsgBox c01 & " has a Sheet Error: " & Err.Description, vbExclamation, "Sheet Error"
                Exit Sub
        End If
            Application.ScreenUpdating = False
            ActiveWorkbook.Unprotect "SECRET"
            ActiveSheet.Copy , ActiveSheet
            With ActiveSheet
                .name = c01
                '.Unprotect "SECRET"
                '.OLEObjects("cmbCopySheets").Visible = msoFalse
                '.Protect "SECRET"
            End With
            ActiveWorkbook.Protect "SECRET"
            Application.ScreenUpdating = True
    Exit Sub
    Errhandler:
    
          MsgBox Err.Description & vbCrLf & vbCrLf & "Not executing Copy Sheet request."
          Err.Clear
    End Sub
    
    Function SheetExists(SHname As String, _
                         Optional wkb As Workbook = Nothing) As Boolean
        On Error Resume Next
        SheetExists = Not IIf(wkb Is Nothing, ActiveWorkbook, wkb).Sheets(SHname) Is Nothing
        If Err.Number Then Err.Clear
    End Function
    
    Function IsValidSheetName(s As String) As Boolean
        If Len(s) = 0 Or Len(s) > 31 Then Exit Function
        If InStr(s, "\") Then Exit Function
        If InStr(s, "/") Then Exit Function
        If InStr(s, ":") Then Exit Function
        If InStr(s, "|") Then Exit Function
        If InStr(s, "*") Then Exit Function
        If InStr(s, "?") Then Exit Function
        IsValidSheetName = True
    End Function
    Named Ranges:
    ErrorCheck: =Cover!$E3=INDEX(Customers, MATCH(Cover!$E4, Plants, 0))
    
    MyPlants:=IF(TRIM(Cover!$E3)="",Message, INDEX(Plants, MATCH(Cover!$E3, Customers, 0)) : INDEX(Plants, MATCH(Cover!$E3 & "zzz", Customers)))
    Thanks!

    Lost

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Copying sheet with relative formula named range causes errors.

    Can you provide the workbook doing this? I'll take a look at it directly.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Valued Forum Contributor
    Join Date
    02-26-2010
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003/2007/2010/2016
    Posts
    432

    Re: Copying sheet with relative formula named range causes errors.

    Jerry,

    The error pops up sporadically, so hopefully you see it:

    1) Go to one of the green sheet tabs.
    2) Click the Copy Sheet button.
    3) Name the copied sheet something that starts with a number ("0050", etc.) (I don't know if using a number as the sheet name causes the error, but that is what we name our sheets. Maybe something else will make it pop up.)
    4) If all goes well, you'll see the error described above.
    5) Otherwise, the sheet will copy just fine with no error.

    Thanks for looking into this!

    Lost
    Attached Files Attached Files

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Copying sheet with relative formula named range causes errors.

    I'm not seeing the error, but naming sheets numbers has caused me unmeasured grief over the years. Many of my "stock" macros use this little trick just in case someone uses numbers instead of text strings for sheet names, it seems to suppress the problems I've encountered.

    Sheets(L1.Text).name = SHname & ""

  5. #5
    Valued Forum Contributor
    Join Date
    02-26-2010
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003/2007/2010/2016
    Posts
    432

    Re: Copying sheet with relative formula named range causes errors.

    Jerry,

    I tried to apply your code to my code, but I don't know what I am doing.

    This isn't right:

    ActiveSheet.Copy , ActiveSheet
            ActiveSheet(L1.Text).name = c01 & ""
    Can you help?

    Respectfully,

    Lost

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Copying sheet with relative formula named range causes errors.

    Oh heavens, I would never write code with variables that give no clue whatsoever as to what they are. c01 ?

    Sheets(L1.Text).name = SHname & ""
    ...works for me.

    Care to provide step by step instructions for duplicating your error, once you find a set of instructions that fail every time?

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Copying sheet with relative formula named range causes errors.

    Also, here's a simpler SHEETEXISTS function:
    Function SheetExists(SHname As String) As Boolean
        SheetExists = Evaluate("ISREF('" & SHname & "'!A1)")
    End Function

  8. #8
    Valued Forum Contributor
    Join Date
    02-26-2010
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003/2007/2010/2016
    Posts
    432

    Re: Copying sheet with relative formula named range causes errors.

    Jerry,

    I updated the SHEETEXISTS function with what you supplied, but I don't know where in this code I need to put your code from Post #4.

    Here is what I have, but it needs your #4 post code:

    Option Explicit
    Private Sub cmbCopySheets_Click()
        Dim c01 As String
        Dim ws As Worksheet
        Dim Ans As VbMsgBoxResult
        On Error GoTo Errhandler
    NAME_AGAIN:
        c01 = InputBox("Please enter new sheet name.")
        If c01 = "" Then Exit Sub
        If SheetExists(c01) Then
                MsgBox "Sheet " & c01 & " already exists.", vbExclamation, "Sheet Error"
                GoTo NAME_AGAIN
        ElseIf Not IsValidSheetName(c01) Then
                MsgBox "Sheet name " & c01 & " is invalid (is blank, too long, or contains reserved characters (/ \ : | ? *).", vbExclamation, "Sheet Error"
                GoTo NAME_AGAIN
        ElseIf Err.Number <> 0 Then
                MsgBox c01 & " has a Sheet Error: " & Err.Description, vbExclamation, "Sheet Error"
                Exit Sub
        End If
            Application.ScreenUpdating = False
            ActiveWorkbook.Unprotect "SECRET"
            ActiveSheet.Copy , ActiveSheet
            With ActiveSheet
                .name = c01
                End With
            ActiveWorkbook.Protect "SECRET"
            Application.ScreenUpdating = True
    Exit Sub
    Errhandler:
    
          MsgBox Err.Description & vbCrLf & vbCrLf & "Not executing Copy Sheet request."
          Err.Clear
    End Sub
    
    Function SheetExists(SHname As String) As Boolean
        SheetExists = Evaluate("ISREF('" & SHname & "'!A1)")
    End Function
    
    Function IsValidSheetName(s As String) As Boolean
        If Len(s) = 0 Or Len(s) > 31 Then Exit Function
        If InStr(s, "\") Then Exit Function
        If InStr(s, "/") Then Exit Function
        If InStr(s, ":") Then Exit Function
        If InStr(s, "|") Then Exit Function
        If InStr(s, "*") Then Exit Function
        If InStr(s, "?") Then Exit Function
        IsValidSheetName = True
    End Function

    ??

    Lost

  9. #9
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Copying sheet with relative formula named range causes errors.

    In your code, it would be:
            With ActiveSheet
                .name = c01 & ""
            End With
    ....but I don't see the point of all this. If you know that you're having problems caused by making sheetnames numeric only, why do you keep doing it? I stopped this very thing years ago for this very reason. Not worth the headaches, IMO.

    The real example is how your code seems to work for me and I never see the error, and you see it "occasionally". Until you can come up with a scenario that fails 100% of the time the same way, I'm not sure what else to offer.

  10. #10
    Valued Forum Contributor
    Join Date
    02-26-2010
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003/2007/2010/2016
    Posts
    432

    Re: Copying sheet with relative formula named range causes errors.

    Jerry,

    1. Go to the "PT (Master)" tab on the workbook attached to this post.
    2. Click the Copy Sheet button.
    3. Name that new sheet "a".
    4. Now go the "a" sheet.
    5. Click the Copy Sheet button.
    6. Name that new sheet "b"
    7. You should get the error.

    HTH!

    Lost
    Attached Files Attached Files

  11. #11
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Copying sheet with relative formula named range causes errors.

    Using the floating form, I created the "a" sheet which came up blank, but the form was still visible, so I created sheet "b" and "c" and "d". No errors.

  12. #12
    Valued Forum Contributor
    Join Date
    02-26-2010
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003/2007/2010/2016
    Posts
    432

    Re: Copying sheet with relative formula named range causes errors.

    Jerry,

    You made the copy from the copy, right?

    PT (Master) makes "a".

    "a" makes "b"

    And you didn't get the error?

    (This is running on Excel 2003.)


    Lost

  13. #13
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Copying sheet with relative formula named range causes errors.

    As far as I can tell. I clicked on the CREATE sheets, created "a", then created "b", "c" and "d" in that order with no other click in between.

  14. #14
    Valued Forum Contributor
    Join Date
    02-26-2010
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003/2007/2010/2016
    Posts
    432

    Re: Copying sheet with relative formula named range causes errors.

    Jerry,

    I tried it again with the file from post #10. When I make a copy of a copy, I get the error.

    (If I go to the PT Master and copy to sheet "a", and then go back to PT Master and copy to sheet "b", I don't get the error.

    If I go to PT Master and copy to sheet "a", and then go to sheet "a" (copy of a copy) and try to copy to sheet "b", I get the error.)

    ?

    Lost

  15. #15
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Copying sheet with relative formula named range causes errors.

    Add this line of code:
            Application.ScreenUpdating = False
            ActiveWorkbook.Unprotect "SECRET"
            Application.DisplayAlerts = False
            ActiveSheet.Copy , ActiveSheet

  16. #16
    Valued Forum Contributor
    Join Date
    02-26-2010
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003/2007/2010/2016
    Posts
    432

    Re: Copying sheet with relative formula named range causes errors.

    Sir,

    I made your change, but I think that the error is now not being displayed, but is still there. If you go to Insert>Name>Define for the copy-of-a-copy sheets (Sheet B) , to the right of the ErrorCheck and MyPlants name is the sheet it goes with (B). If you delete those, it jumps back to the "Workbook-level" ErrorCheck and MyPlants.

    Then, I have to go to each copy and manually delete those "sheet-specific" names.

    I added the deletion to the code, but that is reactive rather than proactive.

    Application.ScreenUpdating = False
    ActiveWorkbook.Unprotect "SECRET"
    Application.DisplayAlerts = False
    ActiveSheet.Copy , ActiveSheet
              With ActiveSheet
                .name = c01 & ""
               End With
    ActiveWorkbook.Names("ErrorCheck").Delete
    ActiveWorkbook.Names("MyPlants").Delete
    ActiveWorkbook.Protect "SECRET"
    Application.ScreenUpdating = True
    Any other ideas on how to fix this?

    Respectfully,

    Lost
    Last edited by leaning; 09-19-2011 at 08:45 AM. Reason: correct spelling

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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