Transfer data to another sheet based on criteria and save as PDF

[manona]

Hi,

I am trying to copy information to another tab based on a criteria, and save as PDF.

I know how to copy different cells from one tab and paste in different cells in another tab, but this time I need to copy values only if there is no 'Yes' in the column D (starting at row 2). Once the data is transferred, I would like for it to save as a PDF under the value in column B (which is 'name'), and go back to the original tab and look for the next row with data that does not have 'Yes' in column D.

I would like this macro to be a 'button' macro (to click on a shape to initiate the macro).

Any help is appreciated, thank you in advance.

Manon

 

[ravishankar]

Sub Macro1()
Dim a As Long
For a = 2 To Cells(Rows.Count, 4).End(xlUp).Row
If Cells(a, 4) <> "Yes" Then
Cells(a, 4).Copy
Sheets("sheet2").Range("A1").PasteSpecial
      Sheets("sheet2").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\excelmacro\ravi" & Cells(a, 2) & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties _
        :=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        End If
        Next a
        MsgBox "complete"
End Sub

change file path and run the macro
ravishankar