Copying rows to another sheet when certain value equals

[jon]

Hi,

I'm completely new to VB and excel but I need a macro that does the
following, just to give you the background its a picking list that
lists our entire product range but to cut down on wasted paper and
picking errors we only want to display and print items that are
actually required by the customer:

(column letter and title shown)
A PART NUMBER
B DESCRIPTION
C SIZE
D PRICE EACH
E QTY REQUIRED
F QTY DELILVERED
G £

I need a button at the end of the document then can be clicked when the
user has finished inputting the "QTY REQUIRED" in row 'E' that will
scan the complete sheet for rows where the value in row 'E' is not
empty (<>""), i.e. where a QTY of the item is required... Then copy the
row A-G to sheet 2 (from sheet 1) - ready for printing and picking.

Any help would be greatly appreciated!

Cheers,

Jon

 

[Nigel]

You might consider using an autofilter placed in the header row, then use
the filter control (custom) in column E and select values greater than 0,
the filtered list can be printed directly as only visible rows will be
printed or copied to another sheet as required.

No VB involved but if you were to use the macro recorder as you apply the
above steps it could then be assigned to a control button if required, you
might also include the printing step in the macro to automate the whole
process, including resetting the filter and clearing values in column E for
the next order!

--
Cheers
Nigel



Hi,

I'm completely new to VB and excel but I need a macro that does the
following, just to give you the background its a picking list that
lists our entire product range but to cut down on wasted paper and
picking errors we only want to display and print items that are
actually required by the customer:

(column letter and title shown)
A PART NUMBER
B DESCRIPTION
C SIZE
D PRICE EACH
E QTY REQUIRED
F QTY DELILVERED
G £

I need a button at the end of the document then can be clicked when the
user has finished inputting the "QTY REQUIRED" in row 'E' that will
scan the complete sheet for rows where the value in row 'E' is not
empty (<>""), i.e. where a QTY of the item is required... Then copy the
row A-G to sheet 2 (from sheet 1) - ready for printing and picking.

Any help would be greatly appreciated!

Cheers,

Jon

 

[jonno]

Thanks Nigel,

I really need the rows copying to a separate sheet because 'Sheet2'
contains other information about the order, such as customer name,
order total, discount etc. etc. (for this reason the macro needs to
copy to the next blank row in sheet2

Any more ideas apprecaited....

Jon

 

[Nigel]

OK, here is some code that will copy the rows on sheet1 to sheet2 where the
value on sheet1 column E is > 0
The destination on sheet2 is determined by the last unused row.
Hopefully this gives you a template, you might need to change the copy
procedure if there are formula on sheet1, as these may fail on sheet2 if not
referencing just the copied range values.

Sub CopyOrder()
Dim wkList As Worksheet, wkOrder As Worksheet
Dim xalr As Long, xr As Long, xblr As Long, xc As Integer

' change the Sheets("SheetX") names below to those of your sheets
Set wkList = Sheets("Sheet1")
Set wkOrder = Sheets("Sheet2")

' get last row on order sheet (test each column)
With wkOrder
For xc = 1 To 256
If xblr < .Cells(Rows.Count, xc).End(xlUp).Row Then
xblr = .Cells(Rows.Count, xc).End(xlUp).Row
End If
Next
xblr = xblr + 1
End With

' copy order rows > 0 to order
With wkList
xalr = .Cells(Rows.Count, "A").End(xlUp).Row
For xr = 1 To xalr
If .Cells(xr, "E") > 0 Then
.Range(.Cells(xr, 1), .Cells(xr, 7)).Copy
Destination:=wkOrder.Cells(xblr, 1)
xblr = xblr + 1
End If
Next xr
End With
End Sub

--
Cheers
Nigel



Thanks Nigel,

I really need the rows copying to a separate sheet because 'Sheet2'
contains other information about the order, such as customer name,
order total, discount etc. etc. (for this reason the macro needs to
copy to the next blank row in sheet2

Any more ideas apprecaited....

Jon