Need a code that looks at the results of a formula in column A and copies row

I'm working on a sheet with a formula in column A that will either return "X", or "". If that value is "X" I need the formulas that I have in row 2 (in columns B through Z) for the entire row to be copied to that row (rows 3 through 5000).

I've been working with this code, but it isn't working:

' Select cells to paste
Sheets("SAP Detail").Select
Range("A2:AI2").Select
Selection.Copy

' Remove rows with a 0 (zero) in column A from the SAP Detail page
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
With ActiveSheet
.Select
'Set the first and last row to loop through
Firstrow = .UsedRange.Cells(2).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
'We loop from Lastrow to Firstrow (bottom to top)
For Lrow = Lastrow To Firstrow Step -1
'We check the values in column A for "X"
With .Cells(Lrow, "X")
If Not IsError(.Value) Then
If .Value = "X" Then ActiveSheet.Paste
End If
End With
Next Lrow

End With

Can anyone help me identify what I'm missing? I'm getting really burned out on this one. It's a huge macro and this is the one piece that is failing. I think I need someone else's eyes on it.

Thanks to anyone that can assist. I've found a ton of valuable threads here, but this is my first post.

Another option would be to count the number of used rows on my other worksheet ("SAP Dump" - data begins in row 6 and will not exceed 5000 rows) and paste row 2 that same number of times in this worksheet ("SAP Detail").

Originally Posted by Domski

So you've got some data coming out of SAP which somehow arrives on your SAP Detail sheet. You've then got some formula in B2:Z2 which I presume are just there so that you can copy them. Right so far?

The "SAP Dump" tab is just as it sounds, a dump of SAP data that begins in Row 6 (cell A6). I am pulling this data into the "SAP Detail" tab via a series of if statements that clean up the data and make it usable. These formulas are found in "SAP Detail" row 2 (cells B2 through Z2).

Originally Posted by Domski

Does the data always start in the same row and is there a column that can be checked that always has data in it we can use to work out the last row? Maybe column A?

The data on the "Dump" tab starts in row 6.
Column A is the only column with a constant formula right now. It is the formula that produces either "X" or "" (nothing). If the result of the formula in column A is "X", I need the formulas from cells B2 through Z2 to be copied down.
So if A3 = "X", I need the macro to paste the formulas from cells A2 through Z2 down to A3 through Z3.

Pure genius. This is beautiful. I couldn't get out of the approach that I was using, this makes perfect sense.

Oh, and it works too!

Originally Posted by Domski

I should have pointed out before but please always use code tags when posting code to the forum per the Forum Rules.

Dom

I will do so in the future, I missed that part in the rules, I've seen people comment on that in threads before but never really got it.