Help with vba printout formulas

[PANDA777]

Hi I have a print input sheet with 3 cells to enter a number into.
Each input cell is linked to cell"C10" on each of the worksheets "1A""1B" & "1C"
I want to be able to link a vba code to a button to be able to only print out all sheets with numbers inputted.
eg. it would be 2 sheets printed only as per the input sheet below

The following formula below is in worksheet "A1" on the the cell c10 highlighted

=IF(Input!G43="","",Input!G43)

The following below code prints all three sheets out even if one of the input numbers is blank. I Think the Isempty statement is interpreting "" as a value
and therefore thinks it isn't empty.

Is there a way to fix this??

I'd also like the entry cells on the input sheet which printout to turn light green to reflect confirmation they've been sent to the printer if possible.

Here's the button code so far :

Sub Button32_Click()
Dim idx As Integer
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
If Not IsEmpty(ws.Range("c10").Value) Then
ws.PrintOut
End If
Next
End Sub

Much appreciate any help

 

[PANDA777]

Thanks you for all your help !!!!! Just the job

 

[PANDA777]

Sorry to come back again, I figured that I might be able to decipher the coding and use it to extend the project to now include more sheets "2A" "2B" "2C" and eventually include the full 17 machines.

Ive copied and edited to include these in the code and they do print out (ONLY) if there is a selection in either "1A" "1B" OR "1C". If there is no selections in "1A" "1B" or "1C" then the command fails and wont print out any selections from "2A" "2B" or "2C". (see below I suspect the cause is because it cant find any cells populated in the 1a 1b 1c range to colour??). I want to be able to colour the input cells for "2A" "2B" and "2C" in the same fashion.

Much appreciate any fix for this

Kind Regards

 

[Snakehips]

@PANDA777 I cannot look at this until tomorrow.
Can you confirm that the row for machine 1 is now row 13 and not 43 as per your first post?
Also, is the machine number in column E then the Product runs in G, I and K ?
Will the Machine cells always contain the numbers 1 in E13, 2 in @15, 3 in E17 etc to 17 in E45 ?

 

[PANDA777]

Hi yes its now row 13 for m\c 1, row 15 for m\c 2, 17 m\c 3, 19 m\c 4 etc.
The machine number on the input sheet is in column C and the product runs are in G, K, O
Yes the machine numbers will remain fixed in C13 C15 C17 C19 etc to C43 (My mistake theres only 16 machines not 17)

Your help is much appreciated and absolutely no rush to answer. MANY Thanks

 

[Snakehips]

@PANDA777 Does this approach do it?

Sub CommandButton1_Click()
Dim ws As Worksheet
Dim Col As Integer
Dim Rw As Integer

'Loop through each machine row
For Rw = 13 To 43 Step 2
    'loop through each product run column
    For Col = 7 To 11 Step 2
        'Check to ignore if product not entered
        If Cells(Rw, Col) = "" Then GoTo NextProduct
        'Check to ignore if cell is marked green, already printed ???
        If Cells(Rw, Col).Interior.Color = 11854022 Then GoTo NextProduct '<<< ??? Delete if not appropriate
        'otherwise
        'set worksheet  - ws name = machine number and suffix A,B,or C
        Set ws = Sheets((Rw - 11) / 2 & Mid(Cells(1, (Col - 5) / 2).Address, 2, 1))
        'print
        ws.PrintOut
        'make Input cell green
        Sheets("Input").Cells(Rw, Col).Interior.Color = 11854022
NextProduct:
     Next Col 'next product
Next Rw 'next machine

Sheets("Input").Select
ActiveSheet.CommandButton1.Activate

End Sub

 

[PANDA777]

Hi again and thanks so much for this. I've tested the button and new code and it works fine for inputs for machines 1A 2A 3A 4A. When I populate 1b 2b 3 b 4b the command prints out 1C 2C 3C 4C even though their input cells are empty and 1b 2b 3b 4b doesnt print at all. I think its just a small tweak needed to the code but its nearly there.

Kind regards

 

[PANDA777]

Hi again and thanks so much for this. I've tested the button and new code and it works fine for inputs for machines 1A 2A 3A 4A. When I populate 1b 2b 3 b 4b the command prints out 1C 2C 3C 4C even though their input cells are empty and 1b 2b 3b 4b doesnt print at all. I think its just a small tweak needed to the code but its nearly there.

Kind regards

 

[Snakehips]

@PANDA777 You keep moving the goalposts!
It looks like your product run columns that were G, I and K are now G,K and O ?
Confirm that that is their final resting place and I will tweak the code accordingly.

 

[Snakehips]

@PANDA777 You keep moving the goalposts!
It looks like your product run columns that were G, I and K are now G,K and O ?
Confirm that that is their final resting place and I will tweak the code accordingly.

@PANDA777 My apologies!!! I see that in your post #14 that you confirmed the columns as G, K, O

 

[Snakehips]

Try this......

Sub CommandButton1_Click()
Dim ws As Worksheet
Dim Col As Integer
Dim Rw As Integer

'Loop through each machine row
For Rw = 13 To 43 Step 2
    'loop through each product run column
    For Col = 7 To 15 Step 4
        'Check to ignore if product not entered
        If Cells(Rw, Col) = "" Then GoTo NextProduct
        'Check to ignore if cell is marked green, already printed ???
        If Cells(Rw, Col).Interior.Color = 11854022 Then GoTo NextProduct '<<< ??? Delete if not appropriate
        'otherwise
        'set worksheet  - ws name = machine number and suffix A,B,or C
        Set ws = Sheets((Rw - 11) / 2 & Mid(Cells(1, (Col - 3) / 4).Address, 2, 1))
        'print
        ws.PrintOut
        'make Input cell green
        Sheets("Input").Cells(Rw, Col).Interior.Color = 11854022
NextProduct:
     Next Col 'next product
Next Rw 'next machine

Sheets("Input").Select
ActiveSheet.CommandButton1.Activate

End Sub