Workbook help

[rickert91]

I am designing a better inventory system for a University food service to track consumables within each department, and I have ran into the dreaded VBA wall. I am trying to move cells from one sheet to the next sheet based on value. But I just want certain cells in the other sheet. If the re-order quantity is greater than 0, I want columns B, C, D AND G to be copies automatically to the next sheet I cannot figure out how to get my workbook to you. I have done all the steps, but somehow, cannot get it to load. I have installed the Xl2bb, but every time I attempt to copy, Excel freezes and has to restart.

 

[OaklandJim]

You can post a link to your workbook using the link icon at the top of the message area. Use Dropbox, Box, 1Drive, Sharepoint, etc. Having the workbook will make it more likely that someone will assist. Otherwise, someone willing to help has to recreate what you have...unlikely.

 

[rickert91]

Here is the link to my workbook. I have been working on it for a bit, and I would love to get certain cells from the CATERING II to the CATERING II ORDER tab. Only the cells that are highlighted, and only rows on the CATERING II ORDER tab. The same for the CHEMICALS tab and the CHEMICAL ORDER tab.

LATESTS AND GREATEST INVENTORY.xlsm

Microsoft Excel Workbook

1drv.ms

 

[kevin9999]

Do you want the 2 Order sheets cleared of any existing entries before the cells are copied across? Also, what exactly do you mean by this:

I want columns B, C, D AND G to be copies automatically

What precisely do you want to happen to trigger the copy to occur?

 

[kevin9999]

Here's a start for you. This module will copy your columns of interest from the "CATERING II" sheet to the "CATERING II ORDER" sheet when the value in column G is greater than zero.

Option Explicit
Sub Catering_Order()
    Application.ScreenUpdating = False
    Dim WsSrc As Worksheet, WsDest As Worksheet
    Set WsSrc = Worksheets("CATERING II")
    Set WsDest = Worksheets("CATERING II ORDER")
    
    'Clear existing data
    WsDest.Range("A3:D1000").ClearContents
    
    Dim LRow As Long, Rng As Range, r As Range
    LRow = WsSrc.Cells.Find("*", , xlFormulas, , 1, 2).Row
    With WsSrc.Range("A3:H" & LRow)
        .AutoFilter 7, ">0"
        If WsSrc.Cells(Rows.Count, "G").End(xlUp).Row > 3 Then
            Set Rng = .Offset(1).Resize(.Rows.Count - 1)
            With Rng
                Set r = Union(.Columns(2), .Columns(3), .Columns(4), .Columns(7))
                r.Copy
                WsDest.Range("A3").PasteSpecial xlPasteValues
                Application.CutCopyMode = False
            End With
        End If
    End With
    WsSrc.ShowAllData
    Application.ScreenUpdating = True
End Sub

 

[rickert91]

Thank you so much. Some are highlighted and some are not. I want to get all of the ones on the order page to not be highlighted so it makes it easier for the Chef and the Catering Manager to order what is needed. I have been here only for a couple of months, and their inventory systems are non-existent. Just trying to make it so simple a chef can do it.

 

[kevin9999]

Thank you so much. Some are highlighted and some are not. I want to get all of the ones on the order page to not be highlighted so it makes it easier for the Chef and the Catering Manager to order what is needed. I have been here only for a couple of months, and their inventory systems are non-existent. Just trying to make it so simple a chef can do it.

The code only copies the values, not the formatting. The highlighting you refer to already exists on the order sheet, some in the form of Conditional formatting. Clear all existing formatting from the order sheet and run the code again and you'll see what I mean.

 

[kevin9999]

Further to my previous post, I've removed all the inconsistent & conditional formatting from the Catering and Chemical Order sheets to demonstrate what I meant. I've also added the code to do the Chemical Order form, and taken the liberty of adding a couple of buttons to run both processes. The link to the updated workbook is provided here.
LATESTS AND GREATEST INVENTORY V2.xlsm