Excel VBA - go through each cell value (row) 4

[cutestuff]

hi,
I'm not quite sure how to make this work (I think I'm close).
The paths to the files are stored in cells in Column B.
I would like to loop through each row and grab the path and copy the file.

So far this is what I have:

  Dim fso As Object
    Dim cPath, nPath As String
    Dim x As Range
    
    nPath = Worksheets("Sheet1").Range("C2").Value
   
    Set fso = CreateObject("Scripting.FileSystemObject")
    
     Application.Goto ThisWorkbook.Sheets(1).Range("B2")
    For Each x In Range("B2:B")
        fso.CopyFile x.Value & "*.pdf", nPath
    Next x
    
    Set fso = Nothing

But it keeps saying "Method Range of worksheet failed" on this line:
For Each x In Range("B2:B")

What am I doing wrong?

Thanks in advance,
cs

 

[PHV]

I'd try something like this:
For Each x In Range([B2],[B2].End(xlDown))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cutestuff]

Thank you! That worked! I didn't want to hard code the range.
You are pure genius!

 

[mscallisto]

FYI You need to be careful saying:

Dim cPath, nPath As String
' nPath will be string while
' cPath will be variant

Unless you really want cPath to be a variant you probably should say:

Dim cPath as string, nPath As String

 

[NashProjects]

assuming that the column B contains all the filenames personally i would go with the following as its clearer and you can F8 through what you are doing easily

Dim dRow as double
Dim fso As Object
Dim nPath As String

nPath = Worksheets("Sheet1").Range("C2").Value
Set fso = CreateObject("Scripting.FileSystemObject")

Application.Goto ThisWorkbook.Sheets(1).Range("B2")
drow = 2
do until cells(drow,2) = ""
fso.CopyFile cells(drow,2) & "*.pdf", nPath
drow = drow+1
loop

Set fso = Nothing

Kind regards

Lee Nash

http://www.Nashprojects.com

 

[cutestuff]

wow - I didn't know that. Thanks, mscallisto! I thought it is 'implied' that all variables declared in that line would be type String.

Thanks also Lee Nash - I will try your suggestion. It looks 'cleaner' than mine.

 

[fumei]

Thanks, mscallisto! I thought it is 'implied' that all variables declared in that line would be type String."

On the contrary, unless something is explicitly declared As something, it is implicitly declared as Variant. Each "As" is independent.

Dim cPath, nPath As String

cPath has no As, and is therefore implicitly declared as Variant.


unknown

 

[cutestuff]

Thanks again .