Modify Macro to Only Print if sales > 0

[rf7clan]

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:

Any assistance would be appreciated.

 

[DanteAmor]

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

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]

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]

It is populating the store number correctly but not the other fields within the letter which are based on vlookup.

 

[DanteAmor]

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

Or maybe the macro should select the sheet

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]

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.

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

 

[DanteAmor]

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]

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]

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.

 

[rf7clan]

SalesLetter_WIP.xlsm

Shared with Dropbox

www.dropbox.com