none
naming a range in vba

    Question

  • OK this should be pretty easy and I can't get it to work.  I have a range selected A1:b44 and I want to name the range to the sheet name which has a space (I know I need to remove it) plus info, i.e. JohnSmithInfo.  I want this to be a macro as I have to do this for about 55 sheets.

    This is what I have so far.

    Dim WS As Integer
        WS = Sheets.Count
       
        For ac = 7 To WS
            Sheets(ac).Activate
            Range("A1:b44").Activate

           ActiveWorkbook.Names.Add Name:=Sheet.name [I'm not sure how to remove the space], RefersTo:=currentregion

          
        Next ac

    currently I just have a temp name and it's not working.  What am I screwing up?

    Friday, March 25, 2011 5:27 PM

Answers

  • This works

    Dim WS As Integer
        WS = Sheets.Count
        Dim RepName As String
           
        For ac = 3 To WS
            Sheets(ac).Activate
            RepName = Replace(ActiveSheet.Name, " ", "")
            Range("A1:b44").Activate
            ActiveWorkbook.Names.Add Name:=RepName, RefersToR1C1:= _
            "='" + RepName + "Info'!R1C1:R44C2"

        Next ac

    But the value comes up as #REF!

    • Marked as answer by Bruce Song Monday, April 04, 2011 1:51 AM
    Friday, March 25, 2011 8:22 PM
  • Hi

    It fails for one definate and one probable reason.

    First the definate

    RepName = Replace(ActiveSheet.Name, " ", "") removes the spaces fromn the current sheet name, fine as you cannot have spaces in a range name.  But later your using it to define the sheetname part range to define as the range.  Since you've changed John Smith (sheet name) to JohnSmith it would only work if you had a sheet with the name JohnSmith. Use:

    RefersToR1C1:= _
            "='" + ActiveSheet.Name + "Info'!R1C1:R44C2

    Secondly in the above you add Info to the sheet name.  So again you probably don't have this sheet JohnSmithInfo either.

    This works:(but I don't know if you intentionally want to use a name+info sheet that you might have)

    Sub addnewname()


    Dim WS As Integer
        WS = Sheets.Count
        Dim RepName As String
          
        For ac = 3 To WS
            Sheets(ac).Activate
            RepName = Replace(ActiveSheet.Name, " ", "")
            Range("A1:b44").Activate
            ActiveWorkbook.Names.Add Name:=RepName, RefersToR1C1:= _
            "='" + ActiveSheet.Name + "'!R1C1:R44C2"

        Next ac

    End Sub


    G North MMI
    • Marked as answer by Bruce Song Monday, April 04, 2011 1:51 AM
    Friday, March 25, 2011 10:06 PM

All replies

  • How about:

     

     

    Sub NoSheetSpace()
    Dim WS As Integer
    WS = Sheets.Count
      
    For ac = 7 To WS
            Sheets(ac).Activate
            Range("A1:b44").Activate
            ActiveWorkbook.Names.Add Name:=Replace(ActiveSheet.Name, " ", ""), RefersTo:=CurrentRegion
    Next ac
    End Sub


    gsnu201103
    Friday, March 25, 2011 5:42 PM
  • Thanks but it doesn't work.  The macro runs without any errors but when I go to name manager the value comes up #NA and the refers to also shows #NA or invalid (I removed them).

    I tried this -

    Dim WS As Integer
        WS = Sheets.Count
       
        For ac = 3 To WS
            Sheets(ac).Activate
            RepName = ActiveSheet.Name
            Range("A1:b44").Activate
            ActiveWorkbook.Names.Add Name:=RepName, RefersTo:=CurrentRegion

        Next ac

    But of course the macro errored out on the Repname because there's a space in the sheetname.

     

    Friday, March 25, 2011 8:03 PM
  • This works

    Dim WS As Integer
        WS = Sheets.Count
        Dim RepName As String
           
        For ac = 3 To WS
            Sheets(ac).Activate
            RepName = Replace(ActiveSheet.Name, " ", "")
            Range("A1:b44").Activate
            ActiveWorkbook.Names.Add Name:=RepName, RefersToR1C1:= _
            "='" + RepName + "Info'!R1C1:R44C2"

        Next ac

    But the value comes up as #REF!

    • Marked as answer by Bruce Song Monday, April 04, 2011 1:51 AM
    Friday, March 25, 2011 8:22 PM
  • Hi

    It fails for one definate and one probable reason.

    First the definate

    RepName = Replace(ActiveSheet.Name, " ", "") removes the spaces fromn the current sheet name, fine as you cannot have spaces in a range name.  But later your using it to define the sheetname part range to define as the range.  Since you've changed John Smith (sheet name) to JohnSmith it would only work if you had a sheet with the name JohnSmith. Use:

    RefersToR1C1:= _
            "='" + ActiveSheet.Name + "Info'!R1C1:R44C2

    Secondly in the above you add Info to the sheet name.  So again you probably don't have this sheet JohnSmithInfo either.

    This works:(but I don't know if you intentionally want to use a name+info sheet that you might have)

    Sub addnewname()


    Dim WS As Integer
        WS = Sheets.Count
        Dim RepName As String
          
        For ac = 3 To WS
            Sheets(ac).Activate
            RepName = Replace(ActiveSheet.Name, " ", "")
            Range("A1:b44").Activate
            ActiveWorkbook.Names.Add Name:=RepName, RefersToR1C1:= _
            "='" + ActiveSheet.Name + "'!R1C1:R44C2"

        Next ac

    End Sub


    G North MMI
    • Marked as answer by Bruce Song Monday, April 04, 2011 1:51 AM
    Friday, March 25, 2011 10:06 PM