Posts by klh166

Re: Subtotal Data in Number of Columns

Thanks for helping me out - I didn't see how the two fit together.

I ran the macro and I'm trying to make a couple adjustments. One - in the original file, column A had the heading "Category 1" and data below it, which is being used to group the subtotals. When I run the macro, it shifts my data one column to the right. Is there a way to keep the detail for "Category 1" in column A with the total (such as "A Total")?

Also, the subtotals end in the correct column (the 3rd-to-last column) but the columns with headings "Category 1" and "Category 2" should not have subtotals. Is there any way to assure that the subtotals don't begin until the 3rd column?

I've tried playing around with the macro, but I can't figure out how to make these changes. Thanks in advance for your help!

I apologize - I didn't have a chance to look at this again until this afternoon and you have been so quick to help me.

PCI -Thanks for taking a look at this. Your code puts subtotals in only the 3rd-to-last column, but I need subtotals in all columns from C through the 3rd to last column. Any ideas for how I can get subtotals in those other columns as well?

Thanks!
Kat

Here's a sample. In this file, the columns I want to subtotal would be C through O. But depending on when this file would be generated the last column might not be O.

If it's possible to accomplish this without using subtotals (inserting a row and sums at each change in column A, grouping the detail and totals and adding a grand total), that would also work.

Thanks Ger, but that didn't work for me. I got an error when it got to the code for the subtotal.

Is it possible to create the text for the array by looping from 3 through the count of my last column? Would that even work?

Hello,

I have data beginning in column C that I want to subtotal by change in column A. The problem is, I cannot hard-code the columns I need to subtotal because I may have more or less columns depending on the month. I know that the last column I need to subtotal will be my last column - 3. Is there a way to define the TotalList part of the subtotal code to include this range of columns?

Sub Subtotal()


    Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3, 4, 5, 6, _
        7, 8, 9, 10, 11, 12, 13, 14, 15), Replace:=True, PageBreaks:=False, _
        SummaryBelowData:=True


End Sub

In the example above (where I recorded the subtotal) TotalList:=Array(3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15). I want to reference an array that starts with column 3 and ends with the column three columns before my last column. Is there a way to do that?

Thanks so much!
Kat

Re: Select first row of filtered data

Perfect! Thanks so much!!

Re: Select first row of filtered data

This selected cell B2, but that is hidden by the filter. It looks like (for whatever reason) this is selecting the second cell in the column, not the second visible cell.

Re: Select first row of filtered data

Thanks SMC, but when I used this, it selected cell B1, which is the header of the column I filtered. I need to select the first visible row below that.

Hello!

I created a macro to sort the data in my worksheet by column B. Then it filters column B. Now I need to select the first row of filtered data but I have no idea how to do it. Any suggestions?

Thanks in advance for your help!
Kat

Re: Change Background Color When Value In Column Changes

Thanks PCI! I'm going with your second answer - it works perfectly!

And thanks for posting the sample of how to do it with conditional formatting. I prefer the code for the spreadsheet this needs to generate, but I wouldn't have understood how to apply conditional formatting (in this case) without your example.

Hello,

I'm trying to find a way to use VBA to apply background color to my worksheet based on the value in Column A. The values start in cell A2. I would like that whole row to be light grey, then continue applying a light grey background until I reach a new value. Then no background until the value changes again, then back to light grey. It would alternate until it reaches a blank cell.

I attached an example. The tab named "P2 Expense Report (2)" is the one I need to color. The tab named "P2 Expense Report with Color" illustrates how I want my file to look after color is applied.

Thanks for your help!
Kat

Re: Move Headings To Left-Most Column Of Each Row

Thanks so much! Your first answer was great - second even better! I'll be taking out the rows with headers, so having blanks there will be a great help!!

Re: Copy Cell & Paste Into Used Row Range

I promise I tried your suggestion several times with no luck. I gave it one more shot and it worked! No idea what I did wrong before. Probably something else I had stuck in there that was breaking it. Thanks for your help snb! Very relieved to have this worked out.

Re: Copy Cell & Paste Into Used Row Range

Thanks, but none of these options is selecting the cells I need to put my formula into or otherwise linking the data on my "P2 Expense Report (2)" tab to the "P2 Expense Report" tab. Any other ideas?

Re: Copy Cell & Paste Into Used Row Range

Neither option worked for me. The first gave me Run-time error '1004' (Application-defined or object-defined error) and the second didn't select any range.

In the sample file I attached, this code should select the range C5:O26. But I can't just use that range because my file may be modified to include additional rows or columns.

Re: Select Range With Blank Rows And Paste Formula

