Using Macro to save data in new excel file

[Spartanz]

Hi guys! I have a problem here. I have completed a form in Excel that allows users to input data and make selection on popup calendars and dropdown list. All the data entered or selected will be input into another worksheet on the same file. Now I need to save whatever data that I obtained from the form into a new Excel file. Is there a way for me to do this by writing a macro, so that all my users have to do is to click on a 'submit' button i created for them?

Advance thansk to all who are kind enough to read my post and many thanks to those who are able to help. I have been pressured by my manager for days...

[Ron de Bruin]

You can use Activesheet.copy to create a new workbook with only that sheet

Try this example that Save the new file in C:\

Sub test()
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "C:\Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
.Close False
End With
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Spartanz" <[email protected]> wrote in message
news:[email protected]...
>
> Hi guys! I have a problem here. I have completed a form in Excel that
> allows users to input data and make selection on popup calendars and
> dropdown list. All the data entered or selected will be input into
> another worksheet on the same file. Now I need to save whatever data
> that I obtained from the form into a new Excel file. Is there a way for
> me to do this by writing a macro, so that all my users have to do is to
> click on a 'submit' button i created for them?
>
> Advance thansk to all who are kind enough to read my post and many
> thanks to those who are able to help. I have been pressured by my
> manager for days...
>
>
>
>
> --
> Spartanz
> ------------------------------------------------------------------------
> Spartanz's Profile: http://www.excelforum.com/member.php...o&userid=28830
> View this thread: http://www.excelforum.com/showthread...hreadid=485785
>

[Spartanz]

Hi Ron, thanks for the reply. If I am not wrong, this method you stated will copy the entire worksheet into a new workbook. Am I right to say so? I think I did not specified out clearly in my above post. I am sorry for this.

Actually, I need to gather the data entered in the form, and save it onto a new file, with the data stored into another table i created. Something like if I write an application out using Java, then afterwhich I use sql to save the fields' information onto an Access database that I created. With java, sql and Access I am able to do so. But Excel here uses macro which is wrote using VB, which i am not as familiar with. Thus I wonder if there is a way for me to append/save the worksheet information onto another worksheet, and not the whole form along.

Once again, Ron, thanks for your previous help. At least now I learnt how to copy the whole sheet out. Something new to me.

You can use Activesheet.copy to create a new workbook with only that sheet

Try this example that Save the new file in C:\

Sub test()
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "C:\Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
.Close False
End With
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Spartanz" <[email protected]> wrote in message
news:[email protected]...
>
> Hi guys! I have a problem here. I have completed a form in Excel that
> allows users to input data and make selection on popup calendars and
> dropdown list. All the data entered or selected will be input into
> another worksheet on the same file. Now I need to save whatever data
> that I obtained from the form into a new Excel file. Is there a way for
> me to do this by writing a macro, so that all my users have to do is to
> click on a 'submit' button i created for them?
>
> Advance thansk to all who are kind enough to read my post and many
> thanks to those who are able to help. I have been pressured by my
> manager for days...
>
>
>
>
> --
> Spartanz
> ------------------------------------------------------------------------
> Spartanz's Profile: http://www.excelforum.com/member.php...o&userid=28830
> View this thread: http://www.excelforum.com/showthread...hreadid=485785
>

[Ron de Bruin]

Do you mean like a database
http://www.rondebruin.nl/copy1.htm

If you use a row below your form with links to the cells(you can hide that row)
in A50 =c10 and in B50 g20 ........

