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