EXIT SUB IF

P

pure vito

Board Regular
Joined
Oct 7, 2021
Messages
180
Office Version
  1. 365
Platform
  1. Windows
Hi all I'm back with another noob question,

I have a number of issues I need to resolve but if I can get over 1 hurdle it may help me with the rest,

Here is my recorded macro I would like it to exit the sub if D3 , E3 , F3 , G3 , H3 , I3 , J3 , cells have no values.

My macro is to print labels of one part number but with a different qty's per label id really appreciate any help given thanks in advance.


VBA Code: 
Sub PRINTLABLES()
'
' PRINTLABLES Macro
'

'
    Range("B3").Select
    Selection.Copy
    Sheets("Bulk LAB").Select
    Range("Q14:R14").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("C3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Bulk LAB").Select
  
    Range("I10:K10").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("D3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Bulk LAB").Select
    Range("Q15").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("Sheet1").Select
    Range("E3").Select
    Selection.Copy
    Sheets("Bulk LAB").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("Sheet1").Select
    Range("F3").Select
    Selection.Copy
    Sheets("Bulk LAB").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("Sheet1").Select
    Range("G3").Select
    Selection.Copy
    Sheets("Bulk LAB").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("Sheet1").Select
    Range("H3").Select
    Selection.Copy
    Sheets("Bulk LAB").Select
    Range("Q15").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("Sheet1").Select
    Range("I3").Select
    Selection.Copy
    Sheets("Bulk LAB").Select
    Range("Q15").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("Sheet1").Select
    Range("J3").Select
    Selection.Copy
    Sheets("Bulk LAB").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
End Sub
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
​
For starters : If [COUNTA(D3:J3)<7] Then Beep: Exit Sub​
 
Upvote 0
Hi Marc,

Thank you for the swift response its very close,

how ever for instance if i remove the value from G3 , H3 , I3 , J3

it exits the sub without printing D3 , E3 , F3

it maybe where I've placed it i'm not sure,


VBA Code: 
Sub PRINTLABLES()
'
' PRINTLABLES Macro
'

'
    Range("B3").Select
    Selection.Copy
    Sheets("Bulk LAB").Select
    Range("Q14:R14").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("C3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Bulk LAB").Select
    Range("I10:K10").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("D3").Select
    If [COUNTA(D3:J3)<7] Then Beep: Exit Sub
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Bulk LAB").Select
    Range("Q15").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("Sheet1").Select
    Range("E3").Select
    Selection.Copy
    Sheets("Bulk LAB").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("Sheet1").Select
    Range("F3").Select
    Selection.Copy
    Sheets("Bulk LAB").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("Sheet1").Select
    Range("G3").Select
    Selection.Copy
    Sheets("Bulk LAB").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("Sheet1").Select
    Range("H3").Select
    Selection.Copy
    Sheets("Bulk LAB").Select
    Range("Q15").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("Sheet1").Select
    Range("I3").Select
    Selection.Copy
    Sheets("Bulk LAB").Select
    Range("Q15").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("Sheet1").Select
    Range("J3").Select
    Selection.Copy
    Sheets("Bulk LAB").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
End Sub
 
Upvote 0
Apologies for the miss understanding, I need the macro to run on all the cells until it hits the cell with no value

1636978597610.png
 
Upvote 0
So, each cell value, if not empty, must be copied to cell Q15 in Sheet "BulkLab"?
BulkLab is then to be printed out?
 
Upvote 0
Try this:

VBA Code: 
Sub PrintIt()
  Dim inCell As Range
  For Each inCell In Worksheets("Sheet1").Range("C3").CurrentRegion.Rows.Columns(1).Cells
    If inCell <> "" Then
      Worksheets("BulkLab").Range("Q15") = inCell
      Worksheets("BulkLab").PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
    End If
  Next inCell
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Counter in For Each loop
Replies
2
Views
197
Possum17
P
P
  • Solved
Need help with macro Dynamic Range??
Replies
6
Views
173
pennbowl
P
A
  • Question
Macro to update list to last row on another sheet copy source from multiple sheets
Replies
0
Views
309
Azamamir
A
J
  • Question
VBA to select partial filename and go back and forth between two files to copy paste
Replies
1
Views
166
shknbk2
S
R
  • Solved
How to loop a macro
Replies
2
Views
374
rlcurlee
R

Forum statistics

Threads
1,214,986
Messages
6,122,611
Members
449,090
Latest member
vivek chauhan

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top