List of data to copy into another sheet and generate PDF

Question

I have a list where I have to copy date in each row in another worksheet and Save it as PDF

Data is in each row in "Instruction" tab, that need to be copied in "Mobile Policy" and saved as PDF

Below is the where each row needs to be copies and saved as PDF

I tried the below code , but it does work properly and creates blank PDF and 1st row never gets generated. Can someone help me fix this please so it can run smoothly

 Sub ARSOAPDF()

Dim varItemsToReplace As Variant
Dim varItem As Variant
Dim wksSource As Worksheet
Dim wksDest As Worksheet
Dim rngSource As Range
Dim rngSource2 As Range
Dim rngCell As Range
Dim email_ As String
Dim email2_ As String
Dim cc As String
Dim subject_ As String
Dim path_ As String
 
Path1 = Worksheets("Instruction").Range("G1").Value



Set wksSource = Worksheets("Instruction")
Set wksDest = Worksheets("Mobile Policy")

wksSource.Activate

email_ = cell.Offset(1, 0).Value
email2_ = cell.Offset(1, 1).Value
cc = cell.Offset(1, 2).Value
subject_ = cell.Offset(1, 3).Value
path_ = cell.Offset(1, 4).Value

With wksSource
    Set rngSource = .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

For Each rngCell In rngSource
    With wksDest
    
    
    Range("A75").Formula = email_
    Range("C75").Formula = email2_
    Range("E75").Formula = cc
    Range("G75").Formula = subject_
    
    Range("A1").Select
    
    wksDest.Activate
    
    

         ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
         path1 & ActiveSheet.Range("C2").Value & " - " & ActiveSheet.Range("B2").Value & ".pdf" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
         :=False, OpenAfterPublish:=False
         ActiveWorkbook.Close SaveChanges:=False

         
    End With
Next rngCell

End Sub

Answer 1

You are not using With properly. Add a dot before each line. So Range("A75").Formula = email_ should be .Range("A75").Formula = email_

It works in second and more loops because you have wksDest.Activate at the end of the loop, so the first loop will read the Activesheet, not wksDest. At second loop, because wksDest is the activeone, then it works.

And probably you'll need ActiveWorkbook.Close SaveChanges:=False outside the loop or deleted.