Export data to new workbook, maintaining formating and page setup

[KUMPFfrog]

Not very familiar with VBA, more of a functions and formula girl.
Have a template set up for my invoicing needs, but once filled out - i would
like to hit a button and have the main invoice form exported to a new
workbook saved with a file name that was created on the form. It would also
need to keep page setup settings as well as the format the data is in (like i
sent it to pdf, but really a new xls).
I would also like to be able to import previous invoice information from
exsisting invoice worksheets, (info like address and previous invoiced
amounts). Am I making any sense? I know Access would be a lot better for
this, but I know nothing there.

 

[Leith Ross]

Not very familiar with VBA, more of a functions and formula girl.
Have a template set up for my invoicing needs, but once filled out - i would
like to hit a button and have the main invoice form exported to a new
workbook saved with a file name that was created on the form. It would also
need to keep page setup settings as well as the format the data is in (like i
sent it to pdf, but really a new xls).
I would also like to be able to import previous invoice information from
exsisting invoice worksheets, (info like address and previous invoiced
amounts). Am I making any sense? I know Access would be a lot better for
this, but I know nothing there.

Hello KUMPFfrog,

This will help you with the first part of your question. The following
macro will copy the sheet that your command button is on (the main
template). Place a command button on your main template form the Forms
tool bar. Attach this macro by right clicking on the button and choose
"Assign Macro" from the context menu. Change the cell address for
NewName in the code to match the cell that holds your new workbook
name.

Sub CopyTemplate()

Dim NewName As String
Dim OldWkb As Workbook

Set OldWkb = ThisWorkbook

NewName = ActiveSheet.Range("A1")
ActiveSheet.Copy
ActiveWorkbook.SaveAs FileName:=NewName

OldWkb.Activate

End Sub

Adding the Macro
1. Copy the macro above by clicking, holding and dragging the mouse.
Press the keys CTRL+C to copy the macro
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time
8. Save the Macro[/b] by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

Sincerely,
Leith Ross

 

[KUMPFfrog]

Leith, Thank you for you help with the first part of my problem.
I did as you said and it worked, but . . . .
I need to change some things and am not sure how to do it.
First, i see that by copying over the entire worksheet - i get to keep the
things i want like the page setup, but i don't want all the formulas &
functions from my template (just the values), and I don't want the whole
sheet, just Range (A158).
Also, when it creates this new workbook - the file remains open. I just want
it to save in the current folder and remain closed.
Any help here?

Thanks again,
KUMPFfrog

Hello KUMPFfrog,

This will help you with the first part of your question. The following
macro will copy the sheet that your command button is on (the main
template). Place a command button on your main template form the Forms
tool bar. Attach this macro by right clicking on the button and choose
"Assign Macro" from the context menu. Change the cell address for
NewName in the code to match the cell that holds your new workbook
name.

Sub CopyTemplate()

Dim NewName As String
Dim OldWkb As Workbook

Set OldWkb = ThisWorkbook

NewName = ActiveSheet.Range("A1")
ActiveSheet.Copy
ActiveWorkbook.SaveAs FileName:=NewName

OldWkb.Activate

End Sub

Adding the Macro
1. Copy the macro above by clicking, holding and dragging the mouse.
Press the keys CTRL+C to copy the macro
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time
8. Save the Macro[/b] by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

Sincerely,
Leith Ross

Not very familiar with VBA, more of a functions and formula girl.
Have a template set up for my invoicing needs, but once filled out - i would
like to hit a button and have the main invoice form exported to a new
workbook saved with a file name that was created on the form. It would also
need to keep page setup settings as well as the format the data is in (like i
sent it to pdf, but really a new xls).
I would also like to be able to import previous invoice information from
exsisting invoice worksheets, (info like address and previous invoiced
amounts). Am I making any sense? I know Access would be a lot better for
this, but I know nothing there.