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
"jonno" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
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
On Dec 28, 11:18 am, "Nigel" <nigel...@suxnospampanet.com> wrote:
> 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
>
> <j...@jordon.co.uk> wrote in
> messagenews:(E-Mail Removed)...
> 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
|
Similar Threads
