Saveas macro problem

[eat_cress]

Hey guys. I was wondering if anyone would be able to help me with a problem I am having

I have a spreadsheet that is like a form that everyone can access. I want to create a button at the botton which saves the data in a sperate file, as a sort of list, like a database for later analysis. So many people can use this form.xls, and when they click the submit button, it is saved onto a seperate file which collates all the answers from all the forms in one column.

I have tried to work it out myself, I know how to use the saveas function to save it as a seperate file, but I wanted to know if there was a way this could be done.

Thanks a lot!!

 

[VoG]

Maybe something like

Sub aSave()
With ActiveSheet
    .Copy
    ActiveWorkbook.SaveAs Filename:=.Name & ".xls"
End With
ActiveWorkbook.Close
End Sub

 

[Trevor G]

Try something like this:

Have a second sheet in the background where the data can be dropped in from the button. How complex is the form design? as you will have to workout the cell ids to drop in etc

Sub copyrange()
Application.ScreenUpdating = False
With Sheets("Sheet3")
.Range("A2") = Sheets("List").Range("A2")
.Range("B2") = Sheets("List").Range("C2")
.Range("C2") = Sheets("List").Range("E2")

End With
Sheets("List").Activate
Rows(2).EntireRow.Copy
Workbooks.Open "M:\Access Files\Forms.xls"
Sheets("Master").Activate
Range("A1").Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
Loop

ActiveCell.PasteSpecial xlPasteValues
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.ScreenUpdating = True
MsgBox "Data Transfered OK", vbInformation, "Excel Sample"


End Sub