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?

 

[Excel1991]

Yes, they are all different

 

[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("sheet2").Range("B:B").find(Target.Offset(, 1).Value, , , xlWhole, , , False, , False)
   If Fnd Is Nothing Then
      Lr = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).Row
      If Lr < 10 Then Lr = 10
      Sheets("sheet2").Range("A" & Lr).Resize(, 7).Value = Target.Resize(, 7).Value
   ElseIf Target.Value = "" Then
      Fnd.EntireRow.Delete
   Else
      Fnd.Offset(, -1).Value = Target.Value
   End If
End Sub

 

[Excel1991]

Thank again for the help. The Only issue I have come across is that if I try to delete more than one item at once in my quality colum in sheet1 (for instance highlight 5 cells and delete), the corresponding values on the sheet2 are not erased. If I then type in a new quantity, the lines lines carry over to sheet2 under the cells that should have been erased. (The cells that should have been erased when I deleted all the quantity values simultaneously essentially become “hard coded”) this isn’t a big issue, as I have found that if I just delete the values one at a time this will not occur.


I was curious if there was a way to define a range on sheet2. I want to be able to put a Sum colum at the end of the rows on sheet two. I was thinking that 30 rows on sheet2 should be enough, as I don’t expect more than 30 quantities to be entered in. Is there a way to define the code so that it is only brings over values to sheet2 between the rows A10 (where our code already starts) but limits it to row 39

 

[Fluff]

Try

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("sheet2").Range("B:B").find(Target.Offset(, 1).Value, , , xlWhole, , , False, , False)
   If Fnd Is Nothing Then
      Lr = Sheets("sheet2").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("sheet2").Range("A" & Lr).Resize(, 7).Value = Target.Resize(, 7).Value
   ElseIf Target.Value = "" Then
      Fnd.EntireRow.Delete
   Else
      Fnd.Offset(, -1).Value = Target.Value
   End If
End Sub

 

[Excel1991]

Thank you again. One last request, would you be able to explain to me how each line of the code works??

 

[Excel1991]

I have run into one another issue. On sheet2 I would like to sum up the columns that I am bringing over. Right now the code brings information over and fills rows 10 through 40 on Sheet2, exactly like its supposed to. However if, in row 41, I attempt to put a Sum formula, it messes up the code.

After I apply my sum formula (row 41 sheet 2), and go back to Sheet 1 and add a new quantity, I automatically get the "Too many Lines" dialogue box. I believe code is trying to fill the rows below the formula (on sheet 2), even though there are plenty of empty rows above it. Any reason this could be happening?

 

[Excel1991]

Just for some additional information, I had previously written "total" in column A of Sheet2, at Row 41. I think the code was counting that as the last row and attempting to put the formula after it. Due to this I moved the word "total" to column C so the code wouldn't read it when looking for the last row. This works for the most part. However, when I go to delete the quantities on sheet1, it starts deletes the rows on sheet 2 as well. So my row 41 that had the word "total" gets moved up to row 40, them 39, then 38, etc...

Basally I am trying to make a code that will allow me to bring over data from rows 10-40 (Sheet2). and have a fixed Total row at row 41. I would like it so that as I delete the corresponding rows on Sheet 1, the "totals" row (row 41)on sheet 2 stays fixed

 

[Fluff]

Try

   ElseIf Target.Value = "" Then
      Fnd.EntireRow.Delete
     [COLOR=#ff0000] Sheets("sheet2").Rows(40).Insert[/COLOR]
   Else
      Fnd.Offset(, -1).Value = Target.Value

 

[Excel1991]

Thank you, I believe that this solved my problem

 

[Fluff]

You're welcome