Copying Cells or Formulas in a Range..

[sloman2001]

I have been trying to learn Excel VBA on my own for a while. The boards are invaluable, but I have come across a problem I can not solve and I need help. The problem is easy to solve but the solution creates a different problem that I am trying to get around, hence my question. In VBA, all I am trying to do is copy one row to another row. I can do this easy enough with the range.copy command. But when I do, my conditional formatting creates an unintended separate entry for just that copied row. This ends up over time, having hundreds of conditional formatting entries, and they are impossible to manage. My conditional formatting "Applies To" (as an example) cells "$R$18:$R$1000", and after the range.copy, it changes the "Applies To" cells to ("$R$18:$R$200","$R$202:$R$1000"), and then it creates a new entry for line 201 (an example), and I now end up with 2 conditional formatting lines. Over time, I have hundreds of these. So to get around the problem, I want to create a loop, that just copies cell by cell from one row to another ( as this avoids the problem in my testing), but it must test whether the cell has a formula first. I seem to lose the formula if I just copy "values". Sorry, but I just can't come up with this solution. This macro is for my personal use, so I am not overly concerned with performance. Here is my code;

Private Sub copyrows(from As Long, toRow As Integer)

Dim cell As Range
Dim rng As Range
Set rng = Range("A" & from & ":BM" & from) ' I am OK with the hard coding of the columns range here
For Each cell In rng
If cell.HasFormula = True Then

I want to copy the cells FORMULA to the "toRow" cell FORMULA

Else

I want to copy the cells VALUE to the "toRow" cell VALUE

End If
Next cell

Exit Sub

Thank you so much in advance. Tom

 

[Fluff]

How about something like

   Rows(8).Insert
   Rows(7).Resize(2).FillDown