VBA to Move Data Based on Criteria

[Excel1991]

Hello,

I am a relatively modest Excel user who i just breaking into VBA. I was hoping some of the members of this forumn could help me with a project that I am working on.

Basically I have a master list of data (sheet one). On this list column A ("Quantity") is empty, and columns B through G have data for maufacturer, part type, price, etc.


I am essentiallty trying to write a code so that if someone were to put a value in Column A (Quanitity), it would generate on Sheet2.

Sheet2 is set up as an invoice, so it will have logos and other stuff at the top rows. Due to this, I do not want the data to start in cell A1. I would likely need to start it at lets say, cell A10.

I had written a code that seemed to work but it was rather buggy. I am having trouble getting the cells to generate at A10 on the second tab. Is there any way to define the range I want the cells to go into on Sheet2?

Additionally, I would like to know how easy it is to disrupt the code? For instance, if I have the data transferring to row A10 on sheet2, and someone later deletes, lets say Row2, would this mess up the VBA code/formula?

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Lr As Long
   Dim Fnd As Range
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column > 1 Then Exit Sub
   Set Fnd = Sheets("Proposal").Range("B:B").find(Target.Offset(, 1).Value, , , xlWhole, , , False, , False)
   If Fnd Is Nothing Then
      Lr = Sheets("Proposal").Range("A" & Rows.Count).End(xlUp).Offset(1).Row
      If Lr < 10 Then
         Lr = 10
      ElseIf Lr > 39 Then
         MsgBox "Too many lines"
         Exit Sub
      End If
      Sheets("Proposal").Range("A" & Lr).Resize(, 10).Value = Target.Resize(, 10).Value
   ElseIf Target.Value = "" Then
      Fnd.EntireRow.Delete
      Sheets("Proposal").Rows(39).Insert
      Sheets("Proposal").Range("C40").Formula = "=sum(A10:A39)"
   Else
      Fnd.Offset(, -1).Value = Target.Value
   End If
End Sub

 

[Excel1991]

That fixed it! I appreciate it

 

[Fluff]

You're welcome