1

It adds a new column from a template of another page so formulas and formatting are copied in. It adds in 1 column before the last column. Last column is being used an an anchor. I would like it to select and goto the myCol & "5" but i am unable to get it to work.

Function GetColumnLetter(colNum As Long) As String
    Dim vArr
    vArr = Split(Cells(1, colNum).Address(True, False), "$")
    GetColumnLetter = vArr(0)
End Function

Sub NewPlate_F() 'Insert Column Button
    Application.ScreenUpdating = False

    Dim sht8 As Worksheet
    Set sht8 = ThisWorkbook.Worksheets("Add")
    Dim sht2 As Worksheet
    Set sht2 = ThisWorkbook.Worksheets("Foundation Plates")

    Call Unprotect
    sht2.Activate

    Dim lastCol As Long
    Dim myCol As String
    Dim rng As Range
    Dim cell As Range

    With sht2
        Set rng = Cells

    lastCol = sht2.Cells(5, sht2.Columns.Count).End(xlToLeft).Column
    myCol = GetColumnLetter(lastCol)

    Set rng = sht2.Range(myCol & "5")

    'MsgBox rng.Address

        With sht2
        Columns(myCol).EntireColumn.Insert
        Columns(myCol).ColumnWidth = 13
            Application.Wait (Now + 0.000005)
        sht8.Range("H7:H48").Copy Range(myCol & "1")
        Range(myCol & "5").Select
        End With
    End With


    Call Format_Foundation
    Call Unprotect

    With sht2
        Range(myCol & "5").Select
    End With
    sht2.Activate

    Set sht2 = Nothing
    Set sht8 = Nothing
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub
Matt Taylor
  • 521
  • 1
  • 11
  • 26

1 Answers1

2

Try it as,

With sht2
    .Activate
    .Range(myCol & "5").Select
    '... or,
    .Cells(5, lastCol).Select
End With

Note my use of a prefixing . with .Cells(...) and .Range(...). When you are inside a With ... End With, the prefix period (e.g. .) passes the parent worksheet reference to the .Range or .Cells property.

It would be worthwhile to read How to avoid using Select in Excel VBA macros.

Community
  • 1
  • 1