Regarding option to VLOOKUP mutiple unique ids

[jyothisurya111]

Hi Team ,
I have started practicing VBA recently and I have came across a situation as below .

I have the data were in contains all the information in sheet 2 Example

and in sheet 1 I have executed some excel lookup formulae to get the data in sheet 2 to sheet 1 . I have taken the Unique id as a base for the whole bunch of VLOOKUP formulas in Sheet 1 . So once I got the values I have to print in PDF copy and save in folder . These is the same step I have to do it for more than 2000 unique ids from sheet 2 to sheet 1 and then PDF . It was consuming a lot of time so could you please help me with macro code where I don't have to copy paste the code from sheet 1 to sheet 2 every time instead macro can pick up one by one from Sheet 2 "Column A" to sheet 1 cell highlighted in Yellow then print a pdf copy for me ?. Kindly help me on this as its very complicated me to process it manually every day.

Below was the sample of Sheet 1 looks like :

Please let me know if any more information required .

 

[jyothisurya111]

Hi Team any help please ?

 

[maabadi]

First Change StrPath to Path you want save pdf files.
Try this:

Sub PrintIDStoPDF()
Dim i As Long, Lr As Long, Sh1 As Worksheet, Sh2 As Worksheet, StrPath As String
Set Sh1 = Sheets("Sheet1")
Set Sh2 = Sheets("Sheet2")
Lr = Sh2.Range("A" & Rows.Count).End(xlUp).Row
StrPath = "C:\Users\46506090\Desktop\"
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For i = 2 To Lr
Sh1.Range("C8").Value = Sh2.Range("A" & i).Value
Sh1.ExportAsFixedFormat Type:=xlTypePDF, Filename:=StrPath & Sh1.Range("C8").Value & ".pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
Next i
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

 

[jyothisurya111]

First Change StrPath to Path you want save pdf files.
Try this:

Sub PrintIDStoPDF()
Dim i As Long, Lr As Long, Sh1 As Worksheet, Sh2 As Worksheet, StrPath As String
Set Sh1 = Sheets("Sheet1")
Set Sh2 = Sheets("Sheet2")
Lr = Sh2.Range("A" & Rows.Count).End(xlUp).Row
StrPath = "C:\Users\46506090\Desktop\"
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For i = 2 To Lr
Sh1.Range("C8").Value = Sh2.Range("A" & i).Value
Sh1.ExportAsFixedFormat Type:=xlTypePDF, Filename:=StrPath & Sh1.Range("C8").Value & ".pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
Next i
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

@maabadi : Excellent it's working perfectly . Thank you so.. much you save a lot of time for me

 

[maabadi]

You're Welcome & Thanks for follow-up.

 

[jyothisurya111]

You're Welcome & Thanks for follow-up.

Hi Hope you are doing good .

I have tried this code and it was working fine till date but all of a sudden from yesterday i am having this run time error 1004 and when i debug it is highlighting the below code lines marked in red . Could you please help me what might be the possible mistake ?

Sub DateToForm3()

Application.ScreenUpdating = False 'Speed up macro
Application.DisplayAlerts = False 'Speed up macro

Dim i As Long, svNm As String, fPath As String
Dim Sh As Worksheet, ShCS As Worksheet
Dim MyRange As Range, MyVal As Range, LR As Long

Set Sh = Sheets("Invoice")
Set ShCS = Sheets("Vendor Info")

LR = ShCS.Range("A" & Rows.Count).End(xlUp).Row

fPath = "C:\Users\u863238\OneDrive - Foot Locker\Desktop"
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For i = 2 To LR 'Loop through 5 to last row number
Sh.Cells(14, 8) = ShCS.Cells(i, 1)
Sh.PrintOut 'Print statements sheet
svNm = Sh.Cells(2, 10) & ".pdf"
Application.PrintCommunication = True
ChDir fPath
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPath & svNm, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
Next i 'go back and do next row in loop

Sh.Cells(14, 8).ClearContents 'Clear Statements "H14" cell contents & You can edit this content number when you have a situation to chnage this cell reference

Err: 'Error handling and end of code to turnscreen updating and display events back on
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[maabadi]

Are your pdf with same name open when code running?
Also add one backslash at the end of fPath.