Posts by praneetbvb

    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.


    • sample.xls

      (78.85 kB, downloaded 88 times, last: )


    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??


    1. Range("I" & Cells(Rows.Count, "E").End(xlUp).Row + 1).Select
    2. ActiveCell.FormulaR1C1 = "=Standard!R[-66]C[21]"

    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??

    1. [/COLOR] Range("AD4").Select Set myRange = ActiveSheet.Range(("AD4"), Range("AD4").End(xlDown)) Range("AD1").Select ActiveCell.Formula = "=Subtotal(9,(myRange))"[COLOR=#333333]


    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

    1. Range("G" & Cells(Rows.Count, "E").End(xlUp).Row + 2).Select ActiveCell.FormulaR1C1 = "Total Overhead"
    2. Range("I" & Cells(Rows.Count, "E").End(xlUp).Row + 1).Select
    3. ActiveCell.FormulaR1C1 = "=SUM(Standard!R[-36]C[21]:R[9963]C[21])"
    4. [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??


    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..


    1. Range("AD1").Select
    2. ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[3]C:R[10002]C)"
    3. Range("AD2").Select
    4. ActiveWindow.SmallScroll Down:=-18
    5. Range("AD1").Select


    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;

    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
    ActiveCell.FormulaR1C1 = "Total"
    ActiveCell.FormulaR1C1 = _
    "=GETPIVOTDATA(""Calc Cost"",R3C1,""Cost"",""05-LO"")+GETPIVOTDATA(""Calc Cost"",R3C1,""Cost"",""04-MO"")"



    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???



    • Sample 1.xlsx

      (11.34 kB, downloaded 79 times, last: )

    Hi I need the code for formatting to be edited, so that it work dynamically for any range

    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

    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.



    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

    1. Range("B7").Select
    2. Range(Selection, Selection.End(xlDown)).Select

    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??


    Re: Formula for summing up.

    Quote from cytop;639742

    Please 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.


    Re: Macros for totalling the rows

    Here is the tags

    1. Range("D34").Select
    2. ActiveCell.FormulaR1C1 = "GE"
    3. Range("D35").Select
    4. ActiveCell.FormulaR1C1 = "MBO"
    5. Range("E34").Select
    6. ActiveCell.FormulaR1C1 = "=SUMIF(R7C1:R30C1,""GE"",R7C:R30C)"
    7. Range("E35").Select
    8. 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

    Here is the present code

    ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-26]C:R[-3]C)"

    But now here the range 'F33' is not constant, it will change with the number of rows.

    Can someone help me with the code??