Help with Queries or VBA code, trying to separate a customer list into separate files

So I have made some progress... I found this video that helps me write a VBA code to extract data from an Invoice and into another sheet. But I think I want to do precisely the opposite. Link below to help illustrate:

https://www.youtube.com/watch?v=r4hYRIn-0nA

Code below that has been written to do the opposite of what I need it to do... I need this code to generate new files that are basically the same template as this invoice template.... so I assume I need to switch some code around but not sure exactly what to do. But, becasue I need to run through a list, I would guess that I need to add a loop somewhere in here.... Does anyone have any recommendations ?

Sub InvoiceReport()
Dim myFile As String, lastRow As Integer
myFile = "C:\INVOICES\" & Sheets("Sheet1").Ranges("B5") & "_" & Sheets("Sheet1").Ranges("F1") & "_" & Format(Now(), "yyyy-mm-dd") & ".pdf"
lastRow = Sheets("Sheet2").UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1
'Transfer data to Sheet2
Sheets("Sheet2").Cells(lastRow, 1) = Sheets("Sheet1").Range("B5")
Sheets("Sheet2").Cells(lastRow, 2) = Sheets("Sheet1").Range("F1")
Sheets("Sheet2").Cells(lastRow, 3) = Sheets("Sheet1").Range("B5")
Sheets("Sheet2").Cells(lastRow, 4) = Now
Sheets("Sheet2").Hyperlinks.Add Ancher:=Sheets("Sheet2").Cells(lastRow, 5), Address:=myFile, TextToDisplay:=myFile
'Create invoice in PDF format
Sheets("Sheet1").ExportAsFixedFormat Type:=xlTypePDF, Filename:=myFile
Application.DisplayAlerts = False
'Create invoice in XLSX format
ActiveWorkbook.SaveAs "C\INVOICES\" & Sheets("Sheet1").Range("B5") & "_" & Sheets("Sheet1").Range("F1") & "_" & Format(Now(), "yyyy-mm-dd") & ".xlsx", FileFormat:=51

'Reset specific invoice values
'Sheets("Sheet1").Range("B5").ClearContents
'Sheets("Sheet1").Range("F1").ClearContents
'Sheets("Sheet1").Range("I36").ClearContents
Application.DisplayAlerts = True

End Sub