open variably named file, copy/paste data into consolidation file, open next file in list

Hello,

This is my first post, and I know enough about macros/VBA to be dangerous (i.e. write simple macros) but not enough to use variables, named ranges, etc. effectively!

I have a set of files, each of which is similarly named, and I want to open one, copy data from a pivot table on a tab that is identically named in each file, and paste the data into a "consolidation" file from which I'm running the macro, close the file and open next one.

I listed the variable part of the file name in one column of the “consolidation” file, and I think I have the code right to open a file, copy the data, but my issues are:
1) I get an error selecting the named range (a variable) in which to paste the data, (at this point I get the error “Run-time error ’1004’: Method ‘Range’ of object ‘_Global’ failed”
2) switching back and forth between the "consolidation" file and the file with the data appears to work, but sometimes the data file opens yet does not become the active workbook, and that makes the rest of the code not work.

Any help you can offer would be greatly appreciated!

Thanks,
Stacey

Please Login or Register  to view this content.