Copy a range in column using Excel VBA

Question

I am trying to add a button, that adds a new column after the last column with values. This works.

Now I want to copy values to the new column. Values shall be copied from the last column from row 32 to the last one with a value in column A. Right now Ihave a code for copying the whole column. How do I concentrate on the specific range?

Sub AddMeeting()
Dim ws As Worksheet
Dim lastcol As Long
 Set ws = ActiveSheet

lastcol = ws.Cells(32, ws.Columns.Count).End(xlToLeft).Column
Columns(lastcol).Copy Destination:=Columns(lastcol + 1)
Range ((Cells.Columns.Count.End(xlLeft)) & Range(32)), (lastcol + 1) & Cells.Rows.Count.End(xlUp)



 Application.CutCopyMode = False
End Sub

Answer 1

Values shall be copied from the last column from row 32 to the last one with a value in column A

Is this what you are trying?

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long, lCol As Long
    Dim LastColumn As String
    Dim rngToCopy As Range

    '~~> Set this to the relevant worksheet
    Set ws = Sheet1

    With ws
        '~~> Find last row in Col A
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        '~~> Find last column in row 32
        lCol = .Cells(32, .Columns.Count).End(xlToLeft).Column
        '~~> Get Column Name from column number
        ' https://stackoverflow.com/questions/10106465/excel-column-number-from-column-name
        LastColumn = Split(Cells(, lCol).Address, "$")(1)

        Set rngToCopy = .Range("A32:" & LastColumn & lRow)

        Debug.Print rngToCopy.Address

        With rngToCopy
            '
            '   Do what you want here
            '
        End With
    End With
End Sub