Workbooks with variable names
 

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

Workbooks with variable names
 

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

Workbooks with variable names
 

"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