You can copy a range like A50:Z50 to the database sheet


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Spartanz" <[email protected]> wrote in message
news:[email protected]...
>
> Hi Ron, thanks for the reply. If I am not wrong, this method you stated
> will copy the entire worksheet into a new workbook. Am I right to say
> so? I think I did not specified out clearly in my above post. I am
> sorry for this.
>
> Actually, I need to gather the data entered in the form, and save it
> onto a new file, with the data stored into another table i created.
> Something like if I write an application out using Java, then
> afterwhich I use sql to save the fields' information onto an Access
> database that I created. With java, sql and Access I am able to do so.
> But Excel here uses macro which is wrote using VB, which i am not as
> familiar with. Thus I wonder if there is a way for me to append/save
> the worksheet information onto another worksheet, and not the whole
> form along.
>
> Once again, Ron, thanks for your previous help. At least now I learnt
> how to copy the whole sheet out. Something new to me.
>
>
> Ron de Bruin Wrote:
>> You can use Activesheet.copy to create a new workbook with only that
>> sheet
>>
>> Try this example that Save the new file in C:\
>>
>> Sub test()
>> Dim wb As Workbook
>> Dim strdate As String
>> strdate = Format(Now, "dd-mm-yy h-mm-ss")
>> Application.ScreenUpdating = False
>> ActiveSheet.Copy
>> Set wb = ActiveWorkbook
>> With wb
>> .SaveAs "C:\Part of " & ThisWorkbook.Name _
>> & " " & strdate & ".xls"
>> .Close False
>> End With
>> Application.ScreenUpdating = True
>> End Sub
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>> "Spartanz" <[email protected]> wrote
>> in message
>> news:[email protected]...
>> >
>> > Hi guys! I have a problem here. I have completed a form in Excel
>> that
>> > allows users to input data and make selection on popup calendars and
>> > dropdown list. All the data entered or selected will be input into
>> > another worksheet on the same file. Now I need to save whatever data
>> > that I obtained from the form into a new Excel file. Is there a way
>> for
>> > me to do this by writing a macro, so that all my users have to do is
>> to
>> > click on a 'submit' button i created for them?
>> >
>> > Advance thansk to all who are kind enough to read my post and many
>> > thanks to those who are able to help. I have been pressured by my
>> > manager for days...
>> >
>> >
>> >
>> >
>> > --
>> > Spartanz
>> >
>> ------------------------------------------------------------------------
>> > Spartanz's Profile:
>> http://www.excelforum.com/member.php...o&userid=28830
>> > View this thread:
>> http://www.excelforum.com/showthread...hreadid=485785
>> >
>
>
> --
> Spartanz
> ------------------------------------------------------------------------
> Spartanz's Profile: http://www.excelforum.com/member.php...o&userid=28830
> View this thread: http://www.excelforum.com/showthread...hreadid=485785
>

[Spartanz]

Hi Ron, that link you gave me is so cool. It is able to save the whole row from sheet1 to sheet2 right? Is it possible to save from sheet1 of workbook1 to sheet1 of workbook2? different workbook instead of the same workbook. this is because database and the form are 2 different files. Thanks

Do you mean like a database
http://www.rondebruin.nl/copy1.htm

If you use a row below your form with links to the cells(you can hide that row)
in A50 =c10 and in B50 g20 ........

You can copy a range like A50:Z50 to the database sheet


--
Regards Ron de Bruin
http://www.rondebruin.nl


>[/color]

[Ron de Bruin]

See the last link on that page
http://www.rondebruin.nl/copy1.htm#workbook

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Spartanz" <[email protected]> wrote in message
news:[email protected]...
>
> Hi Ron, that link you gave me is so cool. It is able to save the whole
> row from sheet1 to sheet2 right? Is it possible to save from sheet1 of
> workbook1 to sheet1 of workbook2? different workbook instead of the
> same workbook. this is because database and the form are 2 different
> files. Thanks
>
> Ron de Bruin Wrote:
>> Do you mean like a database
>> http://www.rondebruin.nl/copy1.htm
>>
>> If you use a row below your form with links to the cells(you can hide
>> that row)
>> in A50 =c10 and in B50 g20 ........
>>
>> You can copy a range like A50:Z50 to the database sheet
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>> >
>
>
> --
> Spartanz
> ------------------------------------------------------------------------
> Spartanz's Profile: http://www.excelforum.com/member.php...o&userid=28830
> View this thread: http://www.excelforum.com/showthread...hreadid=485785
>[/color]

[jbieser]

This first code in this thread is working great for me; however, is there a way to save the new workbook with the values from the original only?? The current code is saving the workbook with the formulas from the original, thus leaving no information on the created workbook where formulas lie.

Thanks,

Jim