Create Names function for Columns RRS feed

  • Question

  • Hello,

    Can somebody tell me how I can create column names efficiently? I get an error in this routine, when I am trying to create names for columns at the very last line of the code. Any suggestions? 

    Sub GenerateIOTypes()
        Dim ws, sh, sh1, sh2, sh3, sh4 As Worksheet
        Dim wrkb As Workbook
        Dim IOType As Integer
        Dim LR1, LR2, LR3, LR4 As Long
        Dim directory As String
        Dim fileName As String
    '    Dim Sheet As Worksheet
        Application.ScreenUpdating = False
        directory = "C:\GenerateSetupFiles\"
        fileName = Dir(directory & "*.xl??")
        Workbooks.Open (directory & fileName)
        Set wrkb = ActiveWorkbook
        wrkb.Sheets.Add After:=Sheets(1), Count:=4
        Set sh = wrkb.Sheets("IOList")
        Set sh1 = wrkb.Sheets("Sheet1")
            With sh1
                .Name = "IOType1"
                .Range("A1").FormulaR1C1 = "IO Position"
                .Range("B1").FormulaR1C1 = "IO Address"
                .Range("C1").FormulaR1C1 = "IO Description"
                .Range("D1").FormulaR1C1 = "Master IO Description"
                .Rows(1).CreateNames Top:=True, Left:=False, Bottom:=False, Right:=False 'This line gives an error
            End With

    Thursday, November 5, 2015 9:31 AM

All replies

  • Use this instead - you need to include the entire range with method:

               .Range("A1").CurrentRegion.CreateNames Top:=True, Left:=False, Bottom:=False, Right:=False 'This line works

    Thursday, November 5, 2015 1:54 PM