I think the problem is that FilePathCopyTo is a full path, and
Window.Activate just needs the filename. However, the window with that file
should be active anyway, because it's the one you just opened. I think you
mean to refer to a different file (going by your original code). You're
probably better off activating the workbook instead of the window - if you
declare a workbook variable and set it to the workbook you can then refer to
it later.

Unless that it, the workbook you are trying to activate is the one with the
code in (don't know if this is the case). Then you can just use
ThisWorkbook.

Jeff

"SandyUK" wrote in
message ...

Hello all

A while back I had to quickly put together a routine to take the
information from a main workbook and copy and save aspects of the data
into many different workbooks using a second workbook as a template. At
the time I recorded a macro to deal with the repetitive stuff which
meant that the workbook names of the workbook containing the data and
the template where embedded in the code. I am trying to tidy it up by
using the code below to specify the main workbook and the template.

Sub GetFilePathCopyTo()

Dim Finfo As String
Dim FilterIndex As Integer
Dim Title As String

' Set up list of file filters
Finfo = "Text Files (*.txt),*.txt," & _
"Lotus Files (*.prn),*.prn," & _
"Comma Separated Files (*.csv),*.csv," & _
"ASCII Files (*.asc),*.asc," & _
"All Files (*.*),*.*"

' Display *.* by default
FilterIndex = 5

' Set the dialog box caption
Title = "Select a File to Copy To"

' Get the filename
FilePathCopyTo = Application.GetOpenFilename(Finfo, _
FilterIndex, Title)

' Handle return info from dialog box
If FilePathCopyTo = False Then
MsgBox "No file was selected."
Else
MsgBox "You selected " & FilePathCopyTo
End If

End Sub


This works fine and I changed the next bit of code in the process from


Sub OpenRequote()

Workbooks.Open Filename:= _
"I:\Costings\Development\2005 - 2006\Requote\requote
template.xls"
Range("B6").Select
Windows("SAStanCosts WE 11 19 05.xls").Activate

End Sub


To this so that the hard coded workbook name was replaced


Sub OpenRequote()

Workbooks.Open FileName:= FilePathCopyTo
Range("B6").Select
Windows(FilePathCopyTo).Activate

End Sub


I am getting a run time error 9 when the code gets to
Windows(FilePathCopyTo).Activate and I don't know what to do, can
anybody help?

Regards

Adrian


--
SandyUK
------------------------------------------------------------------------
SandyUK's Profile:
http://www.excelforum.com/member.php...o&userid=17487
View this thread: http://www.excelforum.com/showthread...hreadid=547362