SaveAs Relative File Reference

[tcgoth]

I have an Excel spreadsheet with two tabs: "Input" and "Results". I created a Command Button at the bottom of the "Input" sheet and I want it to do the following: Copy "Results" to a new workbook, then save that new workbook with a relative filename based upon the contents of "Results".Cells(5,2) Once the new workbook is saved, I want to close that file and return to the "Input" sheet of the original workbook.

I am relatively new to VB and I cannot seem to figure out how to get the correct syntax for the relative file reference inside the SaveAs??

Thaks for any help that you can provide.

Private Sub CommandButton1_Click()
    Sheets("Results Sheet").Select
    Sheets("Results Sheet").Copy
ActiveWorksheet.SaveAs "C:\Documents and Settings\Terry\" & "Results Sheet".Cells(5, 2) & ".XLS"
ActiveWindow.Close
    Sheets("Input Sheet").Select
End Sub

 

[johnwm]

VBA == forum707

Please read faq222-2244 to see how to get the best from these forums

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints