Export data to new workbook, maintaining formating and page setup
On May 30, 1:12 pm, KUMPFfrog
wrote:
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
|