'How to copy column from last column N number of times?

I currently have the following code, which was made by a lovely member here

Sub YearsNumberReduction()
Dim LastCol As Long
Dim DelCnt As Integer
DelCnt = Sheets("Panel").Range("E19").Value
With ThisWorkbook.Sheets("Current")
    LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    If LastCol >= DelCnt Then
        .Range(.Cells(1, LastCol - DelCnt + 1), .Cells(1, LastCol)).EntireColumn.Delete
    End If
End With

This code takes the last column, and deletes the column by the number of times specified in E19. How can I change the code, so instead of deleting a specified number of times from the last row, we can add columns instead? Essentially reversing the role of that VBA Macro.

I also have another question. In my Excel solution, I constantly add the number of years and decrease them (those years are business years and each year carries a profit, calculated by revenue-costs). On the main worksheet, I have 10 years. That is the default number of years. However, this does not change when I add or decrease years based on the function I mentioned. The profit for the 10 years is calculated by =SUM(B29:B39). How can I make the range change (e.g if I add one more year, Year 11 will be added and =SUM(B29:40) in the main spreadsheet which gives a profit overview?

Current adding of columns

Worksheets("Sheet1").Range("L1:L15").AutoFill Destination:=Worksheets("Sheet1").Range("L1").Resize(15, Worksheets("Panel").Range("E17") + 1), Type:=xlFillDefault

However, this adds from a fixed column, rather than the last. So I extend columns by 5, it will add 5, but if I extend it by 5 again, it wont extend since it has a fixed point. So I need it to change based on the last column.

EDIT

Year 1  Year 2  Year 3  Year 3  Year 3
Sales   100 115 132 132 152
Costs   30  32  33  33  35
Profit  70  84  99  99  99

Year 3 stays the same, and costs do not update (=C3*1.05) it should rise by 5%

enter image description here

enter image description here

enter image description here



Solution 1:[1]

Try the following. Assumes you can use column A to find the last used row:

Sub YearsNumberReduction()

    Dim LastCol As Long
    Dim DelCnt As Integer
    DelCnt = Sheets("Panel").Range("E19").Value
    Dim copyCol As Range
    Dim DestRange As Range
    Dim lastRow As Long

    With ThisWorkbook.Sheets("Current")

        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

        Set copyCol = .Range(.Cells(1, LastCol), .Cells(lastRow, LastCol))

        Set DestRange = .Range(.Cells(1, LastCol), .Cells(lastRow, LastCol + DelCnt))

        copyCol.AutoFill Destination:= DestRange, Type:=xlFillDefault

    End With


End Sub

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 QHarr