Regarding option to VLOOKUP mutiple unique ids

jyothisurya111

New Member
Joined
Feb 18, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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
1613650206740.png

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 :
1613650864210.png


Please let me know if any more information required .
 

Attachments

  • 1613650194765.png
    1613650194765.png
    3.4 KB · Views: 4

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,681
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
First Change StrPath to Path you want save pdf files.
Try this:
VBA Code:
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
 
Solution

jyothisurya111

New Member
Joined
Feb 18, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
First Change StrPath to Path you want save pdf files.
Try this:
VBA Code:
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

Well-known Member
Joined
Oct 22, 2012
Messages
2,681
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

You're Welcome & Thanks for follow-up.
 

jyothisurya111

New Member
Joined
Feb 18, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • Picture1.png
    Picture1.png
    23.1 KB · Views: 2
  • Picture2.png
    Picture2.png
    60.9 KB · Views: 2

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,681
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Are your pdf with same name open when code running?
Also add one backslash at the end of fPath.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,110
Messages
5,768,156
Members
425,458
Latest member
Jaspal1996

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top