BMRobin February 2016

Excel - Adding multiple columns after last column in chart

I'm trying to create a macro in Excel that will add three columns to the end of the chart, identical to the previous columns found in the chart with one of the columns increasing by one.

For example:

Column 1         Column 2   Column 3    Column 4         Column 5   Column 6
PGM Number 1     Amount     Notes       PGM Number 2     Amount     Notes

Is there a way to add three columns (PGM Number +1, Amount and Notes) at the end of the chart, so it can do the following?

Column 7      Column 8   Column 9       Column 10      Column 11   Column 12
PGM Number 3     Amount     Notes       PGM Number 4      Amount       Notes

This is the code I have to replicate the last column:

Sub InsertColumn()
Dim lastColumn As Long
lastColumn = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Column
Columns(lastColumn).Copy Destination:=Columns(lastColumn + 1)
End Sub

But obviously this is not enough.

Thank you for your help!

Answers


findwindow February 2016

Sub addCols()

Dim lastColumn As Long
Dim f As String
Dim s As String
Dim t As String
Dim c As Long

f = "PGM Number "
s = "Amount"
t = "Notes"

With ActiveSheet

    lastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
    c = (lastColumn + 3) / 3

    'assuming first row, otherwise change "1" to desired row
    .Cells(1, lastColumn + 1) = f & c
    .Cells(1, lastColumn + 2) = s
    .Cells(1, lastColumn + 3) = t

End With

End Sub

Post Status

Asked in February 2016
Viewed 2,056 times
Voted 5
Answered 1 times

Search




Leave an answer