Daily Worksheet Macro - Make Copy Of Template For Each Day Of Month?
Dec 22, 2011
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.
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)
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 am trying to create a macro that would copy three cell values - Date, Amount, Rate (from Sheet1) and paste them into Sheet2 as values.
In addition, this macro will be run for each business day's file, which only has the 3 values only as of that date; I want Sheet2 to be updated on a daily basis with historical data from past days (when the macro was run) and pasted as values.
For example, if i run the macro today, it should copy Date, Amount & Rate from Sheet1 ('Data' tab in sample.xls) and paste them as values in the next row after yesterday's data in Sheet2 ('Historic data' tab in sample.xls).
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.
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?
I need to make an identical copy with the name I choose from the input box with (wk2). For example: If I enter Tommy in the input box, it would make 2 worksheets:
Dim NewSheetName As String Sheets("NewPerson").Copy After:=Worksheets(Worksheets.Count) NewSheetName = InputBox("What would you like to name this sheet?")
On Error GoTo Err_Trap ActiveWindow.ActiveSheet.Name = NewSheetName
Err_Trap: If Err.Number = 1004 Then Application.DisplayAlerts = False Sheets("NewPerson (2)").Delete Application.DisplayAlerts = True MsgBox ("A new sheet was not created." & Chr(10) & _ "Please choose a different name."), vbInformation, "Sheet Creation Error" Exit Sub End If
I am making a sheet to track the daily cycles of a machine. I need to get the information from the "meter reading" column to populate in the " cycles recorded" cell. I'm very new to this and need a formula to use to track the changes in column D on the attached sheet that will populate changes in cell D4.
I'm trying to make two identical worksheets in a workbook. I would like the second sheet reflect all the changes made in the first one. I thought I would simply use = but it doesn't work correctly if I add or delete rows. If I add a row between row 1 and 2 in the first sheet the formula in row 2 in the second worksheet changes from =sheet1!A2 to =sheet1!A3. The result is the same if I use absolute references which suprised me. I want the formula in row 2 to stay =sheet1!A2 whatever happens on the first sheet.
I don't care about formatting, just data are important.
The reason I want the same data on two worksheets is that the second sheet will contain some more data that should not be visible to everyone. I want to protect the second worksheet and require a password to unhide it.
I have a large set of daily rainfall and evaporation data (see attached sheet) which I would like to sum into monthly data. I have previously been doing this in Access, can anyone show me a quick way to do it in excel?
I am performing a given exercise every day in the month of march. I have the dates all lined up in my spreadsheet, with the sum at the bottom. What I want to do is have another cell that will give me my daily average of performance. For example today is the 2nd and I have performed this exercise 360 times, making my daily average 130 per day. If I performed 200 of the exercise tomorrow I would have a grand total of 560 repetitions making my daily average roughly 186 per day (the day, march 3 divided by the number of repetitions, 560) and so on and forth.
I need a formula that will automatically calculate that daily average as I progress with the month. But I want the calculation to stop at the end (i.e. I don't want it to continue as the year progresses, meaning the divisor keeps going throughout the rest of the year). Basically, I guess, I am looking to fill in the daily repetition and the divisor to increase each day as I do so. My average is to be displayed in cell B36, so today my formula should read (=2/B36) and on March 31st the formula would be (=31/B36).
What I need to do is make a copy of one of the worksheets, however the name of it varies each month.
I have used the macro recorder to make a copy of a worksheet and rename the copy and this is what it gives:
Sheets("Trial August 09").Select Sheets("Trial August 09").Copy Before:=Sheets(1) Sheets("Trial August 09 (2)").Select Sheets("Trial August 09 (2)").Name = "Comparison File" Range("B781").Select
The last line is just clicking away from the renaming of the worksheet.
BUT, the worksheet called "Trial August 09" will next month be "Trial Sept 09" so my recorded macro wouldn't work.
I do have a summary worksheet in this file where the user specifies the month and year - Cell A2 will have August 09 and B2 July 09 for this month and next month it'll be A2 as Sept 09 and B2 as August 09 - can I use these to help do the copy?
Is it possible to make excel template work only on 1 computer by giving any vba password (on lines of license number).
Aim is to ensure it works on only client computer, even if template is shared outside it becomes useless.(We often see clients audit team getting access to templates using it for thier purpose & distributing).
In same spirit what are best practices one can take like protecting formulas with password etc
Although Copy + Paste and Copy to New Row are fairly common questions I find, I am having trouble getting it to fit to what I want.
In the attached example, I have three sheets: 'TBC', 'Bland Anna' and 'Coates Beth'.
I would like to search in columns E and F (no dates will be present until row 5, but as rows 1 through 4 will not contain dates, it may be easier/involve less code to just search the columns) for a specific month, e.g. for this example search for any dates that are in January.
I would then like to copy the data in columns D through G that correlate to said date, and then paste onto sheet TBC on the next available row, not to overwrite.
The macro in question would need to go through every sheet and perform the above action, although there are only 2 sheets in this example, the live workbook will likely contain 100+ sheets containing employee sick leave.
Furthermore, and this is something I'm not sure would be feasible, everytime it copies data, I need it to pull across the name of the individual. This part is slightly more complicated as the names on the sick leave sheets are based at E2 and G2, whereas it needs to copy the names to B and C. Moving the name information from E2 and G2 would make sense to make copying easier, there are other sheets that I have excluded as they are not relevant here, but which are used elsewhere.
If this is not possible, I will have to look into reworking the layout of the information to make it feasible.
I've created an Excel 2007 workbook with about a dozen worksheets that are full of formulas and links to other workbooks, as well as conditional formatting that isn't backwardly compatible with Excel 2003. However, many people who want to see this workbook are still on Excel 2003. I need to recreate an Excel 2003 compatible workbook with the appearance and formatting of the original and have all the formulas and links converted to values. So, the final product will "look" just like the original without the conditional formatting, links or formulas. It will just have the look, colors and values displayed for all cells.
Essentially, I want a macro that will copy however many worksheets (1-n) are in an Excel 2007 workbook to a new Excel 2003 workbook. I'd like the worksheets to retain their label names as well.
I got a macro to copy and paste values onto another tab within my worksheet. I have a lot of data and currently takes about 30 seconds to calculate and paste. Not sure if its an issue with my macro or with my computer (Mac - Excel 2011).
Here is an example of my macro:
Sub SimulateWeek() If Range("AdvanceWeek").Value = "Week 1" Then Range("Week1B").Copy Sheets("Schedule - Results").Range("C2").PasteSpecial Paste:=xlPasteValues
(this continues on until 'ElseIf Range("AdvanceWeek").Value = "Week 31"....etc). So you can see I have the same code repeated 31 times.
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
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.
Is there a way to make the attached worksheet automatically shade out all the Saturdays & Sundays in any given month everytime you change the Month/Year cell at the top of the worksheet, as example? I've tried using the weekday/Weekend formula, but can't quite get it right.
What I am trying to do is to write a macro that will automatically copy six columns from worksheet (Sheet 1) to another worksheet (Sheet 2). i.e. ‘Description of Project’, ‘WBS Code’, ‘Rate’, ‘Employee Name’, ‘Premium’, ‘Invoice’, ‘Status’, ‘Total Cumulative Hours’, ‘Total Cumulative Amount’ from Worksheet (from Sheet 1 to Sheet 2)
The problem arises as I know the names of the columns to be copied in Sheet 1 (as details above) but they can be in any order in sheet 1.
In additional the columns ‘Total Cumulative Hours’, ‘Total Cumulative Amount’ are total columns so when they are copied from ‘Sheet 1’ to ‘Sheet 2’ their values should be copied as opposed to the formulas
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?
I want it to average based on month and year. I have daily data and want to average everything for say January 2000 into one cell and February of 2000 and so on. Column A has date (mm/dd/yy) and column B has data.
I am working in a spreadsheet that contains a bunch of data, it is not limited, and varies. It has some fiels such as: name, date of birth, address, and others. I am interested in creating a column with only the month digit of the date of birth for each row.
I have been working with some code, I am not quite sure how to continue. The following table would be a example that I have of the data, it has only to entry in the column of Date of Birth, but my data range will always vary. I want to get the month and past it in the next cell that is available in this case would be column c or 3... The worksheet name is REP.
Dim Cell As Range ' 1st cell with the posting date Set Cell = Range("A2")