I've got 50 rows of product descriptions in Column A. Quantity of product in Column B. I want to create a list on a different workbook within the same document listing only the products that have quantities next to them, but I just can't work out how to do it.. Please help!

Thanks, Janet

re: "I want to create a list on a different workbook within the same document."Do you mean a different

worksheetwithin the same document?Workbook and Document are usually considered to be the same thing.

Thanks for your reply DerbyDad03. Apologies for the wrong termination, yes I meant worksheet within the same document.

Thanks,

Janet

Manually:With your input data in Sheet1 and your desired output in Sheet2, try this:

1 - In A1, enter this formula: =IF(Sheet1!$B1>0,Sheet1!A1,"")

2 - Drag it over to B1, then drag both cells down as far as you need.

3 - Edit...Copy...Edit...PasteSpecial...Values

4 - Sort

5 - Delete blank rows

Automatically:Option Explicit Sub ProductQuantity() Dim lastSrc_row, nxtDst_row, srcData As Integer 'Find last cell with data in Sheet1 Column A lastSrc_row = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row 'Loop through Sheet1 data, copying Sheet1 Columns A & B to 'the Next empty row in Sheet2 if Sheet1 Column B is > 0 For srcData = 2 To lastSrc_row If Sheets(1).Range("A" & srcData).Offset(0, 1) > 0 Then nxtDst_row = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1 Sheets(1).Range("A" & srcData & ":B" & srcData).Copy _ Destination:=Sheets(2).Range("A" & nxtDst_row) End If Next End Sub

Ask Your Question

Weekly Poll