copy row dependant on certain cell

[vac]

Hi,
Im trying to copy rows from a parts list to an invoice sheet only when there is a number in the quantity column, ie
part price quantity
nail £1 1
gun £23
glue £2 2


In the above example I would like only the rows with figures in the quantity column to be copied across to another sheet, result should be
part price quantity
nail £1 1
glue £2 2

Many Thanks

 

[AgentSilverfern]

Hi Vac,
Why don't you try this.
When u have the sheet with your parts list visible select any cell in the top row of your spreadsheet i.e. one of the titles like part or price, then go to the Data menu, select Filter and then Auto Filter. A series of drop down arrow buttons should then appear next to the names of all your headers.
Click on the button next to the quantity header.
A drop down list should appear, near the bottom of which should appear the option (non blanks).
Select this option and all those parts with no quantity should disappear from your list. They are still there, just temporarily filtered out.
You can now copy the filtered list and paste it into another sheet. It will not paste the filtered out information.
When you are done you can return to your list click on the drop down button again and select (none) to restore your parts list to its former state.
Hope this helps,
Agent Silverfern

 

[vac]

Thanks for the reply, but I was hoping to be able to do this with a macro.
. I used to have a piece of code for this sort of thing from a previous thread , but unfortunately have lost the link...Doh
Any other suggestions

 

[north19701]

Sub Macro1()
Range("c1:c" & Range("c65536").End(xlUp).Row).SpecialCells(xlCellTypeConstants).EntireRow.Copy _
Destination:=Sheets("Sheet2").Range("a1")
End Sub

this should get you started. are the values in column C based on a formula, though? If so, the code will have to change.

 

[AgentSilverfern]

Vac,

In actual fact if you don't mind having the filters on your spreadsheet all the time. You can record a macro of all the steps I gave you subsequent to turning the filters on. This will work whether or not your values are based on formulas.

Agent Silverfern

 

[vac]

Thanks guys