Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying rows to another sheet when certain value equals
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying rows to another sheet when certain value equals
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 wrote in message ps.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying rows to another sheet when certain value equals
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" 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 wrote in glegroups.com... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying rows to another sheet when certain value equals
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" wrote in message s.com... 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" 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 wrote in glegroups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying rows from one sheet to another.... | Excel Worksheet Functions | |||
Help copying rows and arranging in new sheet | Excel Programming | |||
copying rows from next sheet over | Excel Discussion (Misc queries) | |||
Copying selected rows to another sheet | Excel Worksheet Functions | |||
Copying rows to a new sheet | Excel Programming |