Modify Macro to Only Print if sales > 0

rf7clan

New Member
Joined
Jun 25, 2020
Messages
8
Office Version
  1. 2007
Platform
  1. Windows
I need to modify a current macro to print only if sales > 0. I have tried a few modifications use if, next, etc. with no luck.

Current Macro:
Sub PSL()
'
' PSL Macro
' Macro recorded 2/6/2012 by wcast
'
'
Sheets("DATA").Select

Range("B12").Select

Dim x As Long, y As Long
Dim q As Integer
x = ActiveCell.Row
y = ActiveCell.Column

Sheets("DATA").Select
Range("A11").Select
x = 11
y = 1

Do While x < Range("r96")


Selection.Copy
Sheets("LETTER").Select
Range("e4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.Run "TM1RECALC"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
x = x + 1
Sheets("DATA").Select
Cells(x, y).Select


Loop

Sheets("DATA").Select
Range("A11").Select
Selection.Copy
Sheets("LETTER").Select
Range("e4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.Run "TM1RECALC"

End Sub

Portion of DATA sheet:
1593097858489.png


Any assistance would be appreciated.
 

rf7clan

New Member
Joined
Jun 25, 2020
Messages
8
Office Version
  1. 2007
Platform
  1. Windows
Dante, appears we need a single quote before the store. Thus for E4 value should be '99089 not 99089. How can this be achieved in your code? I attempt to do by adding a line after initial E4 value set but my method did not work.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
Try this:

VBA Code:
Sub PSL()
  Dim i As Long, sh As Worksheet
  Set sh = Sheets("DATA")
  For i = 12 To sh.Range("A" & Rows.Count).End(3).Row
    If sh.Range("C" & i).Value > 0 Then
      With Sheets("LETTER")
        .Range("E4").Value = "'" & sh.Range("A" & i).Value
        Application.Run "TM1RECALC"
        .PrintOut Copies:=1, Collate:=True
      End With
    End If
  Next i
 

rf7clan

New Member
Joined
Jun 25, 2020
Messages
8
Office Version
  1. 2007
Platform
  1. Windows
Dante, my light bulb went on and I had the business owner change the E4 cell format from number to text then your macro worked liked a charm! Really appreciate your assistance, professionalism, and mentoring!
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,445
Messages
5,636,322
Members
416,912
Latest member
danluk12

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
Top