excel - Copy and paste last used column into next available column

Question

I know this has probably already been answered but I have searched through the previous questions and cannot find an answer that works for me.

Basically I have an excel spreadsheet which can be updated daily/weekly/monthly depending on the workflow. What I need is a macro that finds the last 'used' column(Headers are in row 5), inserts a blank column directly to the right of that - (we have a totals table at the end that needs to move along) & copies the entire last used columns data into that newly created column.

It's probably quite a simple code but I've only just started using VBA and hope someone can help!! I'm hoping once I've started doing some bits and pieces I can build up my knowledge!

Thanks in advance

Emma

Answer 1

From here: Copy last column with data on specified row to the next blank column and here: Excel VBA- Finding the last column with data

Sub Test()

    Dim ws As Worksheet

    Set ws = ActiveSheet
    Dim rLastCell As Range
    Dim LastCol As Integer

    Set rLastCell = ws.Cells.Find(What:="*", After:=ws.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)

    LastCol = rLastCell.Column

    ws.Columns(LastCol).Copy ws.Columns(LastCol + 1)

End Sub