VBA Macro Improvement

[heffo500]

Hi

I have this macro I wrote which drags down formulas to the last row the formulas are contained in a certain row,

Sub CopyFormula()


Dim LastRow As Long
Dim Row As Long
Dim Ans As Long
Dim ColNo As Long


LastRow = Sheet2.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row


Ans = MsgBox("Continue?", vbYesNo + vbQuestion, "Tell Me")
If Ans = vbNo Then Exit Sub

ColNo = 2
Sheet2.Cells(1, ColNo).Select
Selection.Copy
Range(Cells(2, ColNo), Cells(LastRow, ColNo)).Select
ActiveSheet.Paste

ColNo = 3
Sheet2.Cells(1, ColNo).Select
Selection.Copy
Range(Cells(2, ColNo), Cells(LastRow, ColNo)).Select
ActiveSheet.Paste

End Sub

However I want to condense the part below to that I can state drag down columns D:E so to speak:


ColNo = 2
Sheet2.Cells(1, ColNo).Select
Selection.Copy
Range(Cells(2, ColNo), Cells(LastRow, ColNo)).Select
ActiveSheet.Paste

ColNo = 3
Sheet2.Cells(1, ColNo).Select
Selection.Copy
Range(Cells(2, ColNo), Cells(LastRow, ColNo)).Select
ActiveSheet.Paste

So it looks like this:
ColNo = 2:3
Sheet2.Cells(1, ColNo).Select
Selection.Copy
Range(Cells(2, ColNo), Cells(LastRow, ColNo)).Select
ActiveSheet.Paste

However when Ive tried to do this I get errors.

Thanks

 

[MARK858]

So it looks like this:
ColNo = 2:3

One way...

Sub CopyFormula()


    Dim LastRow As Long
    Dim Row As Long
    Dim Ans As Long
    Dim ColNo As Long


    LastRow = Sheet2.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row


    Ans = MsgBox("Continue?", vbYesNo + vbQuestion, "Tell Me")
    If Ans = vbNo Then Exit Sub

    ColNo = 2
    With Sheet2
        .Cells(1, ColNo).Resize(, 2).Copy .Range(.Cells(2, ColNo), .Cells(LastRow, ColNo)).Resize(, 2)
    End With
End Sub

 

[heffo500]

One way...

Sub CopyFormula()


    Dim LastRow As Long
    Dim Row As Long
    Dim Ans As Long
    Dim ColNo As Long


    LastRow = Sheet2.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row


    Ans = MsgBox("Continue?", vbYesNo + vbQuestion, "Tell Me")
    If Ans = vbNo Then Exit Sub

    ColNo = 2
    With Sheet2
        .Cells(1, ColNo).Resize(, 2).Copy .Range(.Cells(2, ColNo), .Cells(LastRow, ColNo)).Resize(, 2)
    End With
End Sub

So how would I extend this to the third column?

 

[MARK858]

Did you try it? It already copies columns 2 & 3.

 

[heffo500]

Apologies It does work. I was playing with it to try copy columns 2-4 but to no avail.

 

[MARK858]

Change the 2 places where it says Resize(, 2) to Resize(, 3)

 

[heffo500]

Change the 2 places where it says Resize(, 2) to Resize(, 3)

Thanks very much really appreciate it.