I wonder if anybody can help a bit please.
What I am trying to achieve is to run some code to loop through the unique pdf cert nos. in column E and offset to column H to get the file path for that pdf cert then copy the cert and paste it in C:\Users\GaryBaker\Desktop\Certs\. And repeat until the end of the unique Nos list.
Also if any pdf certs are missing then highlight the unique cert No. in column E then continue to next cert number.
From Rows 5 to 204
Column B is just numbering the rows
In column C Have a list of pdf file names (these are pdf certs)
I run a bit of code and it filters just the unique values from column C to column E and sorts A-Z
Column G has the formula =IF(E5="","",LEFT(E5,2)) to get the file index No.
Column H has the formula =IF(E5="","",VLOOKUP(G5+0,$I$5:$J$11,2,FALSE)) to get the file path for the folders where the certs are
Columns I & J are the Lookup table.
My code so far is as below, at the moment its giving me an error “Run time error 13, type mismatch” on this line
where I am trying to offset from the list of cert nos in column E. to get the file path for each cert
And I have no clue on how I could highlight the unique No. if the cert is missing
As always all help is much appreciated.
What I am trying to achieve is to run some code to loop through the unique pdf cert nos. in column E and offset to column H to get the file path for that pdf cert then copy the cert and paste it in C:\Users\GaryBaker\Desktop\Certs\. And repeat until the end of the unique Nos list.
Also if any pdf certs are missing then highlight the unique cert No. in column E then continue to next cert number.
From Rows 5 to 204
Column B is just numbering the rows
In column C Have a list of pdf file names (these are pdf certs)
I run a bit of code and it filters just the unique values from column C to column E and sorts A-Z
Column G has the formula =IF(E5="","",LEFT(E5,2)) to get the file index No.
Column H has the formula =IF(E5="","",VLOOKUP(G5+0,$I$5:$J$11,2,FALSE)) to get the file path for the folders where the certs are
Columns I & J are the Lookup table.
My code so far is as below, at the moment its giving me an error “Run time error 13, type mismatch” on this line
VBA Code:
SourcePath = R.Offset(0, 3).Value
And I have no clue on how I could highlight the unique No. if the cert is missing
As always all help is much appreciated.
VBA Code:
Sub CopyCerts()
Dim R As Range
Set R = Range("E5:E204")
Dim SourcePath As String, DestPath As String, FName As String
SourcePath = R.Offset(0, 3).Value 'filepath of folder to copy from
DestPath = "C:\Users\GaryBaker\Desktop\Certs\" 'folder to copy to
'Copy Pdf certs named in list starting at "E5" from folder (File path in cell offset (0,3) from file name in "E5" list)
'then paste on desktop folder "C:\Users\user\Desktop\Certs"
'Highlight any Cert Nos. in range E that are missing
For Each R In Range("E5", Range("E" & Rows.Count).End(xlUp)) 'Check file name in each used cell in column E
FName = Dir(R.Value) 'name of file from list in (E5:E)
Do While FName <> "" 'Loop while files found
FileCopy SourcePath & FName, DestPath & FName 'Copy the file
FName = Dir() 'Search the next file
Loop
Next
MsgBox ("files copied")
End Sub