how do i use worksheetfunction save to save a particular sheet everyday at a certain time e.g. 5.30pm to a particular folder.
Also the name of the file will change with the day.. e.g. 22_Apr_07.xls for today.
And to also refresh the page / recalc before saving at 5.30pm...
I have a macro that opens up a file, copies info from the file then closes it and the macro continues. I don't want the user to have to click on any 'do you want to save changes' prompts.
View 5 Replies View RelatedI have created a macro which copies the content of one of the sheets of a workbook, on to a new workbook and then saves the new workbook as 'temp.xls' on the desktop.
View 5 Replies View RelatedI need to restrict printing & saving functionality on a excel spreadsheet that is shared with a large group of people.
I was also hoping for the restricting save part, to do an AD lookup to see if they are a member of group "X", if they are then allow the save, if they are not then deny the save.
I have a spreadsheet that contains product images in cells in column A. In the matching cell in column B there are product codes. I would like to be able to "batch/bulk" save these images and label them by the corresponding product.
Is this possible. Or is there a different way around this issue.
I have two spreadsheets, one gives me the beginning and end of civil twilight as a measure of day vs. night. The spreadsheet has Date/Time in the first column, and the value 45 in the 2nd column when it is night. The second spreadsheet has also 2 columns with date/time and body temperatures of a squirrel. I want to get basic statistics (mean and standard deviation) of the squirrel's nocturnal body temperature, that is for times when it is night (value 45). The tricky part is that Date/Time of both spreadsheets are different. The procedure has to recognize that the date/time of body temperature lies between the beginning and end of the value 45 blocks of the first spreadsheet.
files: twilight sheet squirrel temperature
I have a workbook that has twenty data sheets. The user presses a button that copies they're specified sheets to a new workbook and prompts them with the SaveAs Dialog. How do I code it so that if they press the CANCEL button on the SaveAs Dialog it closes the created spreadsheet without saving it?
View 6 Replies View RelatedI work with alot of reports that are pulled directly from an application.
On each report i need to 'Alt=' at each cost center so that all manual changes are picked up.
However as you can imagine with thousands of rows this could take a while and be very tedious. Is there a quick way to do it?
Subtotals will not work because the costcenters will appear in the same column as the employee numbers.
I import lots of data into a spreadsheet then have to manually format it which takes hours, I want code to automate it.
Example: I have data such as:
No-----Name-----Date---Price----Cost
10-----Dino------etc-----1--------2
10-----Dino------etc-----3-------5
12-----Bino------etc-----4--------6
13-----Rino------etc------5-------5
13-----Rino-----etc-------6--------6
I want it looking like this:
No-----Name-----Date---Price----Cost
10-----Dino------etc-----1--------2
10-----Dino------etc-----3-------5
shaded cells to here ishh 4-------7
No-----Name-----Date---Price----Cost
12-----Bino------etc-----4--------6
shaded cells to here ishh 4---------6
No-----Name-----Date---Price----Cost
13-----Rino------etc------5-------5
13-----Rino-----etc-------6--------6
shaded cells to here ishh 11-------11
The difficulty is due to the fact that the number in No column varies, for example there may be 2 rows of 10 or 4 rows of 10. What is the quickest way to format it all too, so have a border and font size etc all the same?
At present I have to manually insert blank rows between the different numbers in the No column, copy column headings, then manually sum up Price and Cost, then insert borders. But for hundreds of records.
I found this code to insert 3 blank rows manually for me when the numbers in No column are different:
I need a formula or script that accumulates data that a person enters into a particular cell.
[INPUT] a6=enter service requests closed today
[OUTPUT] b6=Service requests closed last month
[OUTPUT] c6=Service requests closed this month
I want to make this as simple as possible and the person enters her data in the same cell every day (a6). The script or formula should (in the background) then take todays total and and add it to the accumulated total and show the outputs in b6 and c6.
If a person enters data multiple times on any given day, the script or formula should recognize only the last entered number for that day. This will control user errors in entering.
I thought about just entering the raw data in a separate spreadseet and using 1 cell for each day, but then a person would have to enter totals on one sheet and look at the results on another seet. not good
I also thought about taking the above solution and combining them on one sheet. this would work, I guess, but I really need a sleek lean sheet that they add to the same cell each day.Also there is additional data that I still need to show on the screen and I really hope I do not need to move this data.
I have trying to automate this macro so I don't have to edit it everyday
the problem being my data grows by one column everyday
Range("A1:GP1").Select
I have a template in which i resave everyday to ddmmyy, i then remove the following sheets "raw_data","Exchange","Advanced Filter", i also remove all the formulas in my other sheets to values. I would like to have a macro that will do all of this for me.
the file is saved in,
C:RecstatsOver2day2009Nov
but would like the year and and month to be generic and not hardcoded to say 2009 or Nov.
What i want is for my user to click the save button linked to a macro that saves my file as Order0000. If there is a file already called this i want it to change the name to Order00001 and so on for Order00002.
Is this possible and if so what VBA code
See the attachment. When the macro runs. The workbook is saved in a folder with a new serial number. That is fine but I need to debugging the code. Since I need to save the WB name with the time and date to be also picked and named when saving. Refer to cell B1.
View 9 Replies View RelatedI have created a userform which is initiated off a button in her spreadsheet, where if she enters an order number, it will display the values from the row containing that order number. I have made it so she can load from the excel sheet the values into textboxes, where she can change values then save them back into the excel sheet.
I have also include the ability to insert a new row at the end for a new order.
Everything works fine except the date values and I'm a bit stumped, I have searched but can't find anything that helps (I thought DateSerial helped but it didn't).
I've had two problems. the first was that the day and month swapped, I could load 1st Mar 2007 from the excel sheet and when stored back it changed to 3rd Jan 2007. This I presume to be due to US and UK date formats, I am in the UK.
I thought I had fixed this by changing:
oDisplay.Value = vStoreArea .............................
I have a list of reports for many countries, I need to make sure each report is there for every country, except for weekend days.
report check.xlsx
Is it possible to use a macro like the one below, and have the second location save with the date / time stamp in the name? This is what I need to accomplish: I have a few folks that need to make changes to a master spreadsheet daily. The spreadsheet is in a Network share that when modified and saved, will save to that network share, but also locally. Can the second location have the date/time stamp added to the name of the file so that I know when the changes were made and have an audit trail ? This will also keep a copy of the last modified file in case the spreadsheet is corrupted. Here is what I have for saving to 2 locations:
View 3 Replies View RelatedIs there a way to automate the date and time in one cell upon saving? I have seen some posts elsewhere upon this subject, but here is the twist:
this is a shared workbook with each tab allocated for a different person. Each person needs to update their worksheet and have the date and time update upon saving. The date and time would appear in the same cell for each sheet. I am fairly certain that this cannot be done with an Excel formula, so I would appreciate if anybody had any VBA suggestions. (I am just starting to learn VBA
I am trying to create a code so that when the save button is hit, it puts the current date and time into a given cell before it saves. This way I will know the last time something was updated and saved.
View 9 Replies View RelatedI have a workbook with several worsheets in it. I wish to have the first worksheet as a list that will be hyperlinked to the other worksheets within the documant.
Is it possible to save the workbook so that it will open at the first worksheet every time irrespective of what worksheet you were on prior to clicking save.
In column A of a database I've calculated workdays for all year (excluding weekends and public holidays).
Every day, I update the numerical data of the database, with the facts of previous day.
In another sheet I've made some monthly, quarterly etc., reports for that data.
What I want, is to have:
1) a report every day, with the sum of specific data (say that in column D), for a period starting the first workday of current month and ending the previous workday of today.
2) the total workdays days for that period.
Is it possible to allow access to multiple users on one spreadsheet but they can only access one tab at a time each?
prevent multiple changes for the same thing.
How do I format a spreadsheet set up as a time card entry log so that I can enter 0543 and it appears as 05:43 AM and/or enter 1653 and it appears as 4:53 PM.
I have it formatted right now to show that data but I have to enter it as 05:43 and it appears as 5:43 AM which is what I want and the in/out times calculate with an end result of hours worked.
I want to do it without having to enter the colon.
I am trying to create a spreadsheet that will show vacation time; accrued, taken, leftover. There are different accrual rates for years of service, and caps on carryover.
I have been trying to figure this out, however the accruals for employees over 1 year are higher than they should be.
Breakdowns are as follows:
1-2 years - 1 week vacation - 40 hours eligible for carryover each year
3-4 years - 2 weeks vacation - 80 hours eligible for carryover each year
5-9 years - 3 weeks vacation - 120 hours eligible for carryover each year
10+ year - 4 weeks vacation - 160 hours eligible for carryover each year
I've searched a few of the posts here about freebies in terms of adding a trial period to your spreadsheet. I'm just starting to get into VBA programming (beginnner), but I was wondering if anyone here could suggest actual programs or add-ins that I could buy that would allow me to create a trial version of my spreadsheet? either by allowing the user to use it for a few days or to allow the user to use it for a specifed # of times.
I'm willing to pay for this, but I can't afford those expensive Licensing/Copy protection programs....I need a simple solution in between $20 and $60....
I have a couple shreadsheets I have created, one is for Purchase order's and has 4 different sheets. with one sheet holding addresses linked a drop down menu in the first sheet, all in the same workbook. Also there is a macro within this sheet to print to a specific printer, and a specific number of copies when CTRL p is pressed. The total file size is approx 9 mb. Opening this file takes approx 30 to 45 seconds. There are no links to any outside files, only links within the workbook itself.
The second File I have recently started having problems with (Our Quotation log) is A workbook containing 3 sheets, 2 sheets independant and one sheet linked to the second that Summarizes the first and also has cels to enter information on the person the quotation has come from. I have this sheet formatted as a table so I can sort by name etc when doing followups.
This workbook also takes approx 30 to 45 seconds to open, and on occasion longer. All workbooks are stored on my computer. The PO workbook has had the problem since I created it, although the Quotation log workbook has only had the issue this week.
I need to calculate how much time I've got left before the earliest order needs to be despatched (it might be 2 or more on every day). This wouldn't be a problem if orders would be placed daily. But for every item it varies. For example: for "X" product there is an order in 3 days time to be despatched at 19:00. I have built a live clock in the spreadsheet but I can't work out the formula.
P.S. Also I need two time formats, first - days(text) hh:mm second - just a numeric value that can be formated as [hh:mm].
I ran my macro on a spreadsheet and it was fine.
But when I ran it from another spreadsheet I received the below error.
i.e. I placed the macro in book1.xls & from there open book2.xls.
ERROR:
HTML Run-time error '1004':
Application-defined or object-defined error
The error pops up when the macro reach the line "Rows ("3:3").select".
Having a problem with ComboBox on spreadsheet, not Userform. When the Sheet1 is first opened the ComboBox is not populated, if I click on Sheet2 and then Sheet1 ComboBox is populated and works fine. It appears that I need some additional coding.
View 3 Replies View RelatedNeed the code which would allow me to create a macro where when data is added into an excel sheet, it automatically adds in the date and the time?
View 2 Replies View Related