So I have recorded a macro that filters for certain criteria in a master list of projects, then copies the projects that meets this criteria and pastes it into a timeline in another worksheet. However, because the list of projects gets bigger and smaller when the filter criteria changes, right now I have to create twenty extra rows so that the pasted list doesnt ourgrow my timeline. How I could input a few lines in my macro so that the table for the timeline expands as the number of prjects grows or shrinks.
One method I have tried is to copy the filtered data and "Insert Copied Cells" into the table, however I don't even get the option to insert copied cells after I have copied the data...
Is it possible to make a self expanding table. What I have is a sheet with a table with loads of biking tracks with track number, date, nr of miles and so on...
I'd like to make a table on the second sheet so that I can see only the tracks that auto fill as I put in info about number of miles or date or track number.
And I'd like it to auto expand so I don't have a lot of wasted space on the page. I'd like the TOTAL amount of miles, when i put in multiple tracks, to move up or down when I add or remove tracks...
I have a problem in expanding/collapsing fields in pivot table.
I have source table with GROUP, SUBGROUP, ACCOUNT and AMOUNT. In pivot table I've put GROUP, SUBGROUP and ACCOUNT in Row labels respectivly, and AMOUNT in values. There are same names in SUBGROUP for different names in GROUP. (for example: groups are Production costs and Distribution costs and in each of them there are subgroups Personnel costs and Other costs).
The question is: Is it possible to expand only field Other costs in group Distribution costs while field Other costs in group Production costs stays collapsed?
I would like to use some VB code to search a Pivot Table, find a specific value and show the details relating to the value. I used the following code I have obtained from another thread in the forum
Sub Expand_Pivot_Values() Dim rngFind As Range Dim rngPTData As Range, rngPTC1 As Range Dim rngF As Range, rngP As Range Dim strFind As String Set rngFind = ActiveSheet.Range("J3:J6") 'set the pivot table data range - see help file for property description Set rngPTData = ActiveSheet.PivotTables(1).DataBodyRange 'set start cell for find Set rngPTC1 = rngPTData.Cells(1) For Each rngF In rngFind.Cells 'set variable to find........................
This code works fine but only identifies the first time the value appears in the pivot table and shows detail relating to that value. I need add to the code to search the pivot table and find recurring values. For example if the value is "2" I would like to show detail on each "2" value there is in the pivot table.
I have two pivot tables where if one expands a subtotal (in this case 'manufacturer') to give the lines below (products made by the manufacturer), is it possible for this to be mirrored in the second pivot? I noted a previous comment on using slicers across two pivots but I cannot do this in this instance, although I am using Excel 2010.
I would like to have a macro to automatically generate a statistics table (on the "statistics" tab) with the 5 following fields: Fragment names / # samples / # of failed samples / % of success / # of variations in the fragment (SNP). At the bottom of this table, I would like to have a cell with the average % of success for all fragments. The data to generate these statistics are on the "gene name" tab (please note that this name will change every time I will work on a new gene). To make things easier, I think the macro should be run from this tab.
1. The Fragment names are displayed in row #5. I use one column per variation per fragment. If one fragment has 3 variations, there will be three columns and I will merge together the fragment name cells. The fact that some cells are merged can be a problem when copy-paste to the stats table (as I would like to get rid of the merging).
2. # of samples corresponds to the number of cells in blue in column A. The number of samples can change from one report to another but is always constant in the same report.
3. # of failed sequences. In the table, I type "Failed Sequence" (if the analysis has failed) and "Missing Sequence" (if the analysis has not been done). When a sample is failed or missing, it is for the who fragment, no matter how many variation there is in the fragment, so I usually merge the cells of all variations for this failed sample.
4. % of success: this is quite easy #sample/#of failed+missing sequence for this fragment
5. # of variation is equal to the number of variations for this fragment (can be 0, 1, 2, etc.). When there is no variation in a fragment, I put '-- in all cells of the corresponding fragment on the "gene name" tab. Fragment 3 on my file is an example of 0 variation.
I have a number of array formulas that refer back to the table in the top left. The array formulas are in Columns I and L, and cells N200, N203, and P203.
Normally when I add a new line to the top left table (usually by clicking on the empty box below the previous final line and typing the date), the table expands by one row and the array formulas all adjust to match, i.e. all the A180/C180s in the formulas become A181/C181s, etc.
However every once in a while (maybe once a month), the arrays simply refuse to update, and I can't figure out why. When they fail, they all fail, it's not hit or miss. You'll see in the attached sheet that though I've added a line to the table (A181) and data to that line, all the arrays still read through A180/C180.
Why this randomly fails to update, and if it's something I'm doing incorrectly?
And that leads to an associated question. In the past when this has failed in this manner, I've laboriously gone through and changed all the cell references manually, in every single individual cell. (After doing it once, the arrays usually magically start auto-expanding again.) I'm certain there is a way to make those formulas refer to a named range, instead of all being manual references, but after spending an hour on it, I keep getting #VALUE errors.
I've been using [URL] ..... and pages like it as a reference without success. Obviously, I would prefer that the formulas auto-update without issue per my first issue above, but in the event that they don't, it'd be really nice to just change the ranges in one location and have that propagate across the sheet.
Can record the all those actions which we need to do on a regular basis. In my work i have to create the pivot table to a data which gets changed every day but the data sequence remains the same.
I have tried to record the macro for the pivot table but when i tried to run the macro again then the below error message comes.
"run time error 5" "Invalid procedure call or argument" when i click on debug then the below macro program is highlighted in yellow color.
I got this code from another old old thread, is there a way that the pivot is already setup like column A is already in Row Labels, B is for Column Labels and C to Values.
Fixing a macro that i recorded in order to generate a pivot table on the same sheet and the same exact location every time. it seems that the macro fails every time i try to run and i'm thinking it has to do with the table reference. Again i want the table in the same sheet and the same location every time i run the macro. this is the recorded code so far.
I have a data table that contains 180 dates e.g. 9/30/06, 10/31/06 etc. When I'm creating the pivot table via a macro I'd like to have (2) parameters to enter at run time, those being Beginning Date and Ending Date.
I’m trying to do is create two tables from data evenly spaced throughout columns A to I. To make the screenshot easier to view I moved the second table under the first. The data is arranged so that each “Subject” has 42 columns and is arranged by “Type” and then by “Subject.”
The tricky part is that the number of subjects will vary so everything has to be done in some sort of loop. From the little I know about VBA I was able to record and edit a macro to add spaces every six rows and average the “MaxResponse.” (that’s what stage the screenshot was taken at) To make the table I think I need to write some sort of Dim statement but that is way beyond my comprehension.
Here is a link to a photo of the table http://yfrog.com/77excelshot3j
I need to create a dynamic list from a table of data.
I have performance data for 110 different pumps. Data points are generated every hour, and the table is updated with new data periodically. I want to automate the population of a list of 6 different pumps, and specify the date range populated. To put it another way: I want to place data from Pumps 1 through 6 for all of November into Columns A through F. Then I want to clear that data, and show data for Pumps 105-110 for last week in those same Columns.
One of the main goals of doing this -- other than quickly narrowing a field of data -- is being able to quickly chart this data on a scatter plot with a custom format. The pumps are grouped together by region, and individual pumps have specific purposes. So I need to quickly generate graphs with a series' color scheme or formatting that is consistent and logical between different pumping regions.
I haven't been able to make pivot table work because of the graphing issue, and also because of the way it handles data points and presents data. I would like to make this work with excel functions and maybe some filtering, but I'm not opposed to figuring out a VB script if you think that's what I should use.
I have recorded a macro to create a pivot table. I thought I had it so that it would create the pivot from the active worksheet only. But looking at the code, it is picking up the sheet name from the one I recorded it from
Code: Sub SalPiv() ' ' SalPiv Macro ' Macro recorded 20/06/2012 by imccormick
I have a leave history report with data for leave taken for the 2014 year. The sheet contains the following headings:
Name; Leave Start Date; Leave End Date; Date Paid; Days Taken; Roundup; Offset
The roundup column rounds the days taken up, for example if someone took 0.4 days off it would round it to 1.The offset column just subtracts the Roundup value by 1 (=Roundup-1) I think we will need this for what I am trying to do.
I have created a new spreadsheet labelled gaant chart, this shows the name of the employee with the working dates for the year and has the following data:
Name 1/01/2014 2/01/2014 3/01/2014 6/01/2014 7/01/2014 8/01/2014 9/01/2014 10/01/2014
[code].....
What I would like for the macro to do is lookup Employee 1 from my gaant chart table and search the leave history data if it finds a relevant start date for the employee it marks that date with a 1, it then looks in the offset column and marks the number of offsets to the right. For example if employee 2 took leave from 1/1/14 to 3/1/14 the macro would mark a 1 on the 1/1/14, and search the offset column which would have 2, it will then mark a 1 in the 2 cells to the right of 1/1/14 also (these being 2/1/14 and 3/1/14)
The "Sum" sheet can change its number of rows. The pivot table is based on it. I'm having trouble with the SourceData portion of the code in my macro ....
Recently, the boss showed me a Pivot table & chart, which consists of a list of about 30 user names in the first column. The row headings were the different items they purchased from a vending machine. & when he clicked on any name in the first column, this created a new sheet, renamed with users name, with a small table of results showing what that person purchased.
Problem is, none of us can figure out how to do this. I have created a new Pivot table & chart exactly like the original, but I cannot get the smaller sheet to generate. (Excel 2010)
I have a table with list of equipments and asset number assigned as primary key, I wanted to have a history movement of these equipments but my idea is somehow not logical and very primitive to update every time 2 excel sheets:
I want to accomplish something like this but slightly different:
[url]
I have the same issue but a bit more complex. In my case I have an additional amount of columns (let's say dimensions). So for the example given (sales per month) I would add two columns for 4 different regions and 5 machine types (just examples).
The output (that would serve as pivot tabel input) would then have to be a 4 x 5 x 12 x 4 = 960 row database table.
I have tried to accomplish it by customizing the given code but that provedto be beyond my powers ;o)
and some 3rd party software [url]and [url] but non of them is doing what I'm looking for.
The code I'm looking for ideally would count the number of columns and unique records per column and construct the database table from that. So I am looking for flexibility in the number of dimensions also...
I have a static table that contains a list of all posible components for a particular product that we sell. The sheet successfully calculates the qty. of each component required for a particular install.
What I would like to be able to do is to create a new list able from the static one that only shows the components that are needed i.e. those that don't have a zero qty count.
I have seen some examples that come close on YouTube but can't find anything that does exactly this.
I need to create a macro that can create a dynamic copy/paste loop. So far what I have is horribly inefficient. Each row in colmn A(minus the header) has a unique number in it. For each unique number, I need to paste it based on the number of column headers in row 1(minus column A). So, if there are 20 column headers, I need to copy cell A2 and paste it 19 times in another sheet. Then, I need to move to the next number in column A and do the same thing. Here's what I have:
[Code] .........
You can see that this is not dynamic. If I add another row to my table and rerun the macro, it will not catch it. I've attached a sample file to show you the big picture of what I'm trying to do. The data that I have is in Sheet1, and I'm trying to get it into the format in Sheet3. Rows/columns will be periodically added to the table in Sheet1, so the macro needs to be dynamic to catch that. The data in Sheet3 will always remain, and the macro will add the updated data below the old data in Sheet3.
I'm trying to write a macro that will create a pivot table, and am getting an Error code 1004: Cannot Open Pivot Table Source File "Sheetname". My code is below. I've tried to note what each section does, and it all seems to work well except for the Pivot Table creation.
[qoute]I have five cells that I am working with cells A,B,C,D & E. cells B & C are time formated. Cells D & E are number formated and equal the number of hours between cells B & C. the difference is that if cell D is less that 2 hrs then cell E is to equal 2 hrs but if cell D is over 2 hrs then cell E equals the value of cell D. All this works fine but I am needing to add in the value of cell A. So if I have say CT in cell A then cell E will equal the value of cell D no matter if is less than 2 hrs[/quote]
in this example below i have record entrys of tps,rpm,etc etc in time. typically 4 records every 1 second.
how can i make the data 10 rows for every second?
i.e can we take the column readings after D i.e row2 then at row4 and then insert 8rows between the 2 and enter averages.
1353ecu *ABCDEF1LogIDLogEntryDateLogEntryTimeLogEntrySecondsTPSRPM21########13:47:190.55517.25491406.2532########13:47:190.80416.078431531.2543########13:47:191.03716.470591437.554########13:47:191.27223.529411343.75 Excel tables to the web >> Excel Jeanie HTML 4
the whole sheet is pretty big 1353ecu ABCDEFGHIJKLMNOPQ1LogIDLogEntryDateLogEntryTimeLogEntrySecondsTPSRPMBatteryCoolantTempAirTempTimingAdvO2SensorSpeedInjDutyCycleKnockSumLoad1BOctaneFlagknock_flag21########13:47:190.55517.25491406.2513.63938195.8122210.956483.2854850031.2100032########13:47:190.80416.078431531.2513.71271195.8122200.897923.2854850025.2100043########13:47:191.03716
copying a document that was made in word into Excel. I set the print area and page size but it seems I can keep adding columns. When I check the print preview it still shows the one page but it appears wider.Will it automatically create a new page if the page size limit is reached?
This is probably something very simple, but I need to find a way to stop row heights expanding when text in a cell wraps. Basically, I have a spreadsheet of projects, and one column is for 'notes'. When the text in this column starts to wrap, Excel automatically increases the row height to make all the text visible - but I don't want that to happen.
I guess there's a button to press somewhere, but I can't find it!!!
I have a bunch of nameds ranges that are essentially entire rows. For example, Rows 1 through 5 are named range "Range_A", "Range_B" is Row 10 through 20. I was looking for a way that I could have the value of one range assume the value and size of another range.
For example
Range_A = rows 1 through 5 Range_B = rows 10 through 20
Now, if I use the following macro all that happens is the first five rows of data is copied over to Range_B while Range_B's other 5 row values are not copied over.
Sub Example() Range("Range_A").Value = Range("Range_B").Value End Sub
What I want to have happen is have of Range_A expand to the same number of rows the other range has (which is 10 in this case) and assume the value of Range_B. Is there a way to do this? In my workbook I have a number of ranges that need to be replaced with other ranges and I want them to assume the size and values of the range they are supposed to equal.
I have a script that converts any List based Data Validation cell into a combo box when you click on it...What I am trying to do now is find a bit of code that will auto expand the combo box.
