I am creating a large workbook using the same worksheet "form" over and over. How can I use a shortcut or someother method to add the form to a new sheet without having to copy and paste from the last sheet?
View 9 RepliesI need a macro to add a work sheet template to an existing work book, I can add a blank work sheet no prob but it needs to be a template
View 3 Replies View RelatedI'm trying to create a macro that creates new worksheets with buttons on them that have macros attached. I've been able to produce one worksheet with a button and macro, but when the function that copies the code is looped it crashes out of Excel.
I realise that the usual method of doing this would be by templates, but this code will be run on other people's computers so the template wouldn’t exist in the location that i had specified (I think with my limited experience with VBA - Please correct me if I'm wrong). I've also tried to copy the page with the button on, but this takes too long (even with screenupdating = false) as there are many sheets to be created.
This is a stripped down version of the code that I have created for the purpose of this forum. To recap, the function that creates a new page, with button, with macro attached works. It's when it's looped that it crashes out. I've stepped through the program, and it reaches the second message box, then it gives up on life ..........
I have created a template with a Worksheet called "Certificate".
There is a userform that is opened on Auto_Open of this template and
allows a file name and some other parameters to be input.
On pressing the OK button on this form, I want to do the following.
1. Create a new XLS workbook with the file name entered on the form
(this includes the full Windows folder address).
2. Copy the Certificate workbook from the template across to this new
file.
3. Close the original template (maybe closing needs to be done at the
end??) and make the "Certifcate" worksheet in the new workbook the
active worksheet.
4. Using the parameters entered on the userform, change the details in
"Certificate" (this code is already written but not set up for the new
workbook - currently it updates the template "Certificate" worksheet)
5. Save the new excel workbook with the changes.
I'm trying to find a simple Excel template to add my sales invoices together for a year...just so I know what I've earnt...nothing complicated.
The Columns I need are;
date - invoice no. - customer - labour - materials - total
I'd like it to add together labour and materials to the total box ( or just labour in the total box if no materials for that job.)
Then each invoice adding up as I add more with subtotal of labour and materials then a grand total.
I have a template file which has a reference to a cell on a sheet in another workbook.
I need to copy this template to 250 workbooks. However, everytime I copy this template sheet into a workbook, it updates the reference to the template name!
Is there an absolute reference I can use for the sheetname?
Example: Workbook Template - Sheet1 - Cell A1 = Value
Workbook Template - Sheet2 - Cell A1 = Sheet1!$A$1
So for so good, but when I copy Sheet2 to a new workbook, I need to have the exact same reference; ie. Sheet1$A$1 and NOT = ['C:Workbook template']Sheet1!$a$1
We have a workbook that we create each month that has one worksheet per day of the month, labeled 12_01_2011, 12_02_2011, 12_13_2011...etc. The pages are an empty template with formulas and fields in place that we simply copy and paste the results of an SQL query into. Presently, we are copying the page manually several times over, and then manually renaming the pages with the new dates for the upcoming month.
So, here is my question. Macro that I might use that would:
1) Make a copy of the template for each day of the month.
2) Label each page in sequence with the dates for the upcoming month.
how to create a copy of my employee attendance template. Is there a way to create a copy of the template by entering an employee name in the "name" cell of the template and it automatically renames the sheet that employee name and also saves or recopies the template for use with the next employee?
View 8 Replies View RelatedI've created a single worksheet that is essentially a "dashboard" type of report that displays data for a single employee/person. This worksheet (let's assume it's my "template") contains formulae that is driven by a single lookup cell (in this case, the employee's ID, in cell, for example, A1).
This "dashboard" report (for just one individual) needs to be duplicated for the entire employee population (let's say ~100 people); this employee population is stored as a list in a separate worksheet, in a single column.
Is there a vba solution that will copy my template and populate the cell A1 (of each duplicated template worksheet) with the the employee IDs - of course, it would be ideal if the solution can search in my employee ID list and continue down until there are no more IDs and create the same number of worksheets as there are IDs?
Because the macro is creating numerous worksheets, is it possible to name the worksheets based off of an adjacent value (in the same sheet as the IDs) to each respective ID (I'm thinking of a concatenation of the employees' IDs and names)?
One major wrinkle in this process: the above process would be ideal if the "template" worksheet won't change (i.e. no changes to formatting, no additional data elements, etc.) However, it's likely that the client will want to add/remove/change items/elements to the "template" dashboard. Assuming, at this point, all 100 worksheets for each individual/employee have been created, it would be a huge pain to manually add/remove elements from each sheet. Is there another macro that would delete ALL employee sheets? That way, I can re-modify my single template and just run the original macro to re-create my duplicate sheets (but populated with different IDs) again.
I have what is probably a simple request to all the VBA experts out there, and simply want to take a template sheet in a workbook and copy it across to 100 sheets numbered/named 101 -200, and add the name of each sheet as a text reference, e.g. sheet name 101 will have a cell within the worksheet that refers to sheet 101.
I have attached a simplified spreadsheet which indicates what i'm trying to achieve.
I Have a number(30+) of excel files based off of one template. I have since updated the template, thus making the the old ones obsolete. Is there a way to update the older files to use the new template without having to copy and paste the addition's row by row column by column?
None of the additions to the template will change the placement of existing data, nor will it change the data itself. It will however add Values to cells that were previously empty.
Hints, Tricks, or Ideas?
1)
I created a one-sheet template, and a new workbook from that sheet. When in that new workbook I go Insert>Sheet>Other and select the aforementioned one-sheet template, Excel crashes, or if not, it adds the new sheet, but then no longer saves the workbook and starts producing error messages (like: "An unexpected error has ocurred. AutoRecover has been disabled for this session of Excel.").
1a)
Now, the complication is that this is working when I do a model operation with a generic template sheet. So I checked the template that I actually want to form sheets after, and Excel finds no errors, nor does it's name contain any unusual characters. What could Excel prevent from working with a template like this?
2)
How do I edit a template? The only way I can find is to manually find the spot in finder, open, and save with the same name. But: if I do that, the documents basing on that template don't change accordingly. If they're intended not to, the whole template procedure makes no sense. I could then just as well copy a file. — I've been searching for tutorials on that, but google doesn't even return a single result on Excel "edit template". Therefor my very basic question here.
I've attached 2 test files, one is the database master file containing the projects (each row represents a project, unique reference number in column A) and the other is the blank template file i'm hoping to export data into and then save down with the naming convention "column A_column B.xlsx"
Kept the test files simple but would need to modify any code to apply to much larger database consisting of many more fields etc.
Master.xlsx‎
Template.xlsx‎
I have a list of invoice #'s on a sheet named "Temp Sheet".
I have a VBA macro that created a new tab for each entry and named it the invoice #. So basically the vba code created a new tab ( based on the number of invoice #'s on my list ), and named each tab an invoice number. So if I had a list of 10 invoice #'s, named S1-S10, the vba code created 10 tabs, named S-1, S-2,. S-3.....
Now to my question.
I have a template sheet I want to copy from ( "Template" ), and select any sheet that starts with "S", and copy/paste this template to.
I have a template file for ordering trafolyte and steel plates. I have added macros to this template file. The existing macros do the following (shortly described):
Macro 1: clears order
Macro 2: update order date + send a read only file to the supplier of plates + save a read only copy of the file into one of three folders acc to info in one of the cells.
It's the Macro 2 I want to edit.
I want to add a "function" which copy a selection of data.column A to N from row 12 to 548 but only the rows where there is a value in column A.
Row 1 to 11 includes standard order info and Macro buttons.
Row 11 includes the heading for order data.
For everytime someone click on the Macro 2 button in the template file, I want the selection to be paste into the first "available" row in a "Total list" file.
The "Total list" file may have to be open (or a function to open, paste selection and then close the "Total list" file may be added)
File and Folder info:
To simplify suggestions, the following file and path info can be used (I can change to the correct later):
Template file name: template_order.xlsm
Template file location: \servershared emplate
Total list file name: total_list.xlsx
Total list file location: \servershared otal
Selection info:
The template file exists of a "general order info area" A1:N10
The column heading for order data is located at A11:N11
The selection to be copied is A12:N550 - But only rows where column A includes data (not empty).
(If the spesific order consists of 14 plates than there will be item no 1-14 in column A and I then I want to copy A12:N25 (row 25 will be item 14).
When I try to use record macro it looks like it only records what's happening in the template file - It doesn't record the pasting in the total list.
I need to write a VBA code which will read the CSV files from a folder by opening the CSV files , then copy the contents of csv file into another EXCEL template already present in the same xls template.
The XLS template has tabs named like "UAT2D" and i want the csv file data to paste in the particular xls tab which is blank initially. But what is happening my code open the required CSV , copy the data and insert the CSV into the xls template by creating the new tab. see the code A below
We have huge restrictions on hours, and to avoid inputting, then revising the schedule into my companies scheduling module, then having to change it again when the boss looks it over, it's just confusing.
Anyway, I'll get to the point, I want to add the hours up in a row of cells for the week sunday through saturday. Like so:
1100-2100 | 1100-2100 | OFF | 1100-2100 etc... I want it to add these cells hours up and have the total end up in the 8th cell.
Is this possible? I am a new excel user, or at least, setting up my own sheet.
I have a workbook with several worksheets and numerous userforms, commandbuttons and macro's. I want to prevent people from saving over the workbook once they have used it, but allow save as using a different workbook name.
I tried saving the book as .xlt, but then when I reopen it and make material changes, it lets me save over it. I have't worked with templated before, is there something I am doing wrong?
I'm running a macro in a template and trying to save the results to a different folder.
I want the file name to be blank in the Saveas Text box of the UI also. Unfortunatley my code is bugging out & I can't work out why.
ActiveWorkbook.SaveAs Filename:="C:", FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
The save to path does change so I want to be able to change the save location.
I made a thread about this last week but I don't think I was very clear about what wanted so now I'm going to describe it more in detail.
I work at a place that takes care of boats and also offers to store them during the winter. Now I have created a template which consists of one work order and one invoice so when a new customer arrives I open up my template and fill out the work order. But then when the customer comes back next year and wants something else done with his boat I need a new work order but in the same document. So I need to add another work order as a new sheet. The only way I can think of now is to copy an old work order and then paste it but that would take too long time to do every time and I would also have to change the width of all columns. So my question is: Is there some easier way to do this? Basically what I want is a quick way to add either a work order or an invoice as a new sheet.
Link to my old thread: http://www.mrexcel.com/forum/showthread.php?t=430633
I do not want the user to be able to change the workbook.
I will put a small macro to export any specific sheet which the user can change into to a new workbook.
For the main file (which the user can interact with, but is not allowed to overwrite), what is the concensus- make it Readonly or save as a template?
I think the template option looks quite fancy. I also think, but I'm not sure, that a ReadOnly can be undone by the user?
I'm leaning towards template format, but I don't really know it. Are there any significant disadvantages?
I recently deployed a single-page spreadsheet intended to be added to existing workbooks (it generates a hiearchical view of pricing in a WBS structure), and it includes some UDFs and subs.
I was hoping I could save it as a tempate, but when inserted as a template, it didn't bring along its code module. I ended up instructing someone to copy the sheet to the intended workbook, and had them open the VBE to drag the module over. There is surely a better way!
I would prefer not to train people to use add-ins or mess with Personal.xls.
Somehow I have changed a default setting that I cannot figure out how to change back. I have various templates setup in a "Forms" folder. Normally I go to My Documents, find the template file and double click on it, and the appropriate program (usually either Excel or Word) starts and opens a copy of the template as a document. What happens now is that the program starts and the template itself opens. I have tried going to My Documents and right clicking on the file and what appears to be happening is that the default (highlighted) option is "Open" instead of "New" which is what the default used to be. How do I change it back?--
View 7 Replies View RelatedI have been googling for a excel sheet, but just seem to find the right one. Then I found this great looking forum so I know someone here must beable to help me.
This is what I need:
I host an event twice a month I have cost that change every two weeks and would like a nice looking sheet that I can puch the numbers in to then it will tell me how much I need to charge each guest. I have cost that are always the same and other that are not. If this isnt clear here is an example...
Week1 week2 Month Year
Milk 2.50 2.50 5.00 130.00
Eggs .75 .75 1.50 39.00
Vodka 2.50 2.50 5.00 130.00
Lawn Darts 10.25 10.25 20.50 530.00
Host 75.00 75.00 150.00 3900.00
room 125.00 125.00 250.00 6500.00
total 216 216 432 11232.00
Number of guest week1 week 2 month year
25 30 55 55
cost of each guest 8.64 7.20
I have a template (attached) that needs to be populated. It's objective is to determine the QUARTER and YTD based on MONTH. For example, there's a M shown for the month Jul, Q also shown for the month Jul (since current month is Jul, current quarter month is only Jul and not Jul, Aug & Sep), and all months up to the current month shows Y for the year row. The above is currently done manually. I need a formula that will fill in the Q and Y's automatically based on the M. Is it possible?
View 3 Replies View RelatedI have created a template that I use to determine interest calculations (it is based on simple daily interest, but monthly compounding and adjusts for any variations in interest rates during the month), and it seems to work fine so far.
However, I am trying to make it better and would like to eliminate as much manual input as possible. I have attached an example of the template to help explain what I would like to achieve
The Running Balance is dependant on column "J" - Interest for the Month. At the moment, I am manually adding up interest accumulated for the month, however if I add one cell too high or miss a cell when adding, it can lead to compounded problems further down. So in short I am looking for a method that would automatically calculate "interest for the month" by adding all the amounts in column "I" (Interest Daily) at month end for that month only (dates are entered in Column B)
In the attached template, grey areas indicate capital movements and/or interest rate changes that may occur during a month (ie movement in the month in question that affects the daily interest calculated)
I hope I've covered everything!
Any help in this regard comes most appreciated. If anything is unclear, I will do my best to explain what I am trying to achieve in greater detail.
i have 2 sheets, one is a database sort. and the other sheet is a template tat i had design. i have inserted a filter to the database sheet that i wan to extract info from my template (diff catergory). but shows nothing,
I am trying to save a chart as a template but when i apply it to another chart it doesn;t have all the original formatting i did. I doesn't retain the gradient fills on the series, the fact that i've changed one series to line on a col chart??
I have a workbook that has a total of 128 pages. The problem is that a lot of those pages won't be used. Basically I have a sheet that needs to be filled out for each day up to around 30 days, sometimes more, most times less.
Currently I have created all the sheets that I will typically need (31 daily's, 31 daily printable reports).
Data is entered into the daily (and other spots) and then with the use of formulas transferred to the report sheet which is hidden and then printable with the use of macros.
I also need to withdraw some of this information (CSV File) to be able to populate a database.
Is it possible to have my "daily" page as a template and then create the subsequent pages as I need them? (this would have to be done with a bunch of other report pages as well). For ease of use I would need all the formulas etc that I currently have to be able to be "created" as well. As an example, there is a running total of costs associated with the "daily" pages that would need to be carried forward.
I have a template and several spreadsheets based on this template, and when changes are made to the template they need to be manually merged into each spreadsheet, a very painstaking, time consuming, and error prone process. Changes are mostly added rows, data tables, and macro (coding) changes. Surely there must be a better way of doing this. There must be some way of automating at least part of this process, or so I would think. I know that changes to a workbook can be tracked, but I don't shows changes to code, and I don't know if it is true for templates as well (but I suspect that it is); at least this would show me what changes to the template had been made, but maybe not changes to the code, but this is only a small part of the problem.
View 1 Replies View Related