Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Thumbs up

    I have searched the lounge and tried to "piece" together code to accomplish what i need done...no luck. I have a work sheet with individual initials in Column A [all initials in Column A are 3 characters in length and I have all 50 workbooks sorted alphabetically by the first letter of the initials ]. In Column B is an amount spent.

    What I need help on is vba or other method to run through column A and the when the initials change {i.e. ABC to ABD, from ABD to ABE, etc.] then insert 2 blank rows. Then sum the numbers in column B for that initial. The process continues until column A is blank [or least I hope that its blank---the initials were pulled from a SQL database--so I am assuming that since I don't "see" anything in column A, that it is really "blank".

    The worksheets do not have any named ranges, etc. though I could create them. Each worksheet varies in the amount of data in Column A [for example my first worksheet has only 600 rows of data---but several other worksheets have more than 2,500 rows of data.

    Can this process be coded or otherwise mechanized? THX.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Why not use subtotals? You won't get an extra blank row between the groups, but it's hardly any work. You can automate creating the subtotals:
    Code:
    Sub CreateSubtotals()
      Dim wsh As Worksheet
      For Each wsh In ActiveWorkbook.Worksheets
    	wsh.UsedRange.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), _
    	 Replace:=True, PageBreaks:=False, SummaryBelowData:=True
      Next wsh
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Post

    [quote name='HansV' post='765828' date='17-Mar-2009 12:11']Why not use subtotals? You won't get an extra blank row between the groups, but it's hardly any work. You can automate creating the subtotals:
    Code:
    Sub CreateSubtotals()
      Dim wsh As Worksheet
      For Each wsh In ActiveWorkbook.Worksheets
    [tab][/tab]wsh.UsedRange.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), _
    [tab][/tab] Replace:=True, PageBreaks:=False, SummaryBelowData:=True
      Next wsh
    End Sub
    [/quote]


    Hans,
    Wow! No, make that a double Wow...I have never used "sub-totals"...always created these manually.

    Is it possible to also insert a blank row after the sub-total? The boss wants the "break" the data between individiuals with at least one blank line after the sub-total. Thx.
    JimC

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='JimClawson' post='765834' date='17-Mar-2009 17:32']Is it possible to also insert a blank row after the sub-total? The boss wants the "break" the data between individiuals with at least one blank line after the sub-total.[/quote]
    The subtotals as provided by Excel are more or less automatic. To insert blank rows would be a lot more work.

  5. #5
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='765838' date='17-Mar-2009 12:40']The subtotals as provided by Excel are more or less automatic. To insert blank rows would be a lot more work.[/quote]


    Hans,
    Thanks for the advice...
    JimC

  6. #6
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Try this variation of HanV's macro:

    [codebox]Sub CreateSubtotals()
    Dim wsh As Worksheet
    Dim i As Long, lRow As Long
    For Each wsh In ActiveWorkbook.Worksheets
    wsh.UsedRange.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), _
    Replace:=True, PageBreaks:=False, SummaryBelowData:=True


    lRow = wsh.Cells(Rows.Count, 1).End(xlUp).Row
    For i = lRow To 1 Step -1
    If InStr(wsh.Cells(i, 1), "Total") > 0 Then
    wsh.Cells(i + 1, 1).EntireRow.Insert
    End If
    Next
    Next wsh
    End Sub[/codebox]

  7. #7
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts
    [quote name='mbarron' post='765844' date='17-Mar-2009 13:02']Try this variation of HanV's macro:

    [codebox]Sub CreateSubtotals()
    Dim wsh As Worksheet
    Dim i As Long, lRow As Long
    For Each wsh In ActiveWorkbook.Worksheets
    wsh.UsedRange.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), _
    Replace:=True, PageBreaks:=False, SummaryBelowData:=True


    lRow = wsh.Cells(Rows.Count, 1).End(xlUp).Row
    For i = lRow To 1 Step -1
    If InStr(wsh.Cells(i, 1), "Total") > 0 Then
    wsh.Cells(i + 1, 1).EntireRow.Insert
    End If
    Next
    Next wsh
    End Sub[/codebox][/quote]

    Mike,
    Thanks..Maybe I am doing something wrong, but your code places a blank row every row and it does not produce sub-totals by initials. I copied your code just like I did for Hans code to avoid typos, etc. Is there something I am missing? A blank row would make the boss--happy--at least temporarily anyway. Thx.
    JimC

  8. #8
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Would it be possible to post a sample book?

    I don't know if version matters - I'm using 2003. If you have 2007, please post a 2003 or earlier version of the file.

    I've attached a zip with before and after wokbooks.
    Attached Files Attached Files

  9. #9
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts
    [quote name='mbarron' post='765862' date='17-Mar-2009 14:31']Would it be possible to post a sample book?

    I don't know if version matters - I'm using 2003. If you have 2007, please post a 2003 or earlier version of the file.

    I've attached a zip with before and after wokbooks.[/quote]


    Mike,
    Let me look at your example further...Sorry for not providing all relevant information--I am using Excel 2002 [xp]. THANKS.
    JimC.

  10. #10
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Post

    [quote name='mbarron' post='765862' date='17-Mar-2009 14:31']Would it be possible to post a sample book?

    I don't know if version matters - I'm using 2003. If you have 2007, please post a 2003 or earlier version of the file.

    I've attached a zip with before and after wokbooks.[/quote]

    Mike,
    Thanks...I got it to work...somehow between working back and forth between the lounge and my excel file..a hidden column was in my worksheet...once I discovered it and deleted the column your code worked great...I checked some of my other worksheets and they worked on the first try. Again, thank you so much...I also learned something new along the way that I can re-use again.
    Take care.
    JimC

  11. #11
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts
    [quote name='mbarron' post='765862' date='17-Mar-2009 14:31']Would it be possible to post a sample book?

    I don't know if version matters - I'm using 2003. If you have 2007, please post a 2003 or earlier version of the file.

    I've attached a zip with before and after wokbooks.[/quote]
    Mike,
    I have a follow-up question and another favor to ask. I am trying to "learn" as I go through this process, so here is my question.

    My first 20+ worksheets were just like your example with no blank rows at the top of the worksheet. My column headings --salesperson and amount were in row 1 columns A and B. But then I had several worksheets that had 2-3 blank rows and the column heading was in row 3 or 4 [i.e. these worksheets were prepared by a customer from some sort of database extract process]. When I ran your code on the worksheets with the blank rows, I rec'd a message box, "Microsoft Excel cannot determine which row in your list or selection contains column labels, which are required for this command". I clicked "OK" and it still runs, but I have a sub-total in the blank row---which I delete.

    So my question in reviewing your code, what causes the assumption regarding the location of column headings? I know very little about VBA, but it would appear to me that its not your code, but an assumption of the sub-total function itself? Is this correct?

    Now my favor...The boss would like me to create a separate worksheet page with rows that contain only the sub-totals. So my sheet2 would have the same two columns, but only list initials in column A and sub-total amount in column B. Is it possible to modify your code to do this or should this be another macro that "reads" column A looking for the initial-total text to snag the amount in column B? I have so little knowledge of vba, I really can't determine if this task can be incorporated into the current macro or is too different and would needs it own separate macro?

    Thanks again for all of your help... JimC

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could create pivot tables to display only the subtotals. No code needed.

  13. #13
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='766021' date='18-Mar-2009 09:27']You could create pivot tables to display only the subtotals. No code needed.[/quote]

    Hans,
    Thanks...I have never used a pivot table...I guess its time for another learning experience.
    JimC

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •