Hi,
Someone please help me see the wood for the trees.
User entry sheet where new data is added on a new row each time. Each row has formulas working from the user-entered values. Whole area is defined in a dynamic named range.
Macro should identify the columns needing formulas in (via an entry in row 1), copy the formula in the first row of data and copy it down for as many rows as there are in the dynamic named range.
Run time error 1004 / Method 'Range' of object '_Worksheet' failed occurs on the "copy" line.
Please put me out of my misery...
THANKS
Code
Sub copyformulas()
Dim RowFrom, RowTo, CurrentCol, FirstCol, MaxCol As Integer
Dim Ws As Worksheet
RowFrom = Range("DataInputArea").Cells(1, 1).Row + 1
RowTo = Range("DataInputArea").Rows.Count + RowFrom - 2
FirstCol = Range("DataInputArea").Cells(1, 1).Column
'MaxCol = Range("DataInputArea").Columns.Count - FirstCol + 1
Set Ws = Sheets("Data input")
For Each c In Ws.Range("1:1")
Select Case c.Value
Case "copy formulas"
CurrentCol = c.Column
Ws.Range(Cells(RowFrom, CurrentCol)).Copy _
Destination:=Ws.Range(Cells(RowFrom + 1, CurrentCol), Cells(RowTo, CurrentCol))
Case "end of table"
Exit Sub
End Select
Next c
End Sub
Display More