I'm trying to create a monthly forecast over 9 months. I know the planned cost for month 1 and month 9 but need to figure out how to spread the remaining amount over months 2-8 using a downward slope.
Here's the problem:I have 9 months to spend $28M. I know in month 1 I plan to spend $6M and month 9 $3M. That leaves $19 for months 2-8. I need to figure out what the burn rate would be in months 2-8 ramping down from $6M to $3M and not exceeding the available amount (19M)
I need a simply excel formula or macro which calculates the numbers of months i need to accrue for certain costs. The data I have includes the start month of the invoice, the end month of the invoice (i.e. showing how long the invoice is relevant for, quite often 1 year), and obviously the current month.
(eg. a 12 month invoice with a start date of Oct 08, end date Sep 09, current May 09 = 8 mths to accrue) (eg2. a 12 month invoice with a start date of Apr 09, end date of Mar 10, current May 09 = 2 mths to accrue) (eg3. a 12 month invoice with a start date of Jun 09, end date of May 10, current May 09 = 0 mths to accrue)
I have added 3 tables data .. also I had created a sample solution calc for emp1 and project1 ... I need to calculation the ratio between the months based on the working days and allocate the efforts accordingly.
macro listed below. Currenlty the macro pulls the last 12 months as long as the data that is pulled is current. However, if the data is 2 months old for example, it only pulls 10 months instead of 12. modify this macro so that it alway pulls the last 12 months. I'm assuming that the minimum date would have to be found first and then go backwards from there?
Code: Dim PI As PivotItem Dim d As Date Dim Twelve As Date
I have a long complicated macro that processes a ton of data and gives the output in a new sheet. I then want to create new buttons (or some sort of user clickable triggers) on this new sheet that runs another macro.
I have got the actual adding of buttons in the sheet working, but I can't find a way to automatically assign macros to newly generated buttons during runtime. Is this even possible (or are there any clever tricks I could use to get around this?)
The attached workbook contains a macro ("Transfer_Data") that copies values from one cell to another when the "Update Progress" button (near cell A3) is selected. It works perfectly fine in this workbook but when I try the same code in another workbook, which looks and operates exactly like this one, nothing happens.
Is this due to a security setting or missing code line items?
Below is a code which I added to my 'Quick access toolbar' (and which is based off a hidden workbook). I use this macro to fulfill tasks to regular excel spreadsheets that are not macro enabled, however, I can't seem to get this macro to work for other workbooks. How to modify my code to work for other spreadsheets.
It gives me a error and says my password is wrong. I have user names set to return the password so that it is unlocked for certain people. It works for unprotecting the sheets. I modified it and added it back in to try to do it for the workbook.
I realize that they " and () around the password are differant, but that's how I last left them. I have tried several combinations and it just gives me more errors saying expected end of statement, etc.
I have this macro which sits in the workbook module:
Code: Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range) Static OldRng As Range
On Error Resume Next If Application.CutCopyMode = xlCopy Or Application.CutCopyMode = xlCut Then OldRng.EntireColumn.FormatConditions(1).Delete OldRng.EntireRow.FormatConditions(1).Delete
It's used to highlight the selected/active row.
But, as soon as I run another macro, it simply stops working entirely and I'm left with a row permanently highlighted until you manually remove the conditional formatting. All formats and cell colours controlled in the other macro stop working as well. This is the other (edited for publishing) macro, which is used to send an email, and is found in a module:
Code: Sub Send_Mail() 'this sends an email that sends a text message
Dim OutApp As Object Dim OutMail As Object Dim MyButton As String Dim strTo As String
I didn't write the first macro so I'm not sure where the problem is? I'm guessing there is something in the code that is stopping it from working (i.e. running conditional formats) as soon as another macro is run? Is that was is happening?
I've got a semi-recorded macro to format a report I run, and towards the end it deals with sorting, subtotalling, formatting text etc. The main function of the macro is to split a large report into several smaller reports defined by whichever manager name appears in the relevant column. The smaller reports need subtotalling by 2 different columns so I have the following macro code :
For some reason this only subtotals by the second column and not the first. I tried swapping them over so GroupBy1 then GroupBy2 but it's still the same. Anyone got any ideas why this might be? I appreciate it's not the smoothest code I could use but I don't know enough about code to do any better.
I've got a column that auto-populates from anther column depending on what's input, and that's working fine. I also have a button set up to select and copy the auto-populated data. I just recorded myself select the top cell and hit shift+ctrl+down to select just the values that are populated. What it does, however, is selects the blank ones too. I know the counta() function would work, but I don't know how to input that into a macro.
The macro I have now is:
Sub Copy() Range("B2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy End Sub
I have a macro to export two worksheets and save as, but the save as part will not work, it just exports to Book1.
Ive checked the directory and everything is fine.
I want the value of a cell in one of the exported worksheet to be included in the save as name.
I'm just wondering if the code needs to somehow specify the worksheet to get the cell value from or will it default to the first worksheet. but either way my code is still not working and i can't tell why.
Should i just create a seperate macro for the save as?
I have a text document to which i need to do some changes and then turn it into CSV. Here are some lines of what the document looks like and below i will show you what i need it to look like. So i open the document with Excel and next I'm not sure how to do what i will show you below. Original:
I am working with excel 2003. I get a workbook sent to me via Email that has 12 tabs on it and each tab has to be printed out. The fastest way that I know, to get this done is start with tab 1; hit alt+f, u, alt+f, enter, Ctrl+P, enter, Ctrl+page down ( to move to next tab and do it all over again), each tab is a slightly different width so I am constantly have to use "Fit to Page" but each tab will fit on one page easy.
I was wondering can I use a key stroke macro to print this workbook. I actually get 5 workbooks like this every night, it does not really take that long, maybe I am just being lazy, I was thinking that I might be able to use a key stroke macro and then name it for what ever workbook it works with (workbook 1 print ) but I don't no if this will work or if there is an easier way to get the printing job done.
I can't upload the workbook but I could create a simple say 5 tab workbook that has no data in but has same formatting and upload that if you need to see what I am doing.
I want to create a new excel application through macro. I basically want to transfer few columns from the parent workbook to new workbook. but these new workbook must be contained in a new excel application.
Ok, I have a situation that I would love to have some help with. I have a command button that when clicked takes the user through a few questions via input boxes. The final objective that I need help with is a large typing area. Either the input box typing area needs to be larger or I need a text box to appear. After user is done typing and presses ok, I need what they typed to go into an area where I have merged some cells/rows to make a paragraph.
I am a new user into excel using macros how I can automate a worksheet, after I have about 300 rows as expenses of which I want some of these to distinguish groups. Note that the name of expense is not stable for each time which i download the file, so based on the name of the output, i would like to write a macro so that running this macro to enter a number in column B for each expense which i want to group it so that then using filter i can issue any group. Note after filtering i would like to have the total in column C. Please see below extract of spreadsheet.