Can this code be simplified? I currently have this code repeated 15 times, I need to color the ranges yellow and then unlock them. So for now I am repeating the code below for range ("C19:N26") and then 14 other ranges.
And if you have suggestions on cleaning up the code below please let me know, I would like to know the proper/most efficient way to write code.
Sub FormatingBudgetFiles()
'Colors each Forecast cell yellow that the end user can change
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
With ActiveWindow
Range("c14:k14").Select
End With
In range C15:C45 I have names of some steps. Starting from column D, row 6 have a drop down with 6 possibilities. Now based on what is selected in row 6 (so cell D6, E6, F6 and so on), I would like to color certain cells from 15 to 45 in that particular column (certain steps that are applicable to option selected in row 6 of that column). And as month goes by, users do that in more and more columns.
Also, steps overlap between conditions that are selected in row 6 and I would like this to macro (or formulae) to be running so that as soon as user selects the condition cells are colored and then they can fill out only those cells. So, I couldn't figure out any way to do this by conditional formating as there are more than three conditions and range is not continuous.see attached file as an example of final output.
i have a list of customers with various information, i would like to rank these customers 1 to 5 and change the colour of the cells dependant on there rank.
Conditional formatting allows me too do this but is limited to 3 formats.
I have Columns A-K filled with data and want the condition to be set on column B if Column B meets a requirement it will then colour that Row (A-K)
So for example Row 3:
B3 = the requirement to change colour to red so cells A3:K3 will turn red B4 = the requirement to change colour to gree so cells A4:K4 will turn red
and this must go down to row 1000 or whatever it may be.
I'm trying to get it so that a cell becomes automatically highlighted if 2 other cells conditions are met. For example, I want cell A5 to become highlighted if BOTH cell B1=Loan 1 AND cell B5=Yes. Here is a mockup of what I'm talking about incase it makes it easier to understand. I'm using Excel 2007.
I have a workbook that updates from external source and creates sheets depending on a cell range.
I have put tab 1 and tab 0 on either end of where the new sheets will be inputted, will never know how many sheets
What i need to happen is if someone fills in "complete" in A7 in my "summary" sheet then the values in row 6 in all the other sheets get hardcoded. This needs to happen from A7 down to A26, so A8 = complete then copy row 7 etc This is what i have so far
I get compile error here ........Sheets(ArrSh(1)).Activate
Also need it to work for all the other rows.
Sub hardcode() ' 'Sheets("Summary"). Select If Range("a7") = "complete" Then ' Sheets(Array("1", "0")).Select Sheets(ArrSh(1)).Activate
The sheet is actually being added to the workbook, seemingly before the error. I resume the code, and a new sheet is placed in the workbook and it errors again. The Debugger stops and highlights on the code above.The sheet count number was 10895 at the error, just as an indicator of how many times the simulation has performed successfully. I am hoping this is something I can fix without having to start over...
I have a workbook with 26 sheets, labelled A to Z. Column A in all the sheets have names from rows A6:A35.
I need a macro or a code to extract all the names from each of the 26 sheets and paste it to a new sheet 'Names' under column A, such that names starting with 'B' paste under all the names 'A' and so forth till 'Z'.
I've got data being scraped from a site, putting 1 new workbook in a folder each day
each workbook has 40 sheets in it.
i need to run 5 modules in sequence on a sheet then loop to the next sheet and run the same 5 modules.
ive writen all the modules, and can loop them through the sheets in sequence but i cant work out how to loop them through the each workbook in the folder..
is there an easy way to do this or can it not be done because it would need access to the folder that holds all the wordbooks which lives outside of excel on the desktop ?
I would like a macro to be able to save 26 tabs within the one document to individual PDFs.Preferably I would like to be able to specify each time exactly which tabs get printed, because often I don't need to print all 26, just the first 10 or so.I would like each PDF to automatically be named with the value in cell E10 of each tab.E10 already has a formula to create its final value. It references cells from other tabs within the same document. Hopefully the fact that this cell has a formula in it won't affect my ability to use the resulting value as a 'save as' reference?I would like it if the PDFs save to the same location as the Excel sheet from which they're generated is located. The location of the excel sheet will change every three months, so I'd prefer not to specify a location with a specific filepath, as it will have changed by the time I run the macro again.
I am using MS Office 2010. I want to count---on multiple sheets---the number of times that a given cell is greater than another cell if and only if a third cell is equal to a given value. I want to do this for 4 sets of data on each sheet. I thought I had it figured out with this formula---
but it returns a value of zero each time. Clearly there is an error in the formula.
Here is some background: -- $H$1:$H$43 is a block of cells that has the names of the sheets in the workbook -- E1 and F1, G1 and H1, I1 and J1, K1 and L1 are the four groups of cells that I am comparing. In the entire workbook, I want to add 1 (counting function) only when: R1=2 AND E1>F1 or S1=2 AND G1>H1 or T1=2 AND I1>J1 U1=2 and K1>L1 on each appropriate sheet in the workbook.
I have saved this on a 2010 workbook as I am at home but this will be used on a 2003 workbook.
I have several projects on one spreadsheet which multiple users will be working and I am trying to create a summary sheet of the work carried out.
Each user is expected to carry out a task on each row of the data held in each worksheet (research, call, update etc) and each task (Option 1-5) is assigned a value. Each user is expected to meet a certain level of points per day to calculate productivity.
I am looking for a sumproduct along the lines of the summary sheet attached but mine just takes one sheet into consideration and I need one for all sheets.
I have a Excel 2010 workbook used to rota in a large amount of staff for a call centre, which is split into four teams. Each sheet corresponds to a month of the calendar year eg Jan201, Feb 2014 etc..
What im trying to do is put in a sheet at the front of the workbook that I can select the team, which populates the list of staff in that team and then checking across a specified date range gives the shifts that those respective staff will be working for the set time period (probably be looking at a seven day period and a 1 month period). (This in turn will be printed out to give to the staff members.)
I'm trying to put together a spreadsheet that tracks disc capacity increases, affected by any incoming projects. I've managed to do so for one project, but would like to for up to 10. The way i've designed the solution (i'm sure there are far more elegant ways, but hey) is thus:
A forecast worksheet keeps track of a grand total, taking information from sheets P1 -> P10 (being projects 1 to 10). I am unable to figure a way to add up all the increases from all 10 project worksheets with one succinct formula. What I use so far is: ='P1'!C83+SUMIF('P1'!E82,"=2009 - Q1",'P1'!D82) ..................
I've got no clue about all this, but I've had to get specific formula examples and fill in the blanks in order for my timesheet to work. There's just one final problem if somebody could please help.
This is a timesheet for a 5 day work week. I need to count the number of unique log numbers for a specific activity. The log numbers counted must be unique across the entire week, not just for each day, which means I want the formula to count the unique log numbers across multiple sheets.
The formula also has multiple conditions. I got 2 columns. The first part of the formula needs to verify a word, say, "split" and if it does it checks the adjacent cell for a unique log number. If both arguments are true, it counts the log as 1 unit.
Here is a working formula for only one page. =COUNT(IF(D4:D29="split",IF(FREQUENCY(C4:C28,C4:C28)>0,1,)))
Here's 2 problems with this formula: 1. I will count if it encounters a blank cell in the Log numbers the first time (which will happen as not every activity we do has a log#), but it will stop counting if it encounters a second blank cell.
2. I don't know how to make it work across several sheets.
This is an alternate formula which works and skips the blank cells, but I don't know how to add the multiple condition of "split" and to have it work across multiple sheets. I just copied it Microsoft. As I said, I don't understand it, I just fill in the blanks.
I've got several worksheets that all have the exact same layout that a user will enter unique information in to each worksheet. Then I've got a final worksheet that I want to have a button that the user can click and when they do, it will look to each worksheet and do the exact same process for each worksheet as follows:
It first looks to see if the worksheet is visible. If it is, I want it to copy the range A5 to K5 down until it gets to the last non-blank cell in column C. The first non blank cell that will be referenced will be C7. Then I want it to paste this information into the range A5:K5 on the final sheet named Sheet8 with the same values and keep cell formatting such as width and height, font. If the worksheet is not visible, it skips the sheet.
I want it to do this for each visible worksheet, placing the next visible worksheet info under the previous visible worksheet info. My current code as shown doesn't do that. It requires that something be inSheet8 A6 before it will even paste, then it pastes the info from A5:K5 but it doesn't do just the values nor does it keep the formatting. What I mean about not doing just the values is some of the info that needs to be copied comes from a drop down they can choose from and it copies the actual drop down menu. Also, it seems to copy all of the ranges from each sheet and paste it into just A5:K5 on Sheet8 and overwrites each other instead of pasting Sheet2 just below the information from Sheet1. So the only information shown after the entire process is completed is the information from the last visible sheet.
If Worksheets("Sheet1").Visible = True Then Sheets("Sheet1").Range(Sheets("Sheet1").Range("A5:K5"), Sheets("Sheet1").Range("C7").End(xlDown)).Copy Sheets("Sheet8").Range("A5").End(xlDown) End If
I am working with a nonprofit to set up their financial ledgers. There is one workbook with 12 sheets, one for each month. The goal is to be able to set up a formula that searches through all 12 sheets for every donation that a specific individual has made. For instance, let's say that John Smith gave x amount of money on 1/1/2000, y amount of money on 1/10/2000, and z amount of money on 2/2/2000. This data will appear on 2 different worksheets. I have the following formula, which allows me to look through one sheet at a time:
Where Column A in each sheet is individual name, and column B is amount donated. Sheet "Jan" is the data for the month of January, and sheet "Smith" is the culmination of John Smith's donations for the full year. Using this array function, I am able to retrieve all data for John Smith in the month of January, but I can't find a way to make one function that searches for all of John Smith's donations in each month. Is there a way to build an array function in VBA that would accomplish this?
I have about 20 workbooks with different file names for different projects all saved in the same folder. Each workbook has about 10 worksheets and each worksheet is named in a similar fashion in each of the 20 workbooks (eg. revenue, cost, variance etc.). I want to pull out a worksheet named ' forecast' from each workbook into a master workbook so that the master workbook would contain the 20 forecast worksheets.
What I'm trying to do is grab data from the raw data sheet and paste it into the master sheet based off of certain criteria. I want to list any accounts that are not correct.
For example, I want to look through the Raw Data sheet, find the account that contains campaigns which are not correct, and then list the name of the account on the Master sheet. I only need the account name listed once on the Master sheet.
Index across multiple sheets returning multiple values
I am building a payroll workbook and I need to build a sheet that will allow me to choose an employee name and the formula will return all the data related to this employee. I found this forumla to do this: =IF(ROWS(...)<=...;INDEX(INDIRECT(...);SMALL(IF(...=...;ROW(...)-ROW(...)+1);ROWS(...)));"")
But in my workbook, the data is on multiple sheets . How do I adapt this formula to look into 12 sheets?
that data would be returned in order of the date of the pay.
I have a need to populate a summary worksheet using two variables to find data in two or more other worksheets.
I find writing out what I want helps some times so let me try it here.
So my variables are:
Product (there are 22 products) Supply Less than (inset number)
These are the two criteria I want to use to produce a result.
The next issue is I have 300 stores that carry said 22 products. Each store has a unique number 0001, 0002, 0003 etc. So in a separate worksheet I have a list of the store numbers, and then the products. So each product has the store's number to the left in Column A, Column B has the product name, Column C has the quantity on hand.
What i would like to do on the summary page is select the product, and then select the supply less than or equal to 'x' and then have the stores with the selected product less than or equal to x display below.
The last part of this is then to display (data from an other sheet) on the summary page which contains the quantity of the product selected available at the warehouse for that store.
I have a spreadsheet that has about 50 sheets and a summary sheet at the front the layout of the summary sheet is column A has skill sets from A4 to A20 and across the top it has week commencing dates from F3 to about BA3 (dates go from w/c 06/05/13 to 08/12/14). On the other sheets they have the dates in the same columns but the skill sets vary as to each project, some projects may have two of the same skill set in column A because there is two people needed who can do the same thing, I need to total up the days over the sheets for each week and skill set.
And so on for each sheet and then change the formula for the next skill set and then again for the next column.
Is there a way of doing this without having to enter a formula into each cell, at the moment I am coping and pasting the formula into word to do a find and replace to change the SummaryA4 to SummaryA5 etc and then into the next cell for the corresponding date.
So to make this seem like it makes any sense I am looking for say an 'Administrator' in all the project sheets over all the dates and for it to add it up for the summary sheet. Under the corresponding week against the skill set.
So it would be F4:F20 for w/c 06/05/13, G4:G20 for w/c 13/05/13 and so on until all skill sets.
Ive put some sheet code together that i need copied to 12 sheets (jan to December) in 24 workbooks (each workbook has trhe same sheet names). I dont want to alter the actual content of the Excel sheets, I just need to copy VB code from a template (in VB editor) to the 12 sheets in each of the workbooks. Is this possible to do with VB or do i need some other utility since Im using the VB editor....
I inherited a spreadsheet that had an userform where the user checked off which 'pages' he wanted to print. The Ok button routine used if statements to run a routine for each 'page.' Here's an example of the original code for one page:
Sub Button2_Click() Sheet7.Activate Run "HorizontalPrintStuff" 'generic landscape pagesetup With ActiveSheet.PageSetup 'specific pageset settings .RightFooter = " Construction Assumptions" .PrintArea = "CONSTRUCTION" 'the named range to print .Zoom = False .FitToPagesTall = 1 .FitToPagesWide = 1 'this changes depending upon the page selected End With ActiveSheet.PrintOut End Sub
The problem was it printed each page as a separate print job; and if you print to adobe, you get serveral files, not one file. That and it took a long time to run.
So I tried a different tack. If the checkboxes has true, then the printarea is set to that named range. If there were more than one named range on a sheet to be printed, I consolidated them. I did this with a bunch of if statements - very cumbersome.
Sheet3.Activate 'Sheet3.ResetAllPageBreaks 'disabled due to errors Run "HorizontalPrintStuff" 'generic landscape pagesetup With ActiveSheet.PageSetup 'specific pageset settings .PrintArea = "DEVBGTALL" 'the named range to print .FitToPagesWide = 4 'this changes depending upon the .FitToPagesTall = 1 End With
I haven't shown all the code cause it goes on for 12 sheets containing 16 different printareas.
My current muck ups are .....
1) it prints every printarea/named range on a given sheet (I took out all the if statements trying to debug everything.) Is there another conditional argument that allows for multiple 'trues'?
2) the pagebreaks in printarea/named ranges that are multiple pages (like a 48 month schedule) won't stay set. I've tried both VPageBreaks(3).Location:= and .VPageBreaks.Add Before:=
3) the Sheet1.select false argument is always adding a random sheet to the end of the print job. Don't know why.
I can do all this in a recorded macro, just not the selection userform. I've thought about copying to another sheet or hiding columns and rows then printing, but that seems just as cumbersome.
To recap, i want to print out, as one print job, multiple printareas from mulitple sheets, based upon checkbox selection on an userform.
I need to be able to track changes on selected ranges on multiple sheets, but Excel does not appear to be able to do this. It only appears to allow me to select multiple ranges on the same sheet.
is there a way to track changes on multiple selected ranges on multiple sheets
The macro creates multiple charts from data in one sheet, but now when I'm trying to scale it up to multiple sheets, the previous charts created disappear. E.g Creates 5 charts for sheet 1 (can see them being created, using debugger mode), looping to the next chart and they are created but the first ones has disappeared! What am I doing wrong? I'm creating my chart by the following code:
[Code] .....
The startpoint variable is changed for every new chart within the one sheet and the outputsheet is changed when you change the sheet.
I have a workbook with multiple sheets in it. One sheet is an Overview sheet, and the others represent the months (JAN, FEB, MAR, etc). On the Overview sheet, I have a few columns of data set up that I need to retrieve from the various sheets within the workbook. The thing is, this data can occur more than once on any particular sheet and it's very likely to occur multiple times throughout the workbook. For example, compressor #1 was blown down on January 1, January 15, February 5, February 9, February 12, April 22, and so on. Compressors 2-5 were also blown down multiple times throughout the year, as well as various other equipment that I have listed out throughout the numerous monthly tabs. On the Overview sheet, I have the columns Equipment, Date, Temp, Begin PSIG and MCF Loss. I'm needing to get the information from those tabs into these columns.
I first tried a simple VLOOKUP. After all, it's worked for so many situations similar to this, why wouldn't it work here? I quickly found that it won't work here because I need to return multiple results, not just the first one it comes across.
When I copy the formula down it seems to return the same results in every cell, not the next result, and the next, and the next, and so on.
I have 6 spreadsheets all within the same folder, these are pretty much identical (rows, colums, sheets within them) apart from the names of the files.
I then have a master spreadsheet within the same folder where I want to combine all the data, from all the sheets within each book (if that makes sense!) apart from the data on the last sheet within each book as this is the reference data, onto one sheet within this master file. If possible I only want to copy rows accross which have complete data too.
So: (names not correct) From book1.xls copy all data on sheets (sheet1, sheet2 etc) except last sheet From book2.xls copy all data on sheets (sheet1, sheet2 etc) except last sheet combine onto masterfile.xls on sheet1.
I have searched on here and can only find how to do it with the first sheet in each workbook, not looping through all the sheets in each book. Please see below.
I'm trying to do is develop a formula to: - look at one column in sheet 1 and if a value in that column equals a value in another column in sheet2. then, display an X
