CODEWITHSUNDEEP
vbaexcel
GregdeLima
GregdeLima

Reputation: 404

VBA Copy Formula to Last Row Then Next Column

I'm working with the code below, and where I'm having a hard time getting this around is the piece in brackets [CURRENT COLUMN]

I can't quite seem to get it to use the currently used column to paste the formula down.

The goal is to go from columns K to END then if there is a formula in row 2 of that column, copy that formula down and proceed to the next column.

Option Explicit
Sub recalcdash()
Dim oWkbk As Workbook
Dim oWkst As Worksheet
Dim oRng As Range
Dim LastCol As Long
Dim LastRow As Long
Dim StartCol As Integer
Dim StartRow As Long


StartCol = 11
Set oWkst = ActiveSheet


LastRow = oWkst.Range("A" & oWkst.Rows.Count).End(xlUp).Row
LastCol = oWkst.Cells(2, oWkst.Columns.Count).End(xlToLeft).Column

For Each oRng In Range(Cells(2, 11), Cells(2, LastCol))
        If oRng.HasFormula Then
            oRng.Copy
            Range(Cells(2, StartCol), Cells(LastRow, [CURRENT COLUMN])).PasteSpecial (xlPasteFormulas)
        End If
        Next oRng

End Sub

Upvotes: 0

Views: 1010

Answers (1)

D. O.
D. O.

Reputation: 626

Try to modify

Range(Cells(2, StartCol), Cells(LastRow, [CURRENT COLUMN])).PasteSpecial (xlPasteFormulas

to

Range(Cells(2, oRng.Column), Cells(LastRow, oRng.Column)).PasteSpecial (xlPasteFormulas)

Upvotes: 1

Related Questions