Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export data to new workbook, maintaining formating and page setup
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export data to new workbook, maintaining formating and page se
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 (A1:P58). 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 "Leith Ross" wrote: 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 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
page setup and print area throughout workbook | Excel Discussion (Misc queries) | |||
Exel 2007: Export to pdf ignores page setup with SP2 installed | Excel Discussion (Misc queries) | |||
FORMAT EXCEL WORKBOOK (PAGE SETUP) ALL AT ONCE INSTEAD OF BY PAGE | Excel Discussion (Misc queries) | |||
copy page setup from worksheet to another within workbook | Excel Worksheet Functions | |||
Define Page Setup for whole workbook | Excel Programming |