Hi,

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

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

Any help would be appreciated.

Thanks,



--
bpmcco
-----------------------------------------------------------------------
bpmccoy's Profile: http://www.excelforum.com/member.php...fo&userid=1671
View this thread: http://www.excelforum.com/showthread.php?threadid=31922

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

"bpmccoy" wrote in message
...

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.

I assume Summary is the target for Mgt & Board. I also assume the latter
part of the filename will always be 5 characters (MMMYY).

pastefilename = "Summary " + Mid(ActiveWorkbook.Name,
Len(ActiveWorkbook.Name) - 8, 5) + ".xls"

Incorporate pastefilename into the paste part of your macro.

Ian