iterate through a list and find a file, copy that file to another folder

Hi guys, let me just get to the example, since I cannot explain it so well.

Because I might explain badly, let me give an example, if I have:

Cat Cat Weight Notes
Dylan 13 God kitty
Kitten 14 Sharp claw

I have some VBA code that gives a folder "dylan" and "Kitten" with some pdf that works as intended: it creates folders "Dylan" and "Kitten" and within Dylan we will export a pdf called "Dylan" that only has Dylan's information and the header row; within Kitten we will export a pdf "Kitten" that only has Kitten's information and the header row. It is useful for, if I have like 100 cats, printing 100 different pdfs for individual cats.

I need to add to the right folder another document on my computer that is called "God kitty.txt" to "Dylan" and "Sharp claw.txt" to "Kitten". These have some important information regarding the cats.

I know that the creating-PDF and saving-in-folder works just fine! All thanks to Leith Ross. Unfortunately, the finding "God kitty.txt" does not work. It finds "God kitty.txt", but then it does not copy it. I am confused how to copy the folder from one place to another, if I just use FSO.CopyFolder I think I need to use FSO.CreateObject("scripting.filesystemobject"), but I am having some trouble with this approach. Is there any other way for me to copy the folder to indexFolder after I find it? indexFolder is the folder I create for each PDF, all within "C:/Users/叶秋/Desktop/Test1". Sorry for the Chinese, cannot change that easily the computer name.

I would greatly appreciate a step in the right direction. I've been at VBA for about 3 weeks, so I'm kind of a beginner. It's really satisfying though

Thanks!
Ye Qiu

Sub pdfCopy()

Dim File As String
Dim Folder As String
Dim index As Long
Dim pdfRng As Range
Dim Rng As Range
Dim Wks As Worksheet

Dim fileRng As Range
Dim indexFolder As String

Dim StrFile As String
Dim wantfile As Variant

Folder = "C:\Users\叶秋\Desktop\Test1\"
wantfile = Dir("C:\Users\叶秋\Desktop\Test2\")

Set Wks = Worksheets("Sheet1")

Set Rng = Wks.Cells(1, "A").CurrentRegion
Set fileRng = Wks.Cells(1, "G").CurrentRegion

Wks.Names.Add "Print_Area", Rng

Set pdfRng = Intersect(Rng, Rng.Offset(1, 0))
If pdfRng Is Nothing Then Exit Sub Else pdfRng.Rows.Hidden = True

For index = 1 To pdfRng.Rows.Count

pdfRng.Rows(index).Hidden = False

With Sheets(1).PageSetup
.Orientation = xlLandscape
.FitToPagesTall = False
.FitToPagesWide = 1
.Zoom = False
End With

indexFolder = Folder & pdfRng.Item(index, 1) & "\"
MkDir indexFolder

While (wantfile <> "")
If InStr(wantfile, fileRng.Item(index + 1, 1)) > 0 Then
MsgBox "found " & wantfile
End If
wantfile = Dir
Wend

File = indexFolder & pdfRng.Item(index, 1) & ".pdf"
Wks.Names("Print_Area").RefersToRange.ExportAsFixedFormat xlTypePDF, File
pdfRng.Rows(index).Hidden = True

Next index

Rng.Rows.Hidden = False

End Sub

Guys the formatting is so bad, let me try again

Please Login or Register  to view this content.