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.
I need to retrieve 6 digits (It's really the date in "ddmmyy" format) from a string. If the strings were to follow a standardized format it would be a simple matter of using left, right or mid. However, I have a couple of different scenarios.
The "ddmmyy" I'm looking for may be sandwiched between other characters, separated by a space or remain at the end of the string. Is there a way to sniff out the 6 digits in the string regardless of it's position?
Here are some examples:
1) ABC COMPANY 3.6% 100111-BLAH
2) MCCAIN JOHN4.9%120509
3) ROYAL BK SCOTLAND 4.03% 160309
4) IBRD 8% 090210 BNPHFN00002 HKD5) AUST & NZ BANKING 4.23%161109-EMTN
I have an order tracking sheet that has start times in column B, end times in column C and the processing time in column A. The start and end times are recorded in ddmmyy hhmm format (9/1/09 13:30).
On another sheet I have dates listed for each workday. Next to these dates I am wanting a formula/macro that will traverse through column B and provide the number of orders and average processing time that match this. I also plan on recording the shortest and longest processing time next to these.
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
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 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
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.
I'm trying to make a macro check if a file has been saved (ever). If so I want the macro to do a regular save (with already esatablished filename and location) before it proceeds with the rest of the macro. If the file hasnt been saved (if it runs from a new workbook) then I want it to pop up the save.as dialog, so that the user can choose the name and location of the file before the macro continues .
The macro itself is saved in personal.xlsx.
I'm trying to have a macro that opens the save as window, places the name in cell f5 and then allows you to save the workbook with cell f5 as the filename. I have managed to get the save as window to come up and the cell f5 as the name, but when I press save is doesn't. Here is what I have (also, is there a way to direct this to a specific folder).
View 4 Replies View RelatedI have a macro that copy one sheet of the Active workbook and sends it via email.
I need to add a code in this sheet so when one opens it from the email, with a command button to be able to save the file to specific, fixed folder on the local network with it’s original file name.
My spreadsheet has 32 worksheets, I've recorded a macro to do the job "save, save as with specific name", but what I want is, when save as, I wanted the file name increase in one number, and the date in a specific range "H8:J8" the date should be the next saturday.
here is part of the code.
Range("D11:J65").Select
Selection.ClearContents
Range("J44").Select
Sheets("NsFri").Select
ActiveWindow.SmallScroll Down:=-12
Range("A12:A21,D12:I21").Select
on this example I want the file saved as "PrA4W05.xls", being the next week "PrA4W06.xls", and so on.
and on "H8:J8" the next saturday.
I have csv files auto generated (with date stamp in name) and saved in a specific folder everyday. I need to find a macro that will copy the row(s) every day and add to the next empty row in the master excel file. Some days the csv file may have a single row of data and on some other days it may have multiple rows of data to copy and paste.
View 4 Replies View RelatedI am trying to create a macro to run from a form button, within a report, to save a file to a variable file path and name depending on the date value in cell B5.
The format of B5 looks like - 13/08/2014 16:39
The file path has folders for each year in format "yyyy" with each year having sub folders for each month in format "mm".
The file name is just the date only and is formatted "dd.mm.yy" e.g. 13.08.14
I have tried the code below in various permutations but always end up with an error - Method 'SaveAs' of object '_Workbook' failed.
[Code] ......
I am trying to make a save&close workbook macro.
I found several examples on google, but unfortunatly it conflicts with another macro I use for forceing users to enable macros (hide all sheets except one if macros are disabled).
The attached file is an example contaning the save&close code and the show/hide sheets depending on macros enabled.
If the file is opened with macros disabled then only one sheet will be visible.
If the file is opened with macros enabled other sheets are visible.
The problem if that this code uses a custom save, witch makes the save&close not save... (in module1 and in ThisWorkbook)
The pourpose of the save&close is to make sure some users don't forget the excel open and thus block access to it. So if a certain idele time passes excel has to save and close without any confirmation messages.
I have a macro that saves each sheet of my workbook to PDF's. I have a macro that runs prior to the save macro that filters each sheet and hides rows I do not need printed.
I simply use a formula to show a 1 in column H.
What I would like to do is sum let's say rows 5:58 and it H59 is below let's say 5 that sheet is not saved as a PDF.
I looked for awhile for a if.then macro that might solve my question, did not see anything that fit.
I want to specify a button that have the function of prompting the user to save the file as new file name. I tried to do the record macro, but I think it's not working as I wanted to. Can any expert teach me the code? As I was creating a standard form (excel file), I don't want the users to overwrite the current file name.
View 9 Replies View RelatedI work with a read-only template, and desperately need a macro that would save it as a workbook, and every worksheet of it as a separate txt file, then close the whole thing.
I will be re-using that workbook it just saved again, and I want to have the same macro there as well (that it should save my workbook as workbook and every worksheet as a separate txt file).
In My sheet I would like to check to make sure col 15 is true before going through the process of adding the contents of each row from col 6 - 10 to column 5. (Col 5 could already contain values)
This is my
Private Sub Workbook_ThisWorkbook(ByVal SaveAsUI As Boolean, cancel As Boolean)
BeginRow = 8
EndRow = 87
ChkCol = 15
BeginCol = 6
EndCol = 10
For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = True Then
For RowCnt = BeginRow To EndRow
Cells(RowCnt, 5).Value = Cells(RowCnt, 5).Value + Cells(RowCnt, ColCnt).Value
Next ColCnt
End If
Next RowCnt
End Sub
when I record a macro in excel and assign it to a command button, it does not save and have to keep recording it when I open up the workbook again. I notice when i close out of the script editor after saving, the debugger box does not pop up when on most occassions it does.
This does not happen all the time, just some of the time which makes absolutely no sense to me. I have double checked to make sure the module is saved under the current workbook, i have tried saving the script to my personal macro workbook (XLSTART), i have tried saving the file on a shared drive and my desktop, but nothing seems to work. The workbook is not protected and am at a complete loss.
so i can save the macro and try (usually unsuccessfully) to change it.
excel 2002
I have a 4 sheet workbook. 3rd and 4th are hidden running formulas. These hidden sheets have macros that creates a new file from each (the macros deleted unwanted rows in the files, then save as a seperate file and format [.mht and .csv respectively]). Everything functions properly, except the problem is, I need to make sure the changes to sheet 1 are saved, but when I save the file, the changes to 3 and 4 are saved, and I lose the formulas.
View 4 Replies View RelatedI would like to run a macro AFTER the user has saved the workbook. I can only think of manipulating the before save event but dont know how to do that.
View 9 Replies View RelatedI'm trying to get a macro to run when the user presses the save button. I have the following code:
[Code] .......
This code is supposed to apply the TRIM function to every worksheet in the workbook when the user presses save, and the saves the results. I understand the Private Sub bit is the code to have the macro run during the save, but for some reason the macro itself isn't actually running. I'm not getting any error message, its just that the cells which should have the TRIM function applied to them aren't being corrected.
I have a problem in saving multiple pdfs from excel. I am posting a sheet here. First sheet has the employee details. Second sheet have the template for payslip. My aim is to save pdfs of templates of all the employee in the list using a macro.
currently i am doing it as
1. In sheet 2, I lookup each employee using the serial number of employees at the top left corner cell of the template.
2. Once the details are updated in sheet 2 i save it as pdf in my desktop.
The problem with recording macro and looping is that the pdfs are overwritting and the last pdf alone is saved. i could not find how to change the pdf name for every loop.
I need a macro that will save the range of cells with values as a pdf and saved as the sheet name. I have a macro that does a print preview function based on the cells that contain values. It works great, but instead I just need this to be saved as a pdf.
This is what I have:
[Code] .....
I have quite a few macro's saved on my personal xls file. Anyway for me to save them with the current date to a specific folder?
e.g. MacroNameDate.bas
I need a before save event macro in excel that will check if sheet1 cell ref A25 = zero. If it isnt zero I want a msgbox to say "Does Not Equal Zero" to pop up, if it is zero then I do not want a msgbox displayed. In both instances I still want the file to be saved.
View 5 Replies View RelatedNow that I have my data sorted, I am trying to write a macro that will save each columns according to the name in row 1, in seperate spreadsheets.
My data currently looks like this:
A B C D
E12144116501367923134587310715136781403013910587415223136808724234615258
11649675211546423253525711033109522325411542155241402112799
(A, B, C, D & E is row 1.)
What I would like the macro to do is copy each column to it's own unique spreadsheet and save it as a .csv. The first spreadsheet becomes:
A.csv and contains only this data:
A
12144107151522311649
The second spreadsheet is called B.csv and contains only:
B11650136781368067521110331154212799
C.csv etc.
I would like to 'save as' my current open file with a new name equal to the value in cell A1 of the active sheet, and save it to C:Apps. I would also like to save it is a pdf with the same name and to the same location. Would that be possible in the same macro?
View 4 Replies View Related