Re: Creating single pivot table from multiple sheets
thank you. But this gives me 2 pivot tables on one page.
I need to have only one pivot table which is pulling data from multiple sheets.
Thank you again
Re: Creating single pivot table from multiple sheets
thank you. But this gives me 2 pivot tables on one page.
I need to have only one pivot table which is pulling data from multiple sheets.
Thank you again
Hi, I need to create a single pivot from multiple sheets which have the same exact data.
i have attached a sample excel file ., there is a pivot table which has pulled the data from the first sheet.
How can i create a single pivot from multiple sheets?
Thank you for your help.
Hi,
I am currently trying to use the current formula. Now in the formula, I am trying to reference the formula in 2nd line to a cell in a different sheet called"standard" the Row 66 and C21 is the cell. [The cell being (AD1) ] But when I run the macros for different reports it does not always reference to the same cell. not sure of the reason.
Can someone help me lock the formula, so that it always references to Cell AD1 in the sheet standard??
[
Hi i am using the below for a selecting a dynamic range, but when i run the macro , I get the result to be "Name?"
Can anybody edit the code for me??
[/COLOR] Range("AD4").Select Set myRange = ActiveSheet.Range(("AD4"), Range("AD4").End(xlDown)) Range("AD1").Select ActiveCell.Formula = "=Subtotal(9,(myRange))"[COLOR=#333333]
[/COLOR]
Re: Sub total for dynamic range
But the above solution was to make a new macro!
but i need it as a formula. Here is what I am using
Range("G" & Cells(Rows.Count, "E").End(xlUp).Row + 2).Select ActiveCell.FormulaR1C1 = "Total Overhead"
Range("I" & Cells(Rows.Count, "E").End(xlUp).Row + 1).Select
ActiveCell.FormulaR1C1 = "=SUM(Standard!R[-36]C[21]:R[9963]C[21])"
[COLOR=#333333][/COLOR]
Can somebody help me edit the code, so that the last number is not 9963 but the last line in the column??
The starting line is always going to R[-36]C[21], but is there a way we can fix the starting point??
Thanks!
Re: Sub total for dynamic range
Here is the code. I have put the range to be 10002, but the number of rows are more than that. Can somebody edit the code to pick up any number of rows..
VB:
Range("AD1").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[3]C:R[10002]C)"
Range("AD2").Select
ActiveWindow.SmallScroll Down:=-18
Range("AD1").Select
Thanks
Hi I need to edit the formula for subtotaling in a dynamic range.
Sheet 1 : Standard: I need to subtotal in column B.
Here is the VBA code I am using;
Range("B1").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[2]C:R[10000]C)"
Can some help me edit the code, so that the formula works for any number of rows?
Sheet 1 : Standard: I need to total in column I
I need to total 2 different columns at the bottom of the pivot table. But the number of rows are not going to be same, so can some body please help me edit the code, so that if totals 2 columns as per the formula at the bottom of the pivot
Here is the code:
ActiveWindow.SmallScroll Down:=21
Range("F40").Select
ActiveCell.FormulaR1C1 = "Total"
ActiveCell.FormulaR1C1 = _
"=GETPIVOTDATA(""Calc Cost"",R3C1,""Cost"",""05-LO"")+GETPIVOTDATA(""Calc Cost"",R3C1,""Cost"",""04-MO"")"
Thanks
Re: Macros for filtering data in Pivot
No leads upon this??? Can somebody help me with this??
Hi,
I have a macros code, for creating a pivot table from some raw data.
Now, the problem is I need a code for filtering out certain set of values.
I have attached a sample file with two sheet.
1. Test Pivot, it is how the pivot table looks like after I run the macros for building the table.
2. Output. This is the file,which I really want my output to look like. The additional step which I wish my macro to perform is filter out activities ABC.4XX. I.e. all activities starting with ABC.4XX should not be visible.
Can some one help me with the code, so that I can go ahead and edit my macros code???
Thanks!!!
Hi I need the code for formatting to be edited, so that it work dynamically for any range
Range("B16:L16").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Display More
Here the columns "B" and "L" shall be constant, but the number 16 may vary depending upon the number of lines in the sheet
Can somebody help me edit the code for the same???
Hi, I need to do a conditional formatting on a particular column. But the rows shall vary.
Here is the code, I got when I recorded the macros
Macro73 Macro
'
'
Range("L7").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).StopIfTrue = False
End Sub
Display More
Can somebody help me editing it so that he formula stands good, for any number of rows the column would have.
Re: Macros for totalling the rows
I would prefer a macro code, as it is going to be a part of a already existing code.
So if any body can help me editing the above code I posted, will be really great.
Thanks!!
Hi,
I need to insert the data after the last line in particular column. The number of lines might differ every time. I want the data to be inserted after leaving 2 blank lines after the last line in the column. Any idea on how bout doing it??
Here is what i tried
Re: Formula for summing up
Ain't there a formula to the count the values in a particular range between the starting and the last entry??
Can somebody please help me on this??
Thanks!!
Re: Formula for summing up.
Quote from cytop;639742Please explain your issue accurately and fully in the thread text.
If you can't be bothered to at least explain your issue, why should anyone bother to download your workbook, virus check it (Nothing personal!), Open it, review your comments and then, perhaps, offer a suggestion...
Ok, I thought I would be able to put my words across properly.I shall try explaining
I need a sum / count up few values in a particular range. The problem is I need to sum / count it up from the first entry to the last including 0 values as well.
Can somebody help me with the formula.
I have attached file for ref.
Thanks!!
Hi ,
Can somebody help me with a formula as the per the scenario in the file attached??
Thanks!!
Re: Macros for totalling the rows
Here is the tags
Range("D34").Select
ActiveCell.FormulaR1C1 = "GE"
Range("D35").Select
ActiveCell.FormulaR1C1 = "MBO"
Range("E34").Select
ActiveCell.FormulaR1C1 = "=SUMIF(R7C1:R30C1,""GE"",R7C:R30C)"
Range("E35").Select
ActiveCell.FormulaR1C1 = "=SUMIF(R7C1:R30C1,""MBO"",R7C:R30C)"
Now here the problem is the the row D34 and D35 will depend upon the number of rows in column 1.
Therefore I want that the D34 and D35 data be inserted into the sheet my macros after checking the number of rows.
Also I need to edit the sumif formulae so that It is not for only for rows 7 to 30 but for all the rows present in the column
Re: Macros for totalling the rows
Can somebody help me with the code please???
I am not sure, how do I make the macros insert the formula at the end of the column??
Re: Macros for totalling the rows
Here is the present code
Range("F33").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-26]C:R[-3]C)"
Range("F34").Select
But now here the range 'F33' is not constant, it will change with the number of rows.
Can someone help me with the code??
Hi,
I have few columns, which I need to sum them up at the bottom.
Now the problem is the number of rows will not be same.
Can any one help me up with the macros code for it??