Copy multiple cells info from sheet if value greater than 0 in another cell Closed

Question

I'm trying to go row by row for a range (c3:c177) to move information from multiple cells in that row that are not grouped together on one sheet to another sheet into specific cells not grouped together or in the same row but only if the value in a cell on the row is greater than 0. I then have to print that out and start with the next row. I have a code that works but it is very long and I'm trying to see if there is a quicker way to do it. Here is the code I'm using, but I only have the first two rows I'm looking at listed so you can see what I'm doing. Any help is much appreciated and thank you in advance for looking.

Application.ScreenUpdating = False

If Range("c3").Value > 0 Then
    
    'pulls shop order number
    
    Sheets("WIP Schedule").Select
    Range("E3").Select
    Selection.Copy

'puts shop order number on shop order
    
    Sheets("Regular").Select
    Range("c3").Select
    ActiveSheet.Paste

'pulls product number
    
    Sheets("WIP Schedule").Select
    Range("f3").Select
    Selection.Copy

'puts product number on shop order
    
    Sheets("Regular").Select
    Range("c6").Select
    ActiveSheet.Paste

'pulls description
    
    Sheets("WIP Schedule").Select
    Range("h3").Select
    Selection.Copy

'puts description on shop order
    
    Sheets("Regular").Select
    Range("d1").Select
    ActiveSheet.Paste

'calls raw material
    
    Sheets("WIP Schedule").Select
    Range("g3").Select
    Application.Run Range("g3").Text
    
    Application.Wait (Now + TimeValue("0:00:01"))
    
    PRINT_each_order
    
    End If
    
If Range("c4").Value > 0 Then
    
    'pulls shop order number
    
    Sheets("WIP Schedule").Select
    Range("E4").Select
    Selection.Copy

'puts shop order number on shop order
    
    Sheets("Regular").Select
    Range("c3").Select
    ActiveSheet.Paste

'pulls product number
    
    Sheets("WIP Schedule").Select
    Range("f4").Select
    Selection.Copy

'puts product number on shop order
    
    Sheets("Regular").Select
    Range("c6").Select
    ActiveSheet.Paste

'pulls description
    
    Sheets("WIP Schedule").Select
    Range("h4").Select
    Selection.Copy

'puts description on shop order
    
    Sheets("Regular").Select
    Range("d1").Select
    ActiveSheet.Paste

'calls raw material
    
    Sheets("WIP Schedule").Select
    Range("g4").Select
    Application.Run Range("g4").Text
    
    Application.Wait (Now + TimeValue("0:00:01"))
    
    PRINT_each_order
    
    End If

vcoolio · Answer

Hello TBone,

My understanding of your explanation and sample code is:-

- You have numeric values in Column C of a worksheet named "WIP Schedule".
-  In the "WIP Schedule" sheet, if any cell in Column C has a value greater than 0 (zero), then cells from Columns E, F and H on the same row need to be copied to cells C3, C6 and D1 respectively in a worksheet named "Regular".
- Immediately after the relevant cell data has been transferred to the "Regular" sheet, the data on the "Regular" sheet, with the newly transferred data from the "WIP Schedule" sheet, needs to be printed out.
- I'm assuming that you have headings in Row2 in the "WIP Schedule" sheet with data starting in Row3.
- With this line of code:-
Application.Run Range("g3").Text
I'm assuming that you are calling another macro to do the printing.

Is my understanding correct?

It would also be very helpful for potential helpers if you uploaded a sample of your workbook to a free file sharing site such as WeTransfer or Drop Box then posted the link to your sample file back here.
Make sure that your sample is an exact replica of your workbook showing actual inputs and the expected outputs. If your data is sensitive, then please use dummy data. A dozen or so rows of data will suffice for us to work with.
Please ensure that any code that you already have is included in the sample.

Cheerio,
vcoolio.

Copy multiple cells info from sheet if value greater than 0 in another cell

1 response

Similar discussions

Subscribe To Our Newsletter!