The following seems to work....until it selects the range where I want to paste my formula. I tried recording the way that I'd select it manually. But it doesn't translate into my macro. Any suggestions?

Sub Create_P2_Expense_Summary()
' Create a Copy of the P2 Expense Report Tab
    Sheets("P2 Expense Report").Select
    Sheets("P2 Expense Report").Copy Before:=Sheets(1)


' Link Cell C5 from 'P2 Expense Report (2)' to Cell C5 from 'P2 Expense Report'
    Sheets("P2 Expense Report (2)").Range("C5") = "='P2 Expense Report'!RC"


' Copy formula in cell C5
    Sheets("P2 Expense Report (2)").Range("C5").Select
    Selection.Copy


'Select all data in the P2 Expense Report (2) tab
    Application.Goto Reference:="R5C3:R65536C256"
    Range(Selection, Selection.End(xlUp)).Select
    Range(Selection, Selection.End(xlToLeft)).Select


' Paste Formulas referencing data in the P2 Expense Report tab
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False


End Sub

Re: Select Range With Blank Rows And Paste Formula

Thanks for your reply. I know my code is lousy. I need to create something quickly and I'm trying to piece it together with my virtually non-existent VB skills.

I don't see the circular reference. I know at this point both tabs are identical. The macro I'm trying to create will copy the first sheet 'P2 Expense Report' and then link the copy to the original sheet. Then I need to manipulate the copy to sort and subtotal the data differently.

So this is the part where I'm trying to get the data from the copied sheet to link to the original sheet. I'm sure there's a better way to write the code and I hope to clean it up at some point but for now I'll settle for something that works.

Could you please tell me where I've got a circular reference? Thanks!

I have a formula in cell C5 which refers to another tab in my workbook. I want to copy this formula, select the range beginning with cell C5 and extending as far right and down as I have data. I have blank rows in this range. I don't care if my formula copies into the blank rows - it just makes selecting the range a little more complicated than if it didn't.

I had some code that seemed to be working but now it's not. Here's what I was using.

' Link Cell C5 from 'P2 Expense Report (2)' to Cell C5 from 'P2 Expense Report'
    Application.Goto Reference:="R5C3"
    ActiveCell.FormulaR1C1 = "='P2 Expense Report'!RC"


' Copy formula in cell C5
    Application.Goto Reference:="R5C3"
    Selection.Copy


'Select all data in the P2 Expense Report (2) tab
    Sheets("P2 Expense Report (2)").Select
    Range("C65536").End(xlUp).Select
    ActiveCell.Offset(0, -1).Range("A1").Select
    ActiveCell.FormulaR1C1 = " "
    Range(Range("IV3").End(xlToLeft), Range("C65536").End(xlUp)).Name = "MyRange"
    Range("MyRange").Select


' Paste Formulas referencing data in the P2 Expense Report tab
    ActiveSheet.Paste

I also attached a sample of my file for reference.

Thanks so much!
Kat

Re: Moving Headings From Top Of Column To Left-most Column Of Each Row

Here's an example - I hope this will help.

The following rows have the headings and descriptions: 4, 8, 9, 14, 15, 19, 20, 26.

I need to take the data from cell C4 (123.456.7890.123.456) and put it into cells A5 through A7. I also need the data from cell D4 (Fruits) to be put into cells B5 through B7.

The number of items under each heading could be increased in a later month. So in addition to apples, oranges and grapes, someday I might also have pineapples and peaches too. So the number of rows between headers will not be constant.

Please let me know if there's anything else I can do to try to clarify. Thanks!

I have a mix of information in column C. There are headings with data below them. I need to pull the headings into column A beside the data. I also need to pull the corresponding data from column D into column B.

I can distinguish the headings from the data by length. The headings are of various lengths (longer than 12 characters), but the data below it is always 12 characters long. So if the length of the data in cell C4 is not equal to 12, it's a heading. I don't need to put anything into A4 and B4 because I want to delete headings later.

So I move onto cell C5. If the length of cell C5 is equal to 12, then I want the last heading (from C4) copied into cell A5 and the name of the heading (from D4) copied into cell B5.

And so on - if the length of cell C6 is equal to 12, then the data from C4 would be copied into cell A6 and the data from D4 would be copied into cell B6. If the length of cell C6 is not equal to 12, this would be my new heading to use for the cells below (until a new heading is found).

This should continue until it finds a cell in column C that is blank.

Then delete the headings (which should not have data in columns A and B).

This would always begin with the first heading in C4 and the name of the heading in cell D4.

Thanks for your help!!
Kat