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.
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
Hi and welcome to MrExcel

I suppose you copy the data from column A on "DATA" sheet, starting in cell A11 and go to the last cell with data from column A; and paste the data in the "LETTER" sheet in cell E4.
I also assume the sales value is in column C, if it is greater than 0 then copy and print.

If it is not the C column, then change the "C" for the letter of the column where you have the Sales value in this line:
If sh.Range("C" & i).Value > 0 Then

VBA Code:
Sub PSL_1()
  Dim i As Long, sh As Worksheet
  Set sh = Sheets("DATA")
  For i = 11 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
End Sub
 

rf7clan

New Member
Joined
Jun 25, 2020
Messages
8
Office Version
  1. 2007
Platform
  1. Windows
This is a great start and amazing knowledge base on this board! Almost there but appropriate values not printing.
You are correct, as each row is read on the DATA table the value of A column should go into LETTER E4. That drives the lookup within the LETTER tab populating the appropriate fields from the row within the DATA worksheet.
 

rf7clan

New Member
Joined
Jun 25, 2020
Messages
8
Office Version
  1. 2007
Platform
  1. Windows
It is populating the store number correctly but not the other fields within the letter which are based on vlookup.
 

DanteAmor

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

ADVERTISEMENT

What do you have in this code:
Application.Run "TM1RECALC"
You can put that macro here

Or maybe the macro should select the sheet

Rich (BB code):
Sub PSL_1()
  Dim i As Long, sh As Worksheet
  Set sh = Sheets("DATA")
  Sheets("LETTER").Select
  For i = 11 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
End Sub
 

rf7clan

New Member
Joined
Jun 25, 2020
Messages
8
Office Version
  1. 2007
Platform
  1. Windows
Initial values on the data sheet are derived from a TM1 cube. The TM1RECALC is just updating values.

Macro is working great and printing correct number of stores but for some reason the vlookup codes are not working on the letter tab. Only field getting properly updated is the store number.

1593176020016.png


Sample vlookup =VLOOKUP(C62,DATA!A:I,9,FALSE)
 

DanteAmor

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

ADVERTISEMENT

Unfortunately I don't see the rows and columns of the sheet. I don't know what data you are looking for.
I also don't see the data in the "DATA" sheet.
You could put a sample of your 2 sheets using XL2BB tool, see my signature.
 

rf7clan

New Member
Joined
Jun 25, 2020
Messages
8
Office Version
  1. 2007
Platform
  1. Windows
Dante, unable to use this tool without approval. Can you provide email and I will send file? I have hard coded on the values thus no connection with TM1 to deal with.

Thanks, Rick.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
The forum rules do not allow sending files by mail, but you can share the book in the cloud.

You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,998
Messages
5,628,046
Members
416,289
Latest member
Jbelisari

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