General form (once you have copied the necessary cells) is as below
Workbooks(DestinationBook).Sheets(DestinationSheet ).Range(DestinationRange).PasteSpecial

In VBA you will need to set or calculate DestinationBook, DestinationSheet,
and DestinationRange - to extract the month from the end of your file name to
select the matching DestinationBook you can do this:

Dim FileSuffix as String
FileSuffix = ActiveWorkbook.Name
' Following line takes just from final space forward - i.e., 'MMMYY.xls'
FileSuffix = Right(FileSuffix, Len(FileSuffix)-InStrRev(FileSuffix," "))
DestinationBook = "Board " & FileSuffix ' If you want to copy to the Board
file

"bpmccoy" wrote:


Hi,

I'm writing a macro to copy and paste value froms from 2 workbooks into
a third workbook. The filenames will vary according to month.
Let's say my files a "Summary Oct04.xls", "Mgt Oct04.xls" and "Board
Oct04.xls". Obviously for November the filenames will be different.

How can I define these to the macro will copy from the relevant
worksheet to the correct workbook, regardless of the filename.

Any help would be appreciated.

Thanks,

B


--
bpmccoy
------------------------------------------------------------------------
bpmccoy's Profile: http://www.excelforum.com/member.php...o&userid=16715
View this thread: http://www.excelforum.com/showthread...hreadid=319220