i have a workbook that is doing a copy and paste between 2 workbooks.
the trouble i am having is every month workbook a changes to be the name of the current month and year workbook b never changes. so when a change in month happens the macro stops working due to new workbook name.
how could i have the macro reed the name of the work book and input that name into the necessary locations withing the macro.
How to translate dates which i already have in the US format, to the UK format.
i.e. mm/dd/yy becomes dd/mm/yy
My database has what I call the 'cover page'. On the sheet is a command button that will take the user to the next page. Basically what I want to do is upon opening the workbook, the user is taken to the cover page, and all the rest of the sheets remain hidden until they click a command button to 'enter' the workbook. P.S. I have some sheets that need to remain hidden though...
Of course the other way is just to have a userform that opens when the book is opened to do the same thing. Where would I put the code for the userform, in ThisWorkbook or a separate module?
I wonder if anyone could help with some VBA code to automatically save a copy of a workbook on a monthly basis (Say the 1st of each month, or the nearest date to that if it has not been saved on the 1st.)
I would like this to be done without user input, and to be saved with the current workbook name + the current date. It can also be saved in the current workbook folder. I am using Win XP Pro and Excel 2003. This would also obviously need to check if the file currently exists, we can say the filename is Cars and the directory is C:Data. Finally I do not wish to stop the users from saving their own version with save or save as.
I have 2 separate workbooks that are linked together and work in tandem. One is the Master Workbook "Master Business Plan Worksheet For Sale.xlsm" and the other is the one containing all the Menu Items for the business "Menu Items Workbook.xlsm"
I have a Master Recipe Sheet that I created in the Master Workbook and made sure it all works. Now I have copied it to the Menu Items workbook and copied all the Userforms and created Macros for it but I need setting up the references to the other sheet. I have included the code from the first sheet for you to look over. The textboxes all need to populate to the Master Workbook and all the info for the combo boxes are also in the Master Workbook.
Code:
Private Sub UserForm_Initialize()
'Populate Measurement combo box.
Dim rngMeasurement As Range
Dim ws As Worksheet
Set ws = Worksheets("Start Here Sheet")
For Each rngMeasurement In ws.Range("Sizes_List")
[Code] .....
My Workbook contains several UserForms in which employees will be entering hours and activities on a daily basis. In the UserForm, they select their department and enter hours for each specific type of work/activity that shows up for that department in the userform. They click the submit button and all the data (date, name, department, type of work and hours) is added to a database table in another sheet, which in turn feeds a number of reports.
I need to be able to have anywhere from 20 to 100+ employees enter their hours on a daily basis, many of them at the same time, all from different locations (within the network of the client's system), using the same UserForms.
We've experimented with Google Drive, but that seems not to be the solution. It kind of works, but is not secure (it installs a copy of the entire workbook on the users system and then synchs the data back to the server upon "save") and way too cumbersome. I've heard of SharePoint, but not every client has that installed (and frankly I don't know how it works).
I have a spreadsheet that has 3 columns, date, id and amount for the whole year.
what I need to do is have a macro on another sheet or workbook that has an entry for month. when i enter the month, i need to get all the data for that particular month and then save it with the month name. i am not very sure how i can do this. pivot table does not work as i need to save a file each month.
My excel database has a master sheet where all data is included. It consists of 8 columns with two rows of headers (1st row: Sheet title, 2nd row: Categories for the columns like, name, salary, emp. start date, Boro,emp. number...etc.
Master sheet aside, I have 12 other sheets in the same workbook (Feb-Jan), we run a fiscal year, not calender. The data that is included in the master sheet needs to copy over to the corresponding worksheet sheet month as long as the date in the emp. start date falls with the month range. For example, if my master sheet has data in rows 4,5,7,9,19,23,101...600 and the emp. start date is in the range of from June 1st, 20xx through June 31st, 20xx then all the data in that row should copy over to the next available row in the month of June's worksheet. The same should happen the months of Feb - Jan.
I have a table of yearly totals for the amount spent by x. I also have a growth rate for each month so for example in 2001 in jan the growth rate might have been 0.3% and feb 0.5% What I want to do is for each month based on the growth rate and the total produce a value for each month which sum to the total amount. It's also important to note that it restarts each year.
Link for excel file is here: [URL] ...........
I’m currently pulling data into two columns labeled “Monthly” & “Non-Monthly” respectively. They indicate work orders with a frequency of “Monthly” or “Non-Monthly”
The Monthly data is obtained using the following formula:....
I get this monthly report that has 5 tabs in it. The last tab, ALL_FAILURES_1mon, is a list of part and serial numbers that have failed that month. From that tab I copy the information into a master workbook that houses all the failures broken up by part number, each part number is a separate tab. I am wondering if there is a way to search in the "Monthly_Report" document for all rows containing the part number, 07X-000-ZZZ" and copy the entire row into the master fails list. I have attached a couple examples with sensitive information blocked out.
What I need is for when the macro is run, it will search "Monthly_Report.xlsx" ALL_FAILURES_1mon tab, for "07X-000-ZZZ" and copy all rows containing the part number and paste them in the next blank row of "Master_Fails_List" in appropriate tab.
*NOTE*I have attached both examples however my "Monthly_Report" document was too large so I had to upload it as a .xlsb but the original is .xlsx
I will have to insert the below table in the worksheet area whereever I am working. Batch No.Doc. No.DateRemarks22-May-12
Fox example I require the "Batch No." in the ActiveCell and "Doc No." in the same row next column, "Date" in the same row and next column and "Remarks" in the same row next column. I also require the Today's date below the "Date", by using =Today() function of excel.
I will have to repeat the above table over 100's of times.
I have a macro that is woking that is activated either via Ctrl-p or by an icon in my quick links.
I would like to call the macro when I enter text in the input cell C7.
At the moment I enter text in C7 and then call the macro to perform a series of tasks.
I would like to make the macro and tasks perform when I enter the text in C7 and hit return or enter key.
Is this possible??????
I have the following situation. I work off-site most of the time in another office. I would like to have a macro on my main office PC to run on a monthly basis at a set time.
For instance on the 15th of every month at 9:00 am the macro would go into a shared drive and pull an updated copy of a report, redo the report and send it out to various offices. I have VB code that performs all the tasks except for setting a timer to tell it too run on the 15th of every month at 9:00 am.
I have a workbook "Weekly Field Report-Master Copy" and another workbook "Weekly Field Report $$$-Master" The "Weekly Field Report $$$-Master" workbook has all the formulas that pull data from the "Weekly Field Reports-Master Copy" workbook and populates a financial report of that week's field unit production.
When I save the "Weekly Field Report-Master Copy" workbook to a new name "Weekly Field Report Job #1" and then save the "Weekly Field Report $$$-Master Copy" workbook to a new name "Weekly Field Report $$$-Job #1" is there a way to automatically change the formulas in the "Weekly Field Report $$$-Job #1" workbook to now pull data from the "Weekly Field Report Job #1" workbook instead of the "Weekly Field Report-Master Copy" workbook???
Currently I am manually changing the formulas in the new workbook "Weekly Field Report $$$-Job #1" to reference the data in the new workbook "Weekly Field Report Job #1". Otherwise the formulas pull the data from the "Weekly Field Report-Master Copy".
i want a macro that will start on D25 and enter a 1 for five rows up to D29
than change and enter a 2 starting on d30 and enter a 2 for five rows
up to D34 than change an enter a 3 up to D39 ....... on and on up D225.
I have a concatenated value in a column D (from three colums A, B and C). Whenever a user enters data in colums A, B and C, I would like to get a sorted list of values from column D to be populated in column E. I use the sorted values in Column E for a dropdown list in another worksheet. Could somebody help me with the macro code?
View 9 Replies View RelatedI want to do is set up a macro to autofill into a new column every month. My data is linked to another excel document so that is where it is pulling the data from. Currently I have data in columns monthly from Dec 2001 until June 2008. I just want a macro that will automatically add in the next month, so in this case July then August the following month ect.
View 10 Replies View RelatedI have an excel sheet which has a macro which captures the current time in a cell with the formula =now(). What I am looking for is that once a user runs a macro and the time is inserted into that cell then that cell should be locked and cannot be changed then. If this can be done by any formula / function or VBA.
View 2 Replies View RelatedI have this macro that I am using but want to automate the file it uses. This file has the last 5 text/digits change each month, e.g.
Internet_GB_Report_Feb07
Internet_GB_Report_Mar07
Clearly the next file would be Apr07. I would like the macro to identify this automatically and use the correct file without having to go into the macro every month and change the last 5 text/digits. If you have any ideas I would be very grateful to hear from you. The Macro is below:
[/code]strPath = "c:Internet"
strFilename = "INTERNET_GB_FEB07"
strThisWkb = ActiveWorkbook.Name
intReports = Workbooks(strThisWkb).Sheets("Lookups").Cells(1, 2)
Code:
This is what I'm trying to do:
1. When a user clicks on a Drop down box and selects "Add New", I would like a Macro to run
2. I want this Macro to open up a Dialog/Text box to say "Enter New Information" where the user can enter in some text
3. The text that is entered by the user into this box will be added as a new entry into the Drop down box selection
My VBA knowledge is still at a beginner's level and I know how to get a Macro to run based on a cell value. However I don't know how to create a Macro for the dialog box nor do I know how to create a Macro for updating the drop-down items.
I am trying to create a macro to insert monthly P&L figures into a spreadsheet.
I am needing the macro to insert a column to the left of a column titled 'movement' each month and copy&paste my monthly figures in.
I have recorded the below macro but my inserted column is always moving into column E and pushing the prior month to the right, where I need it to always be situated to the immediate left of the movement column, e.g. if the macro is inserting September P&L data I need it to be situated between August data (currently in column E and movement data currently in row F and so on.
Column C D E FItem
July
August
Movement
Fees
26,500
28,000
2,000
Assets Ongoing
150,000
160,000
10,000
Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Sheets("P&L Current Month").Select
Range("D11:D127").Select
[Code] ...
I'm looking for a formula that will paste an entire row from sheet 2 into sheet 1 of the same workbook when only 1 cell is typed.
I am trying to come up with a property access log for my guards so when they enter a tag number from(sheet 2 column B) into (sheet1 column D) , it will auto fill sheet1 column C and E with the entries from sheet 2 column A and C.
I am trying to get a function (sum) of several cells to be automated in a monthly spreadsheet. I can get the function to work, but how do I get it to Autofil? The syntex seems to call for a range, which will be different every month. I can't figure out how to loop it, so I thought autofill would work.
View 3 Replies View RelatedI have 3 workbooks in XLStart. I want to change the workbook that gets the focus after they are all open. The workbook i want to have focus is DG_Tourny.xls
View 2 Replies View RelatedI have an overview worksheet that features a list of dates; I want to hyperlink the dates to the another worksheet within the workbook that represents the month. I want to do this without changing the dates in the overview sheet. For example, I have 2014-05-23, 2014-05-24,2014-05-25 in my overview sheet and I have a worksheet titled May 2014. When I try to hyperlink the dates to the May worksheet it turns the dates to #### in the overview sheet. How can I stop that from happening? 
View 13 Replies View RelatedI am working with two files everyday: today's and yesterday's. Each report has the day's date in the name of the file.
So I'll take today's (11-5) and import it into yesterday's (11-4). Then, tomorrow I'll take 11-6 and import into 11-5. The day after, I'll take 11-7 and import into 11-6. So on and so forth. I'm trying to figure out how I can get VBA to accommodate these changes in names. So that it sees the date in the name of today's report, sees that it is today's, then selects yesterday's (or says "Open yesterday's report!"), and imports the information.
I can figure out everything else, just not the naming issue.
I guess I could always stipulate to the person running it that they should only have these two workbooks open and that they should run the macro starting in yesterday's workbook. Just writing VBA code to select the only other workbook so VLOOKUP can be done from it. But I am trying to make it fool proof.
I've got a macro set up to copy a range of cells in one workbook, open another existing workbook, and then paste that range. So far so good.
However, I need the macro to also save a copy of that second workbook with a filename that is derived from the original workbook.
To be a bit clearer, let me give an example of how I'd like this to work. Let's say the original workbook is titled Pickle2014-03-14.Raw.xlsm, and I have a second workbook titled UploadFormat.xlsx. I want to run the macro in Pickle2014-03-14.Raw.xlsm, have the data pasted to UploadFormat.xlsx, then save UploadFormat.xlsx with the file name Pickle2014-03-14.Final.xlsx.
can anyone show me the way to run a macro when visual basic editor opens
View 5 Replies View RelatedI have a workbook, that when opened the first time needs to prompt the user to save it. I got that working with no issues by using
Private Sub Workbook_Open()
SaveOnOpen
End Sub
where SaveOnOpen is a procedure in another module. What I would like to do now is re-assign the Workbook_Open sub to be set to null, so that it doesn't run any more. Is it possible to somehow assign Workbook_Open to call a null procedure, as opposed to setting up an onTime call to delete the code itself?