Relative Reference for SaveAs 1

[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?? Also, would it be possible for me to add code that would automatically set-up an email with the new workbook as an attachment? (user would enter email address and Send. Then Macro would continue by closing that WorkBook and returning to the "Input" worksheet.)

Thanks 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

 

[IknowMe]

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

There's some nice FAQ's explaining why ActiveWorksheet and such are bad practices and how to avoid using it if your interested in learning something new.

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[tcgoth]

Thank you for the response. Now I keep getting a "Object required" error in the "ActiveWorksheet" line.

I also realized I have calculations in the tab that is getting copied - so I also need to copy/paste special so that I can display only the values.

I'll read the FAQs to see if I can get the copy/paste special. Any ideas on why I'm getting the Object Required error?

Thanks!

 

[Gavona]

ActiveWorksheet.Cells(5, 2).value


Gavin

 

[PHV]

ActiveSheet.SaveAs "C:\Documents and Settings\Terry\" & ActiveSheet.Cells(5, 2) & ".XLS"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tcgoth]

I am still coming up with "Runtime Error 424. Object required."

I stripped everything out and I am back to just trying to use SaveCopyAs - I continue to get the same error. Uggghhhhh!!!!! Help!!!!!

Private Sub CommandButton1_Click()

    ActiveWorkbook.SaveCopyAs "C:\Documents and Settings\Terry\" & ActiveWorksheet.Cells(9, 2).Value & ".XLS"

End Sub

 

[PHV]

There is no ActiveWorksheet proprty but ActiveSheet

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tcgoth]

PHV nailed it - thanks everyone for your input!!!

 

[tcgoth]

I apologize for the ongoing saga - since the "Results" will be sent to clients, I want to get rid of the calculated fields & macros in the file after the SaveAs. I recorded the additional macro to do this and it seems like it should work. I get an error message "Select method of Range Class Failed"

Private Sub CommandButton1_Click()

    ActiveWorkbook.SaveAs "C:\Documents and Settings\Terry\" & ActiveSheet.Cells(9, 2) & ".XLS"
Sheets("Results").Select
    Range("A1:C37").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Input Sheet").Select
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.Delete
    ActiveWorkbook.Save

End Sub