When I update the pivot table with new data, the column where I have some percentage numbers the formatting changes. One number goes back to standard. The new numbers that enter the column also enters as standard numbers. How do I get the column to stay in percentage numbers?
I have the below vba and would like to have the '''event calculated ''' on any event on the worksheet. Right now it only update if updating the val1 thru val6 cells. How can i get the worksheet to update on any event or change on the sheet?
Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next fr = Range("a65536").End(xlUp).Row Set Rng = Range("a2" & ":n" & fr) If Intersect(Target, Rng) Is Nothing Then Exit Sub
I have a pivot table that I created and now I want to use the same pivot cache to create another pivot table instance on a different worksheet. how can I do that? My first worksheet gets saved as "OO By buyer" and now I want to create a new worksheet and drop the next pivot there.
I need to copy certain values of multiple pivot tables of different worksheets into a new worksheet.
To be more clear I attached an example worksheet:
In "Sheet1_pivot table" and "Sheet2_pivot table" I have two pivot table with data divided by Area, Year, quarter and month. In "Table" i have a summary table where, depending on the values of cell B2, B3 and B4, the summary table has different values copied from the 2 pivot tables.
I was thinking of a code like this but i have problems getting the values from the pivot tables
VB: Sub else_if() If Range("B2") = "North" And Range("B3") = "October" Then ElseIf Range("B2") = "South" And Range("B3") = "October" Then Else . . . . Else MsgBox ("Area not present") End If End Sub
I have a 208-tab workbook (4 employees with 52 submitted weekly timesheets). I need to consolidate and pivot job costs based on these submitted timesheets. There are two kinds of jobs: a list of products, from which the employee chooses through a drop-down list. There are also "Tracking Job ID" codes that are numbers between 7000 and an unknown ending number, which the employee types into the sheet. In both cases, the % of work performed is multiplied by the employees hourly rate and creates a total.
I need a pivot table that collects these totals from all the sheets.
I tried to use a VBA template from contextures.com just with a few examples from my workbook, but I don't know how to use VBA, so I failed. The only thing I did was substitute their example sheets with a few sheets from my workbook and re-wrote that part of the array formula.
I am using a count if to look at a column C in a worksheet a and return how many time the word 'on test' occurs. This is then returned to a table in another worksheet. I have 5 worksheets in total with the same columns, how can I do multiple countif - so that it will count all the 'on test' in column C for all 5 sheets.
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've tried my best to describe my dilema. I import data in an SQL query from a mainframe. This data shows a job number, site address, job number and a length. Multiple lengths on the same site appear as separate entries. I use a pivot table to calculate a total length and also formulas to determine whether a site is in an East or West area. I then need to then be able to copy the 'East' jobs to an 'East' worksheet and the 'West' jobs to the 'West' worksheet. But, I only want a site to appear once with the total length.
One of the best tools that Pivot Tables let's me use is the ability to create new tabs very easy. Anything field placed in the Report Filter is doable. My question is, is there a similar feature available in the regular data worksheet?
I have a worksheet in which I have a worksheet_change macro. This worksheet_change macro makes sure that a few cells will keep their colors, even if the user copies and pastes a new value to that cell. This worksheet_change macro runs each time there is a change on the worksheet. Now my problem is that on the same sheet I have an update list macro which updates around 20.000 rows and two columns (which is alltogether around 40.000 values) and it takes a while to run. So.. it takes a loooooooooot of time (too much) when these two macros both run.
My question is that can I somehow disable the worksheet_change macro while the update list macro runs. I mean something like when I start the update list macro to disable worksheet_change macro and when the update list macro finishes, then reenable worksheet_change macro?
i want to change the table_array reference without changing formula in every worksheet. I tried using a new worksheet and naming it the same as what is referenced in vlookup table_array but it messed up all the data.
I have to use a different worksheet every month so need a way to change reference OR how to change array data without messing up the worksheets with the vlookup
I am fairly new to macros and have trouble with VBA. I have a file with multiple worksheets. Each worksheet contains the name of a specific location in cell A8. I want this name in cell A8 to be the name on the worksheet tab for each worksheet in my file but do not know how to accomplish this. Is that even possible?
In cell A1, I have the month number (eg, 1, 2, 3,). The month number reflects current month and will automatically change with every month. For example, right now itís 6, next month it will automatically change to 7. Each two columns in Range A10:X20 represents the data from January to December. I want to use a worksheet event to change the background of the current month two columns in the range to yellow color and the two columns in the range will be visible when I activate this sheet.
looking to only allow a check ("x") in a column for a reconcile - type worksheet. Am I close?
Private Sub Worksheet_Change(ByVal Target As Range) 'Data protection. Only allow "x" in the "cleared" column. If anything else is entered, a message box informs the user 'and the cell contents are cleared. Dim val As Variant Dim msg As String If ActiveCell.Value "x" Then msg = "You can only enter an X in the cleared column." ActiveCell.ClearContents End If End Sub
I have data that develops 3 to 4 pivot table each day. I would like to know if there is a way to change the date on one of the pivot table and have the other pivot tables date change to match with the first pivot table. At this time I am going to all 3 or 4 pivot table to select the correct date. The date is in the page position of the pivot table. I have attached a small sample of the data and the pivot tables.
I would like to be able to select several non-sequential rows in a worksheet called "Data" (using a check box or just entering a value in Column A) and then be able to press a Command button to copy the selected rows to another worksheet called "Estimate" at the bottom of a table, and delete the designators in Column A (i.e. deletes the value, or unchecks the boxes) so I can repeat the process again if needed.
When I tried to Refresh the Pivot Table, the message I got was: The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. if you are changing the name of a PivotTable field, you must type a new name for the field. I did go in a check that all the columns are named though.
I've created a pivot table that summarizes sales and gross profit on a quarterly basis and YTD basis by Market and store. I have a drop down in my source data file which changes the month. I go to update the pivot table and it no longer recognizes the data fields because the headings change to the subsequent month. Is there a way to not have to recreate the pivot table every time a run a new period? See below. Once I run August and then refresh the data it will not recognize these fields any longer.
My problem is, when i add a new row to the main table, say with a date of "December 20, 2008", and refresh the pivot table, the information ends up clear at the bottom (in this case, after the year 2014).
I figured the pivot refresh would place the data among the other December 2008 rows in the pivot table.
I have a pivot table on a hidden sheet and a combo box the user picks from. I would like a macro or formula that could change my pivot table Page Value to that which is chosen from the combo box. Then update the pivot table. I can then use my report to look up values from this pivot as needed.
I created a Pivot Table with employees summarized or total hours, which also totals up their billable charges. I would like to "read" the pivot table & break it down to the employees weekly or monthly hours & billable.
I was going to use the following function, but can't use because there is not a column that finds the employee's name several times:
Also, I was going to upload an example & removed some confidential content from the original log file in which the pivot table(s) came from but don't see the pivot tables updating or giving error messages from lost data.
How does that pivot table update then? I hope I don't have to re-create it again.
I have a spreadsheet that has a top row of weeks ranging from 1/1/2012 to 1/1/2015
I am making a pivot table to show a sum of each column by user (in column A of the source). My problem is this, I need to show the dates and there related values in the top row of the pivot table, and the users as the rows. My problem is two fold - how to display dates across the top row of the pivot (only when a value exists) and then how to make sure the value is a sum rather than a count.
I have a pivot table I am try to update. The table references another tab where my data sheet exists. If I add data to the datasheet how can I get my pivot table to recognize the additional information.