Populate filled in data in master worksheet to a separate worksheet.

[texsa]

So I have a database of products listed in a worksheet over 500 items.

The headers for the file is

					Pallet Qty	Box Size	Order Per Box
Code	Product	Pack	Cost P/kg	Cost per Case

It also has sub categories up to 25.

Now the client looks at the main database and fills in what he requires in column "Order Per Box"
usually out of the 500 items at most clients chooses 60 items. So I would like those 60 items to appear on a separate worksheet.
This will save us time going through orders and saving paper. As we print out every single order for record purposes.

So Stock Sheet is where stock list is and an ordering column and then Order Sheet is where only filled in items on the stock sheet will appear.

 

[earthworm]

So I have a database of products listed in a worksheet over 500 items.

The headers for the file is

					Pallet Qty	Box Size	Order Per Box
Code	Product	Pack	Cost P/kg	Cost per Case

It also has sub categories up to 25.

Now the client looks at the main database and fills in what he requires in column "Order Per Box"
usually out of the 500 items at most clients chooses 60 items. So I would like those 60 items to appear on a separate worksheet.
This will save us time going through orders and saving paper. As we print out every single order for record purposes.

So Stock Sheet is where stock list is and an ordering column and then Order Sheet is where only filled in items on the stock sheet will appear.

kindly share sample data in excel file

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your two sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[texsa]

hi there

here is the file on dropbox
ORDER LIST WEEK 38 - 2020-09-14-LAPTOP-7C7E0SEM.xlsx

 

[mumps]

Click here to download your file. There may be a problem with how you designed your Stock sheet. It uses a font size of 45 so when the orders are copied to the Order Sheet, that size is reflected in the results. You also have a hidden column which will be copied. The macro assumes that column K will have the quantities for the desired products and all other cells in column K are left blank.

Sub texsa()
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet
    Set srcWS = Sheets("Stock Sheet")
    Set desWS = Sheets("Order Sheet")
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Intersect(Range("K6:K" & LastRow).SpecialCells(xlCellTypeConstants), Columns("K")).EntireRow.Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
    Application.ScreenUpdating = True
End Sub

 

[texsa]

thank you for the macro script. I see what you say with regards to the font size 45.
It works like a charm, I will revert back once I have fine tuned the spreadsheet. If there is anything else I would need.

thank you once again.

 

[mumps]

You are very welcome.