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?

 

[My Aswer Is This]

So if you enter any value in column A of Sheet(1) you want this entire row copied to Sheet(2)
Is that what you want?
And the first time we do this you want the row copied to Row (10) of Sheet(2)
Next time copy to row(11)
And you want this to happen automatically when you enter some value in column A of Sheet(1)

Now you should be aware any time you copy data from one sheet to another sheet the formulas will take on a different result depending on the formula and how it is written.

So do you want the formulas and formatting copied or just the results of the formulas.

 

[Excel1991]

All the numbers on the master list a hard coded so the formulas should not be an issue.

It sounds like you are understanding all of the assumptions correctly. An input to column a on sheet1 would trigger the code to pull that whole row sheet2. Basically the criteria for moving the row is any value greater than 0. Since column A is title “quantity”, someone could put a 1 in one cell and 1,000 in another. I would like an value that is put into this box to trigger the action.

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Lr As Long
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column > 1 Then Exit Sub
   Lr = Sheets("[COLOR=#ff0000]sheet2[/COLOR]").Range("A" & Rows.Count).End(xlUp).Offset(1).Row
   If Lr < 10 Then Lr = 10
   Sheets("[COLOR=#ff0000]sheet2[/COLOR]").Range("A" & Lr).Resize(, 7).Value = Target.Resize(, 7).Value
End Sub

This needs to go in the sheet module for your "Master List".
Change sheet name in red to suit.

 

[Excel1991]

Sub Sonitrol()
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Lr As Long
If Target.CountLarge > 1 Then Exit Sub
If Target.Column > 1 Then Exit Sub
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
End Sub




When I post thhe code in the module, I get an error "Expected End Sub" and it highlights the portion of the code that I have just put in BOLD. This code should be entered into Module1 rather than Sheet1, correct?

 

[My Aswer Is This]

Fluffs script should be installed like this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Lr As Long
If Target.CountLarge > 1 Then Exit Sub
If Target.Column > 1 Then Exit Sub
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
End Sub

 

[Excel1991]

Thanks for the responses! I just pasted it and it seems to be working as needed so thank you very much!

If anyone were to delete a row on sheet 2 now, would that mess up the code?

 

[Fluff]

No, you can delete rows on sheet2 without any problems.

 

[Excel1991]

Is there a way to edit the code so that if, I delete a quantity, it removes that row from my Sheet2?

Currently, if I put a value in it carries he row over perfectly. However if I edit or delete that value in my quantity column, it does not remove/edit the row accordingly.

Is it possible to do this?

 

[Fluff]

Are the values in col B unique?