can you review this macro plz?

[cmefly]

hi,

I've got the following macro that goes into every excel file in a certain directory and copies certain cells to another worksheet...for some reason, it's not finding any files even though i clearly have 65 files in the directory...any help would be greatly appreciated.

Sub fetch2011columns()
Dim a, k As Integer
Dim strPath As String
Dim strFileName As String
Dim i As Integer
Dim vaFileName As Variant


strPath = "C:\2011"
a = 1

With Application.FileSearch
.LookIn = strPath
.Filename = ".xls"


For Each vaFileName In .FoundFiles
Workbooks.Open vaFileName

With ActiveWorkbook
.Worksheets("DataFetcher").Select
.Range("C5:C240, e5:e240,j5:j240,m5:m240").Select
.Close
End With

Windows("2011extractor").Activate
Range("A" & a).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

a = 240 * k + 1

Next

End With

End Sub


thanks,

Marc

 

[bertie]

Mistake
I made a typo in changing back the file path.

Edit this:

strPath = "C:\2001"

 

[cmefly]

bertie,

is it working for you?

i can't seem to get past the paste special section! ahhhh

 

[cmefly]

no worries - i caught that error....
i'm trying so many different things but can't get the paste or paste special to work...

 

[bertie]

Yes, it's working for me.

If you can't get past the PasteSpecial line then check the name of the worksheet you are pasting into is correct. 2011extractor

When testing I just copied that into the sheetname.

If you send me a pm with your eMail I can forward the file I was working on.

Bertie

 

[cmefly]

yeah! it worked...i've got to learn how to copy and paste properly!

thank you for your time and patience bertie - you have no idea how much time you just saved me!

 

[bertie]

Your welcome.