Pano

It also adds the current date to the filename.

sFilename = "Personal.xls " & _
Format(DateSerial(Year(Date), Month(Date), _
Day(Date)), "dd MM yy") & ".xls"

Regards
Dylan

"pano" wrote:

For all who have searched the archives and the net & found nothing
complete.
The following code works. Will save the excel file to personal.xls on
your USB drive no matter what letter drive it is.

Sub Save_to_usb_drive()
On Error Resume Next
Dim i As Integer
Set fs = CreateObject("Scripting.FileSystemObject")
For i = 67 To 90
Set drspec = fs.GetDrive(Chr(i))
If drspec.drivetype = 1 Then

' MsgBox "Drive " & Chr(i) & " is the removable drive"
sPath = Chr(i) & ":\"
sFilename = "Personal.xls " & _
Format(DateSerial(Year(Date), Month(Date), _
Day(Date)), "dd MM yy") & ".xls"
ActiveWorkbook.SaveCopyAs sPath & sFilename
i = 90

End If
Next i
End Sub


Thanks members for all your help and suggestions.....