Copy Cell on Based on Criteria, Paste in Different Sheet, Save as PDF, Loop

[shaelyntai]

Hello!

I'm pretty new to Excel Macros but absolutely love them (very recently learned how to save as a PDF using the value in a cell, small wins!)

I have a pretty big ask I'm hoping someone can help with:

Essentially looking to have the values of invoice ids (column A, Sheet1) be copied into my invoice template (D2, Sheet2) [where a vlookup takes place], only if Billing = "No" (column K, Sheet1) , and then save as a pdf (name based on B5 and B12 in Sheet2). I would like this to loop through all of the rows where Column K in Sheet1 has "No"

Appreciate any assistance or insights if this would be possible, thank you!

 

[yinkajewole]

Sub CopyValues()
Dim LastCell As Integer
Dim mySheet1 As Worksheet
Dim mySheet2 As Worksheet
Dim fileName As String
Dim filePath As String
filePath = "C:\Invoice\"
Set mySheet1 = Sheets("Sheet1")
Set mySheet2 = Sheets("Sheet2")
LastCell = mySheet1.Cells(Rows.Count, "K").End(xlUp).Row
For i = 1 To LastCell
    If mySheet1.Cells(i, "K").Value = "No" Then
    With mySheet2
    .Range("D2").Value = mySheet1.Range("A" & i).Value
    fileName = .Range("B5").Value & " " & .Range("B12").Value
    .ExportAsFixedFormat Type:=xlTypePDF, fileName:=filePath & fileName & ".pdf"
    End With
    End If
Next i
End Sub

 

[shaelyntai]

Thank you so much yinkajewole! This works fantastic and a great framework to work with, I truly appreciate it!

 

[yinkajewole]

You are most welcome