Cycling through files in a folder

Hi again,

Thanks for the link, Dave, I've used the Macro you suggested and this has solved the problem I was having before. Unfortunately I've come across a new problem!

I'm trying to do a basic copy and paste from one workbook to another. At the moment I have the sheets worked out (I think) but there's a problem with the pasting. The code I have in place at the moment (with a bit of extra around the edges) is:

 Do While strExtension <> ""
            Set wbOpen = Workbooks.Open(strPath & strExtension)
            With wbOpen
                .Sheets("Sheet3").Select
                Range("A3:B3").Copy
                .Close SaveChanges:=False
            End With
            With wbNew
                Cells(1, 1).Select
                ActiveCell.SpecialCells(xlLastCell).Offset(1, 1).Select
                ActiveSheet.Paste
            End With
            strExtension = Dir
        Loop

The problem is that Excel doesn't like the ActiveSheet.Paste command for some reason! I've done the copy and paste manually while recording a Macro, and this is what it came up with, but when it's run on its own I get the error 'Object doesn't support this property or method'.

I've tried loads of other paste options, including Selection.Paste and changing the Cell line above to .paste instead of .select (but I don't think the cells command works with paste), and changing the cells command to a range command, but this throws up yet more problems.

I've simplified the macro to the real basics: I select the cells in the other workbook, then select the book I want to paste into and have the macro:

Cells(1, 1).Select
                ActiveCell.SpecialCells(xlLastCell).Select
                Selection.Paste

And while this is basically exactly what's recorded when I do it manually and record, when I run it as a macro I get the same problems.

I've tried other methods of selecting the cell I need to paste into, such as columncount etc, but have had no luck either.

I can't believe I'm having this much trouble with such a simple thing, so any help would be really appreciated!

Thanks again,

Kate

Hi again Dave,

Unfortunately that's one of the things I've tried. In fact I've still got it in in the mini macro I'm using to tease the problem out, but it hasn't helped. Should have posted it in the code, really, sorry about that.

Just to check the line I have works, it's:

Workbooks("data.xls").Worksheets("Sheet1").Activate

Kate