T
TimN
I have the following code that copies data to another workbook, then saves
the file as the employee name in cell C2. The user can define the location
the file saves.
Private Sub CommandButton4_Click()
'rCell Makes a copy of the initial calculations and saves to the Data
worksheet
'rFound looks for a duplicate date and if found copies over it else copies
to next avail row
Dim rCell As Range
Dim rFound As Range
With Application.ThisWorkbook
Set rFound =
..Worksheets("Data").Columns("B").Find(What:=(.Worksheets("STD Calc") _
.Range("C6")), LookAt:=xlWhole, LookIn:=xlFormulas)
If rFound Is Nothing Then
Set rCell =
..Worksheets("Data").Range("A65536").End(xlUp).Offset(1, 0)
Else
Set rCell = rFound.Offset(-3, -1)
End If
Worksheets("STD Calc").Range("B1737").Copy
rCell.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End With
'the following opens the "Save As" MsgBox in Excel so the user
'can save to the location they desire. File is saved as name of employee in
cell C2
Dim RetVal As Variant
RetVal = Application.GetSaveAsFilename(Range("C2"))
If RetVal <> False Then
ThisWorkbook.SaveAs RetVal
End If
End Sub
The problem I have is after saving the file to the desired location the file
is saved as type "File" not .xls. I don't know what that means exactly.
However when the file is opened for a second time and changes made, then the
user clicks the Save command button which fires the above code, and I get a
runtime error saying Unable to locate file "employee name".
Why can't it open the file? If I close and go to the file and open it is
fine. I just can't replicate the code above a second time.
Thanks for your help!
the file as the employee name in cell C2. The user can define the location
the file saves.
Private Sub CommandButton4_Click()
'rCell Makes a copy of the initial calculations and saves to the Data
worksheet
'rFound looks for a duplicate date and if found copies over it else copies
to next avail row
Dim rCell As Range
Dim rFound As Range
With Application.ThisWorkbook
Set rFound =
..Worksheets("Data").Columns("B").Find(What:=(.Worksheets("STD Calc") _
.Range("C6")), LookAt:=xlWhole, LookIn:=xlFormulas)
If rFound Is Nothing Then
Set rCell =
..Worksheets("Data").Range("A65536").End(xlUp).Offset(1, 0)
Else
Set rCell = rFound.Offset(-3, -1)
End If
Worksheets("STD Calc").Range("B1737").Copy
rCell.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End With
'the following opens the "Save As" MsgBox in Excel so the user
'can save to the location they desire. File is saved as name of employee in
cell C2
Dim RetVal As Variant
RetVal = Application.GetSaveAsFilename(Range("C2"))
If RetVal <> False Then
ThisWorkbook.SaveAs RetVal
End If
End Sub
The problem I have is after saving the file to the desired location the file
is saved as type "File" not .xls. I don't know what that means exactly.
However when the file is opened for a second time and changes made, then the
user clicks the Save command button which fires the above code, and I get a
runtime error saying Unable to locate file "employee name".
Why can't it open the file? If I close and go to the file and open it is
fine. I just can't replicate the code above a second time.
Thanks for your help!