Default Workbook
How to add header/footer to the default workbook that appears when you open
Excel? Eg. want to add file name and date as footers to all files created without having to remember to add them for each workbook created. |
Default Workbook
Open a new workbook. Customize as you wish. To set a footer/header for each
sheet, select a sheet then right-click and "select all sheets". Enter your footer on the active sheet and will be done to all. Ungroup sheets when done. FileSave As Type: scroll down to Excel Template(*.XLT) and select. Name your workbook "BOOK"(no quotes). Excel will add the .XLT to save as BOOK.XLT. Store this workbook in the XLSTART folder usually located at........ C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART This will be the default workbook for FileNew or the Toolbar button FileNew or CTRL + n WARNING................Do not use FileNew...Blank Workbook or you will get the Excel default workbook. NOTE: Existing workbooks are not affected by these settings. You can also open a new workbook and delete all but one sheet. Customize as you wish then save this as SHEET.XLT in XLSTART folder also. It now becomes the default InsertSheet. More can be found on this in Help under "templates"(no quotes). Gord Dibben Excel MVP On Fri, 25 Jan 2008 12:06:03 -0800, Insurance Daddy-O <Insurance wrote: How to add header/footer to the default workbook that appears when you open Excel? Eg. want to add file name and date as footers to all files created without having to remember to add them for each workbook created. |
Default Workbook
I would just like clarification. If we were to have a branded workbook
template available for our consultants to download and use at a client site, in order for new sheets to be inserted into the template with our branding in the header/footer, they would need to download and save SHEET.XLT into the XLSTART folder of whatever machine they're located at? Is there any other way to have that branded header/footer show up in newly inserted sheets without overwriting the SHEET.XLT on the machine they're at? Our consultants often get assigned a client owned computer when they are at client sites. "Gord Dibben" wrote: You can also open a new workbook and delete all but one sheet. Customize as you wish then save this as SHEET.XLT in XLSTART folder also. It now becomes the default InsertSheet. |
Default Workbook
Yes, you would have to have your personalized SHEET.XLT in the Xlstart folder.
Have you thought about a macro in the Branded Workbook Template to insert a new Excel default sheet and code to add the Header/Footer customization to that newly inserted sheet? Your consultants would then only need the Branded Workbook Template. Any workbook based upon the Template would have the macro. Gord On Mon, 11 Feb 2008 14:21:23 -0800, Pugs wrote: I would just like clarification. If we were to have a branded workbook template available for our consultants to download and use at a client site, in order for new sheets to be inserted into the template with our branding in the header/footer, they would need to download and save SHEET.XLT into the XLSTART folder of whatever machine they're located at? Is there any other way to have that branded header/footer show up in newly inserted sheets without overwriting the SHEET.XLT on the machine they're at? Our consultants often get assigned a client owned computer when they are at client sites. "Gord Dibben" wrote: You can also open a new workbook and delete all but one sheet. Customize as you wish then save this as SHEET.XLT in XLSTART folder also. It now becomes the default InsertSheet. |
Default Workbook
Gord,
Thanks for the quick reply. I have thought about using macros, but unfortunately that's a little beyond my knowledge level. At least one of the consultants has to have that skill set, though, so I'll be checking with them. Thanks again. "Gord Dibben" wrote: Yes, you would have to have your personalized SHEET.XLT in the Xlstart folder. Have you thought about a macro in the Branded Workbook Template to insert a new Excel default sheet and code to add the Header/Footer customization to that newly inserted sheet? Your consultants would then only need the Branded Workbook Template. Any workbook based upon the Template would have the macro. Gord On Mon, 11 Feb 2008 14:21:23 -0800, Pugs wrote: I would just like clarification. If we were to have a branded workbook template available for our consultants to download and use at a client site, in order for new sheets to be inserted into the template with our branding in the header/footer, they would need to download and save SHEET.XLT into the XLSTART folder of whatever machine they're located at? Is there any other way to have that branded header/footer show up in newly inserted sheets without overwriting the SHEET.XLT on the machine they're at? Our consultants often get assigned a client owned computer when they are at client sites. "Gord Dibben" wrote: You can also open a new workbook and delete all but one sheet. Customize as you wish then save this as SHEET.XLT in XLSTART folder also. It now becomes the default InsertSheet. |
Default Workbook
Okay,
I've figured out the how to create the macro. The problem that I'm having now is that in the custome header and custom footer we have an image with our company logo (header) and an image with our company slogan (footer). I'm having difficulty figuring out how to have the macro add the images. "Pugs" wrote: Gord, Thanks for the quick reply. I have thought about using macros, but unfortunately that's a little beyond my knowledge level. At least one of the consultants has to have that skill set, though, so I'll be checking with them. Thanks again. "Gord Dibben" wrote: Yes, you would have to have your personalized SHEET.XLT in the Xlstart folder. Have you thought about a macro in the Branded Workbook Template to insert a new Excel default sheet and code to add the Header/Footer customization to that newly inserted sheet? Your consultants would then only need the Branded Workbook Template. Any workbook based upon the Template would have the macro. Gord On Mon, 11 Feb 2008 14:21:23 -0800, Pugs wrote: I would just like clarification. If we were to have a branded workbook template available for our consultants to download and use at a client site, in order for new sheets to be inserted into the template with our branding in the header/footer, they would need to download and save SHEET.XLT into the XLSTART folder of whatever machine they're located at? Is there any other way to have that branded header/footer show up in newly inserted sheets without overwriting the SHEET.XLT on the machine they're at? Our consultants often get assigned a client owned computer when they are at client sites. "Gord Dibben" wrote: You can also open a new workbook and delete all but one sheet. Customize as you wish then save this as SHEET.XLT in XLSTART folder also. It now becomes the default InsertSheet. |
Default Workbook
Have you thought of including a worksheet (hidden) in that workbook and
providing a macro that would create a new (visible) worksheet based on that hidden sheet? Pugs wrote: I would just like clarification. If we were to have a branded workbook template available for our consultants to download and use at a client site, in order for new sheets to be inserted into the template with our branding in the header/footer, they would need to download and save SHEET.XLT into the XLSTART folder of whatever machine they're located at? Is there any other way to have that branded header/footer show up in newly inserted sheets without overwriting the SHEET.XLT on the machine they're at? Our consultants often get assigned a client owned computer when they are at client sites. "Gord Dibben" wrote: You can also open a new workbook and delete all but one sheet. Customize as you wish then save this as SHEET.XLT in XLSTART folder also. It now becomes the default InsertSheet. -- Dave Peterson |
Default Workbook
Dave,
This is an excellent solution! I'm playing with it right now. My only complaint so far is I'm trying to figure out how to have newly created sheet moved to be the last sheet. With the sheet.copy function, you have to specify a sheet for it to be inserted before or after. If you tell it to insert after Sheet3 (let's say that this is the last sheet in the book), it will always insert after Sheet3. So you insert one sheet new sheet and it inserts after Sheet3. You insert another sheet and it inserts after Sheet3 again, but before the one the previously inserted. Did that make sense? "Dave Peterson" wrote: Have you thought of including a worksheet (hidden) in that workbook and providing a macro that would create a new (visible) worksheet based on that hidden sheet? |
Default Workbook
Nevermind,
I've sorted it out. Right now, my macros look like: Sub NewSheet() Sheets("Sheet").Select Sheets("Sheet").Copy After:=Sheets(Sheets.Count) End Sub Sub HideSheetTemplate() Sheet1.Visible = xlVeryHidden End Sub Sub ShowSheetTemplate() Sheet1.Visible = True End Sub They're working beautifully. See! Sometimes us non-technical Project Managers can accomplish something slightly technical! "Pugs" wrote: Dave, This is an excellent solution! I'm playing with it right now. My only complaint so far is I'm trying to figure out how to have newly created sheet moved to be the last sheet. With the sheet.copy function, you have to specify a sheet for it to be inserted before or after. If you tell it to insert after Sheet3 (let's say that this is the last sheet in the book), it will always insert after Sheet3. So you insert one sheet new sheet and it inserts after Sheet3. You insert another sheet and it inserts after Sheet3 again, but before the one the previously inserted. Did that make sense? |
Default Workbook
Excellent and I am happy Dave came up with a better solution with the hidden
worksheet. Gord On Tue, 12 Feb 2008 08:48:09 -0800, Pugs wrote: Nevermind, I've sorted it out. Right now, my macros look like: Sub NewSheet() Sheets("Sheet").Select Sheets("Sheet").Copy After:=Sheets(Sheets.Count) End Sub Sub HideSheetTemplate() Sheet1.Visible = xlVeryHidden End Sub Sub ShowSheetTemplate() Sheet1.Visible = True End Sub They're working beautifully. See! Sometimes us non-technical Project Managers can accomplish something slightly technical! "Pugs" wrote: Dave, This is an excellent solution! I'm playing with it right now. My only complaint so far is I'm trying to figure out how to have newly created sheet moved to be the last sheet. With the sheet.copy function, you have to specify a sheet for it to be inserted before or after. If you tell it to insert after Sheet3 (let's say that this is the last sheet in the book), it will always insert after Sheet3. So you insert one sheet new sheet and it inserts after Sheet3. You insert another sheet and it inserts after Sheet3 again, but before the one the previously inserted. Did that make sense? |
Default Workbook
Hey,
I appreciate your input and responding so quickly. That sent me down the macro path so Dave's suggestion dovetailed nicely. In case it will help anyone else, I had to modify the macro slightly. I found that when the worksheet is hidden, the 'Sheets("Sheet").Copy After:=Sheets(Sheets.Count)' function errors out because it cannot find the hidden sheet. To modify it, I've added code to un-hide the sheet before copying it, and then hiding it again after copying it. The scripts now look like this: Sub NewSheet() Sheet1.Visible = True Sheets("Sheet").Select Sheets("Sheet").Copy After:=Sheets(Sheets.Count) Sheet1.Visible = xlVeryHidden End Sub Sub HideSheetTemplate() Sheet1.Visible = xlVeryHidden End Sub Sub ShowSheetTemplate() Sheet1.Visible = True End Sub "Gord Dibben" wrote: Excellent and I am happy Dave came up with a better solution with the hidden worksheet. Gord |
Default Workbook
If you add:
application.screenupdating = false your code to unhide, copy, and hide application.screenupdating = true You won't see the flickering. And you could drop this line: Sheets("Sheet").Select You didn't do anything once you selected it. Pugs wrote: Hey, I appreciate your input and responding so quickly. That sent me down the macro path so Dave's suggestion dovetailed nicely. In case it will help anyone else, I had to modify the macro slightly. I found that when the worksheet is hidden, the 'Sheets("Sheet").Copy After:=Sheets(Sheets.Count)' function errors out because it cannot find the hidden sheet. To modify it, I've added code to un-hide the sheet before copying it, and then hiding it again after copying it. The scripts now look like this: Sub NewSheet() Sheet1.Visible = True Sheets("Sheet").Select Sheets("Sheet").Copy After:=Sheets(Sheets.Count) Sheet1.Visible = xlVeryHidden End Sub Sub HideSheetTemplate() Sheet1.Visible = xlVeryHidden End Sub Sub ShowSheetTemplate() Sheet1.Visible = True End Sub "Gord Dibben" wrote: Excellent and I am happy Dave came up with a better solution with the hidden worksheet. Gord -- Dave Peterson |
Default Workbook
Roger that.
Thank you! "Dave Peterson" wrote: If you add: application.screenupdating = false your code to unhide, copy, and hide application.screenupdating = true You won't see the flickering. And you could drop this line: Sheets("Sheet").Select You didn't do anything once you selected it. |
All times are GMT +1. The time now is 03:54 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com