copies blank cell. screws up layout of rows

[hopr37]

2 worksheets
worksheet 1 is for entering data
worksheet 2 makes a copy of certain rows of data from worksheet 1 ( depending if a value was added to a certain row from worksheet 1)

worksheet 1:

Value 1	Value 1	Value 1
Blank	Value 2	Value 2
Value 3	Value 3	Value 3

<tbody>
</tbody>

So....
Whenever I change a value in the center column, it copies the values of that entire row.
Change value 1 in center column copies first, center and last row to different worksheet. That works great.
Change value 3 in center column copies first,center and last row to a different worksheet just underneath the previous column...that works great.
Now... If I were to change the value in the center column of value 2 AND the first column is blank, it still copies over to a different worksheet. However,
since there is no data in the first row it leaves it blank. The problem is, if I were to change another value in a different center column it moves the entire first row up while leaving the other rows in place thus causing everything to be off . ( Essentially, it would move value 3 up to the blank spot)

Any idea how I can avoid this?

 

[mumps]

Can you post the code you are using?

 

[hopr37]

when I change a value in column "D" it copies 3 column ( C, D and F) to a new worksheet.
Column C has a description
Column D has a value that changes when I enter a number
Column F has a value that changes depending on what value I put in column D ( it basically just subtracts the numbers )
the problem is that when/if column C is blank, it does indeed copy all 3 cells but then if I make ANOTHER change to column D it pushes column D and F one row.


On Error Resume Next
Application.ScreenUpdating = False


If Not Intersect(Target, Range("D:D")) Is Nothing Then


Dim r1, r2, r3 As Integer

r1 = Application.WorksheetFunction.CountA(Sheets("RunningTotal").Range("C:C")) 'copper column goes to running total sheet,column C
r2 = Application.WorksheetFunction.CountA(Sheets("RunningTotal").Range("D:D")) ' used footage column goes to running total sheet,column D
r3 = Application.WorksheetFunction.CountA(Sheets("RunningTotal").Range("E:E")) ' date entered column goes to running total sheet, column E


Sheets("RunningTotal").Range("C5").Offset(r1).Value = Sheets("Copper").Range("A" & Target.Row).Value 'copies column A from "Copper" to C4 of "Running Total"
Sheets("RunningTotal").Range("D5").Offset(r2).Value = Sheets("Copper").Range("D" & Target.Row).Value 'copies column D from "copper" to D4 of "Running Total"
Sheets("RunningTotal").Range("E5").Offset(r3).Value = Sheets("Copper").Range("F" & Target.Row).Value ' copies column F from "copper to column E4 of "Running Total"

 

[Fluff]

Try

   Dim Nxtrw As Long
   Application.ScreenUpdating = False

   If Not Intersect(Target, Range("D:D")) Is Nothing Then
      Nxtrw = Sheets("RunningTotal").Range("C:E").Find("*", , xlValues, , xlRows, xlPrevious, , , False).Row
      
      Sheets("RunningTotal").Range("C" & Nxtrw).Value = Sheets("Copper").Range("A" & Target.Row).Value 'copies column A from "Copper" to C4 of "Running Total"
      Sheets("RunningTotal").Range("D" & Nxtrw).Value = Sheets("Copper").Range("D" & Target.Row).Value 'copies column D from "copper" to D4 of "Running Total"
      Sheets("RunningTotal").Range("E" & Nxtrw).Value = Sheets("Copper").Range("F" & Target.Row).Value ' copies column F from "copper to column E4 of "Running Total"
   End If

 

[hopr37]

hold on. I may have spoken too soon. Give me a few minutes to verify.

 

[hopr37]

Hmmm. doesn't seem to do anything. Might be doing something wrong?

 

[Fluff]

In future please do not use the report button to say thanks. It is there to report problem posts to the moderators.
If you add this

Nxtrw = Sheets("RunningTotal").Range("C:E").Find("*", , xlValues, , xlRows, xlPrevious, , , False).Row
MsgBox Nxtrw

What does the message box say

 

[hopr37]

5553

 

[Fluff]

In that case if you have a look at row 5553 you should see your data.

 

[hopr37]

yup. it's there. so why did it start there and how to fix so it starts at C5?