Auto totalize in Ron de Bruin script

A) I use Ron de Bruin's script "Copy records with the same value in a
column to a new sheet or workbook". How to add VB code that totalize
one of the columns and write the value under the last row?

See the script http://www.rondebruin.nl/copy5.htm


B) How to exclude sheet "name" from being summerized?

See the sript: http://www.rondebruin.nl/summary.htm


Bart

0
7/28/2007 9:34:15 AM
excel 39879 articles. 2 followers. Follow

6 Replies
677 Views

Similar Articles

[PageSpeed] 35

Hi Bart


A:

This will add a Sum formula in column C

Range("C" & Rows.Count).End(xlUp).Offset(2, 0).FormulaR1C1 = "=SUM(R1C:R[-2]C)"


B:

One way

You can fill in the sheet names in the array that you want to exclude


    For Each Sh In Basebook.Worksheets
        If Sh.Name <> Newsh.Name And Sh.Visible Then

            If IsError(Application.Match(Sh.Name, _
                            Array("Sheet1", "Sheet3"), 0)) Then

                ColNum = 1
                RwNum = RwNum + 1
                'Copy the sheet name in the A column
                Newsh.Cells(RwNum, 1).Value = Sh.Name

                For Each myCell In Sh.Range("A1,D5:E5,Z10")    '<--Change the range
                    ColNum = ColNum + 1
                    Newsh.Cells(RwNum, ColNum).Formula = _
                    "='" & Sh.Name & "'!" & myCell.Address(False, False)
                Next myCell

            End If

        End If
    Next Sh



-- 

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"AA Arens" <bartvandongen@gmail.com> wrote in message news:1185615255.528629.86460@i13g2000prf.googlegroups.com...
> A) I use Ron de Bruin's script "Copy records with the same value in a
> column to a new sheet or workbook". How to add VB code that totalize
> one of the columns and write the value under the last row?
> 
> See the script http://www.rondebruin.nl/copy5.htm
> 
> 
> B) How to exclude sheet "name" from being summerized?
> 
> See the sript: http://www.rondebruin.nl/summary.htm
> 
> 
> Bart
>
0
rondebruin (3790)
7/28/2007 12:01:49 PM
For (A):

I have placed under the DIM \codes:

Worksheets("TelkomSel").Range("Z:AH").Delete Shift:=xlShiftToLeft
Worksheets("TelkomSel").Range("W:W").Delete Shift:=xlShiftToLeft
Worksheets("TelkomSel").Range("L:M").Delete Shift:=xlShiftToLeft
Worksheets("TelkomSel").Range("F:J").Delete Shift:=xlShiftToLeft

because I first want to clean up the stuff.

2nd: I have also the headers copied, so the summary should start on
the second row instead of the first. How will be the code you gave me
and where do I have to place it in the present script I mentioned
under A.

For (B): Where to add this code in the script I mentioned in B?

Bart




Ron de Bruin wrote:
> Hi Bart
>
>
> A:
>
> This will add a Sum formula in column C
>
> Range("C" & Rows.Count).End(xlUp).Offset(2, 0).FormulaR1C1 = "=SUM(R1C:R[-2]C)"
>
>
> B:
>
> One way
>
> You can fill in the sheet names in the array that you want to exclude
>
>
>     For Each Sh In Basebook.Worksheets
>         If Sh.Name <> Newsh.Name And Sh.Visible Then
>
>             If IsError(Application.Match(Sh.Name, _
>                             Array("Sheet1", "Sheet3"), 0)) Then
>
>                 ColNum = 1
>                 RwNum = RwNum + 1
>                 'Copy the sheet name in the A column
>                 Newsh.Cells(RwNum, 1).Value = Sh.Name
>
>                 For Each myCell In Sh.Range("A1,D5:E5,Z10")    '<--Change the range
>                     ColNum = ColNum + 1
>                     Newsh.Cells(RwNum, ColNum).Formula = _
>                     "='" & Sh.Name & "'!" & myCell.Address(False, False)
>                 Next myCell
>
>             End If
>
>         End If
>     Next Sh
>
>
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
> "AA Arens" <bartvandongen@gmail.com> wrote in message news:1185615255.528629.86460@i13g2000prf.googlegroups.com...
> > A) I use Ron de Bruin's script "Copy records with the same value in a
> > column to a new sheet or workbook". How to add VB code that totalize
> > one of the columns and write the value under the last row?
> >
> > See the script http://www.rondebruin.nl/copy5.htm
> >
> >
> > B) How to exclude sheet "name" from being summerized?
> >
> > See the sript: http://www.rondebruin.nl/summary.htm
> >
> >
> > Bart
> >

0
7/28/2007 4:22:45 PM
A: 

Which macro do you use from that page?


B: 

You see this block in the macro

 For Each Sh In Basebook.Worksheets
.....
....
...
  Next Sh

Replace this with the code I posted


I must go now so I will read your reply tomorrow



-- 

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"AA Arens" <bartvandongen@gmail.com> wrote in message news:1185639765.970429.79610@m37g2000prh.googlegroups.com...
> For (A):
> 
> I have placed under the DIM \codes:
> 
> Worksheets("TelkomSel").Range("Z:AH").Delete Shift:=xlShiftToLeft
> Worksheets("TelkomSel").Range("W:W").Delete Shift:=xlShiftToLeft
> Worksheets("TelkomSel").Range("L:M").Delete Shift:=xlShiftToLeft
> Worksheets("TelkomSel").Range("F:J").Delete Shift:=xlShiftToLeft
> 
> because I first want to clean up the stuff.
> 
> 2nd: I have also the headers copied, so the summary should start on
> the second row instead of the first. How will be the code you gave me
> and where do I have to place it in the present script I mentioned
> under A.
> 
> For (B): Where to add this code in the script I mentioned in B?
> 
> Bart
> 
> 
> 
> 
> Ron de Bruin wrote:
>> Hi Bart
>>
>>
>> A:
>>
>> This will add a Sum formula in column C
>>
>> Range("C" & Rows.Count).End(xlUp).Offset(2, 0).FormulaR1C1 = "=SUM(R1C:R[-2]C)"
>>
>>
>> B:
>>
>> One way
>>
>> You can fill in the sheet names in the array that you want to exclude
>>
>>
>>     For Each Sh In Basebook.Worksheets
>>         If Sh.Name <> Newsh.Name And Sh.Visible Then
>>
>>             If IsError(Application.Match(Sh.Name, _
>>                             Array("Sheet1", "Sheet3"), 0)) Then
>>
>>                 ColNum = 1
>>                 RwNum = RwNum + 1
>>                 'Copy the sheet name in the A column
>>                 Newsh.Cells(RwNum, 1).Value = Sh.Name
>>
>>                 For Each myCell In Sh.Range("A1,D5:E5,Z10")    '<--Change the range
>>                     ColNum = ColNum + 1
>>                     Newsh.Cells(RwNum, ColNum).Formula = _
>>                     "='" & Sh.Name & "'!" & myCell.Address(False, False)
>>                 Next myCell
>>
>>             End If
>>
>>         End If
>>     Next Sh
>>
>>
>>
>> --
>>
>> Regards Ron de Bruin
>> http://www.rondebruin.nl/tips.htm
>>
>>
>> "AA Arens" <bartvandongen@gmail.com> wrote in message news:1185615255.528629.86460@i13g2000prf.googlegroups.com...
>> > A) I use Ron de Bruin's script "Copy records with the same value in a
>> > column to a new sheet or workbook". How to add VB code that totalize
>> > one of the columns and write the value under the last row?
>> >
>> > See the script http://www.rondebruin.nl/copy5.htm
>> >
>> >
>> > B) How to exclude sheet "name" from being summerized?
>> >
>> > See the sript: http://www.rondebruin.nl/summary.htm
>> >
>> >
>> > Bart
>> >
>
0
rondebruin (3790)
7/28/2007 5:16:07 PM
On Jul 29, 12:16 am, "Ron de Bruin" <rondebr...@kabelfoon.nl> wrote:
> A:
>
> Which macro do you use from that page?
>
> B:
>
> You see this block in the macro
>
>  For Each Sh In Basebook.Worksheets
> ....
> ...
> ..
>   Next Sh
>
> Replace this with the code I posted
>
> I must go now so I will read your reply tomorrow
>
> --
>
> Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm
>
> "AA Arens" <bartvandon...@gmail.com> wrote in messagenews:1185639765.970429.79610@m37g2000prh.googlegroups.com...
> > For (A):
>
> > I have placed under the DIM \codes:
>
> > Worksheets("TelkomSel").Range("Z:AH").Delete Shift:=xlShiftToLeft
> > Worksheets("TelkomSel").Range("W:W").Delete Shift:=xlShiftToLeft
> > Worksheets("TelkomSel").Range("L:M").Delete Shift:=xlShiftToLeft
> > Worksheets("TelkomSel").Range("F:J").Delete Shift:=xlShiftToLeft
>
> > because I first want to clean up the stuff.
>
> > 2nd: I have also the headers copied, so the summary should start on
> > the second row instead of the first. How will be the code you gave me
> > and where do I have to place it in the present script I mentioned
> > under A.
>
> > For (B): Where to add this code in the script I mentioned in B?
>
> > Bart
>
> > Ron de Bruin wrote:
> >> Hi Bart
>
> >> A:
>
> >> This will add a Sum formula in column C
>
> >> Range("C" & Rows.Count).End(xlUp).Offset(2, 0).FormulaR1C1 = "=SUM(R1C:R[-2]C)"
>
> >> B:
>
> >> One way
>
> >> You can fill in the sheet names in the array that you want to exclude
>
> >>     For Each Sh In Basebook.Worksheets
> >>         If Sh.Name <> Newsh.Name And Sh.Visible Then
>
> >>             If IsError(Application.Match(Sh.Name, _
> >>                             Array("Sheet1", "Sheet3"), 0)) Then
>
> >>                 ColNum = 1
> >>                 RwNum = RwNum + 1
> >>                 'Copy the sheet name in the A column
> >>                 Newsh.Cells(RwNum, 1).Value = Sh.Name
>
> >>                 For Each myCell In Sh.Range("A1,D5:E5,Z10")    '<--Change the range
> >>                     ColNum = ColNum + 1
> >>                     Newsh.Cells(RwNum, ColNum).Formula = _
> >>                     "='" & Sh.Name & "'!" & myCell.Address(False, False)
> >>                 Next myCell
>
> >>             End If
>
> >>         End If
> >>     Next Sh
>
> >> --
>
> >> Regards Ron de Bruin
> >>http://www.rondebruin.nl/tips.htm
>
> >> "AA Arens" <bartvandon...@gmail.com> wrote in messagenews:1185615255.528629.86460@i13g2000prf.googlegroups.com...
> >> > A) I use Ron de Bruin's script "Copy records with the same value in a
> >> > column to a new sheet or workbook". How to add VB code that totalize
> >> > one of the columns and write the value under the last row?
>
> >> > See the scripthttp://www.rondebruin.nl/copy5.htm
>
> >> > B) How to exclude sheet "name" from being summerized?
>
> >> > See the sript:http://www.rondebruin.nl/summary.htm
>
> >> > Bart

Ron, I uses the automatic one: Copy_With_AdvancedFilter_To_Worksheets()

0
7/29/2007 3:07:37 AM
Hi Bart

This example have a sheet with the data named "Sheet1" and before it split the data it delete the columns you want in this sheet
I will add a sum formula in column C of every sheet it create

Maybe you want to change the column in the filter range ?
 Set rng = ws1.Range("A1:IV" & Rows.Count)



Sub Copy_With_AdvancedFilter_To_Worksheets()
    Dim CalcMode As Long
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim WSNew As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim Lrow As Long

    Set ws1 = Sheets("Sheet1")  '<<< Change

    With ws1
        .Range("Z:AH").Delete Shift:=xlShiftToLeft
        .Range("W:W").Delete Shift:=xlShiftToLeft
        .Range("L:M").Delete Shift:=xlShiftToLeft
        .Range("F:J").Delete Shift:=xlShiftToLeft
    End With

    'Set filter range : A1 is the top left cell of your filter range and
    'the header of the first column, D is the last column in the filter range
    Set rng = ws1.Range("A1:IV" & Rows.Count)

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    ' Add a worksheet to copy the a unique list and add the CriteriaRange
    Set ws2 = Worksheets.Add

    With ws2
        'This example filters on the first column in the range
        'first we copy the Unique data from this column to ws2
        rng.Columns(1).AdvancedFilter _
                Action:=xlFilterCopy, _
                CopyToRange:=.Range("B1"), Unique:=True

        'Then give A1 the same value as B1 (header of column 1) in ws2
        .Range("A1").Value = .Range("B1").Value

        'loop through the unique list in ws2 and filter/copy to a new sheet
        Lrow = .Cells(Rows.Count, "B").End(xlUp).Row
        For Each cell In .Range("B2:B" & Lrow)
            .Range("A2").Value = "=" & Chr(34) & "=" & cell.Value & Chr(34)
            Set WSNew = Sheets.Add
            On Error Resume Next
            WSNew.Name = cell.Value
            If Err.Number > 0 Then
                MsgBox "Change the name of : " & WSNew.Name & " manually"
                Err.Clear
            End If
            On Error GoTo 0
            rng.AdvancedFilter Action:=xlFilterCopy, _
                               CriteriaRange:=.Range("A1:A2"), _
                               CopyToRange:=WSNew.Range("A1"), _
                               Unique:=False

            WSNew.Range("C" & Rows.Count).End(xlUp).Offset(2, 0).FormulaR1C1 = "=SUM(R1C:R[-2]C)"

            WSNew.Columns.AutoFit
        Next

        'Delete the ws2 sheet
        On Error Resume Next
        Application.DisplayAlerts = False
        .Delete
        Application.DisplayAlerts = True
        On Error GoTo 0

    End With

    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With
End Sub


-- 

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"AA Arens" <bartvandongen@gmail.com> wrote in message news:1185678457.098186.295270@x40g2000prg.googlegroups.com...
> On Jul 29, 12:16 am, "Ron de Bruin" <rondebr...@kabelfoon.nl> wrote:
>> A:
>>
>> Which macro do you use from that page?
>>
>> B:
>>
>> You see this block in the macro
>>
>>  For Each Sh In Basebook.Worksheets
>> ....
>> ...
>> ..
>>   Next Sh
>>
>> Replace this with the code I posted
>>
>> I must go now so I will read your reply tomorrow
>>
>> --
>>
>> Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm
>>
>> "AA Arens" <bartvandon...@gmail.com> wrote in messagenews:1185639765.970429.79610@m37g2000prh.googlegroups.com...
>> > For (A):
>>
>> > I have placed under the DIM \codes:
>>
>> > Worksheets("TelkomSel").Range("Z:AH").Delete Shift:=xlShiftToLeft
>> > Worksheets("TelkomSel").Range("W:W").Delete Shift:=xlShiftToLeft
>> > Worksheets("TelkomSel").Range("L:M").Delete Shift:=xlShiftToLeft
>> > Worksheets("TelkomSel").Range("F:J").Delete Shift:=xlShiftToLeft
>>
>> > because I first want to clean up the stuff.
>>
>> > 2nd: I have also the headers copied, so the summary should start on
>> > the second row instead of the first. How will be the code you gave me
>> > and where do I have to place it in the present script I mentioned
>> > under A.
>>
>> > For (B): Where to add this code in the script I mentioned in B?
>>
>> > Bart
>>
>> > Ron de Bruin wrote:
>> >> Hi Bart
>>
>> >> A:
>>
>> >> This will add a Sum formula in column C
>>
>> >> Range("C" & Rows.Count).End(xlUp).Offset(2, 0).FormulaR1C1 = "=SUM(R1C:R[-2]C)"
>>
>> >> B:
>>
>> >> One way
>>
>> >> You can fill in the sheet names in the array that you want to exclude
>>
>> >>     For Each Sh In Basebook.Worksheets
>> >>         If Sh.Name <> Newsh.Name And Sh.Visible Then
>>
>> >>             If IsError(Application.Match(Sh.Name, _
>> >>                             Array("Sheet1", "Sheet3"), 0)) Then
>>
>> >>                 ColNum = 1
>> >>                 RwNum = RwNum + 1
>> >>                 'Copy the sheet name in the A column
>> >>                 Newsh.Cells(RwNum, 1).Value = Sh.Name
>>
>> >>                 For Each myCell In Sh.Range("A1,D5:E5,Z10")    '<--Change the range
>> >>                     ColNum = ColNum + 1
>> >>                     Newsh.Cells(RwNum, ColNum).Formula = _
>> >>                     "='" & Sh.Name & "'!" & myCell.Address(False, False)
>> >>                 Next myCell
>>
>> >>             End If
>>
>> >>         End If
>> >>     Next Sh
>>
>> >> --
>>
>> >> Regards Ron de Bruin
>> >>http://www.rondebruin.nl/tips.htm
>>
>> >> "AA Arens" <bartvandon...@gmail.com> wrote in messagenews:1185615255.528629.86460@i13g2000prf.googlegroups.com...
>> >> > A) I use Ron de Bruin's script "Copy records with the same value in a
>> >> > column to a new sheet or workbook". How to add VB code that totalize
>> >> > one of the columns and write the value under the last row?
>>
>> >> > See the scripthttp://www.rondebruin.nl/copy5.htm
>>
>> >> > B) How to exclude sheet "name" from being summerized?
>>
>> >> > See the sript:http://www.rondebruin.nl/summary.htm
>>
>> >> > Bart
> 
> Ron, I uses the automatic one: Copy_With_AdvancedFilter_To_Worksheets()
>
0
rondebruin (3790)
7/29/2007 10:40:56 AM
On Jul 29, 5:40 pm, "Ron de Bruin" <rondebr...@kabelfoon.nl> wrote:
> Hi Bart
>
> This example have a sheet with the data named "Sheet1" and before it split the data it delete the columns you want in this sheet
> I will add a sum formula in column C of every sheet it create
>
> Maybe you want to change the column in the filter range ?
>  Set rng = ws1.Range("A1:IV" & Rows.Count)
>
> Sub Copy_With_AdvancedFilter_To_Worksheets()
>     Dim CalcMode As Long
>     Dim ws1 As Worksheet
>     Dim ws2 As Worksheet
>     Dim WSNew As Worksheet
>     Dim rng As Range
>     Dim cell As Range
>     Dim Lrow As Long
>
>     Set ws1 = Sheets("Sheet1")  '<<< Change
>
>     With ws1
>         .Range("Z:AH").Delete Shift:=xlShiftToLeft
>         .Range("W:W").Delete Shift:=xlShiftToLeft
>         .Range("L:M").Delete Shift:=xlShiftToLeft
>         .Range("F:J").Delete Shift:=xlShiftToLeft
>     End With
>
>     'Set filter range : A1 is the top left cell of your filter range and
>     'the header of the first column, D is the last column in the filter range
>     Set rng = ws1.Range("A1:IV" & Rows.Count)
>
>     With Application
>         CalcMode = .Calculation
>         .Calculation = xlCalculationManual
>         .ScreenUpdating = False
>     End With
>
>     ' Add a worksheet to copy the a unique list and add the CriteriaRange
>     Set ws2 = Worksheets.Add
>
>     With ws2
>         'This example filters on the first column in the range
>         'first we copy the Unique data from this column to ws2
>         rng.Columns(1).AdvancedFilter _
>                 Action:=xlFilterCopy, _
>                 CopyToRange:=.Range("B1"), Unique:=True
>
>         'Then give A1 the same value as B1 (header of column 1) in ws2
>         .Range("A1").Value = .Range("B1").Value
>
>         'loop through the unique list in ws2 and filter/copy to a new sheet
>         Lrow = .Cells(Rows.Count, "B").End(xlUp).Row
>         For Each cell In .Range("B2:B" & Lrow)
>             .Range("A2").Value = "=" & Chr(34) & "=" & cell.Value & Chr(34)
>             Set WSNew = Sheets.Add
>             On Error Resume Next
>             WSNew.Name = cell.Value
>             If Err.Number > 0 Then
>                 MsgBox "Change the name of : " & WSNew.Name & " manually"
>                 Err.Clear
>             End If
>             On Error GoTo 0
>             rng.AdvancedFilter Action:=xlFilterCopy, _
>                                CriteriaRange:=.Range("A1:A2"), _
>                                CopyToRange:=WSNew.Range("A1"), _
>                                Unique:=False
>
>             WSNew.Range("C" & Rows.Count).End(xlUp).Offset(2, 0).FormulaR1C1 = "=SUM(R1C:R[-2]C)"
>
>             WSNew.Columns.AutoFit
>         Next
>
>         'Delete the ws2 sheet
>         On Error Resume Next
>         Application.DisplayAlerts = False
>         .Delete
>         Application.DisplayAlerts = True
>         On Error GoTo 0
>
>     End With
>
>     With Application
>         .ScreenUpdating = True
>         .Calculation = CalcMode
>     End With
> End Sub
>
> --
>
> Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm
>
> "AA Arens" <bartvandon...@gmail.com> wrote in messagenews:1185678457.098186.295270@x40g2000prg.googlegroups.com...
> > On Jul 29, 12:16 am, "Ron de Bruin" <rondebr...@kabelfoon.nl> wrote:
> >> A:
>
> >> Which macro do you use from that page?
>
> >> B:
>
> >> You see this block in the macro
>
> >>  For Each Sh In Basebook.Worksheets
> >> ....
> >> ...
> >> ..
> >>   Next Sh
>
> >> Replace this with the code I posted
>
> >> I must go now so I will read your reply tomorrow
>
> >> --
>
> >> Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm
>
> >> "AA Arens" <bartvandon...@gmail.com> wrote in messagenews:1185639765.970429.79610@m37g2000prh.googlegroups.com...
> >> > For (A):
>
> >> > I have placed under the DIM \codes:
>
> >> > Worksheets("TelkomSel").Range("Z:AH").Delete Shift:=xlShiftToLeft
> >> > Worksheets("TelkomSel").Range("W:W").Delete Shift:=xlShiftToLeft
> >> > Worksheets("TelkomSel").Range("L:M").Delete Shift:=xlShiftToLeft
> >> > Worksheets("TelkomSel").Range("F:J").Delete Shift:=xlShiftToLeft
>
> >> > because I first want to clean up the stuff.
>
> >> > 2nd: I have also the headers copied, so the summary should start on
> >> > the second row instead of the first. How will be the code you gave me
> >> > and where do I have to place it in the present script I mentioned
> >> > under A.
>
> >> > For (B): Where to add this code in the script I mentioned in B?
>
> >> > Bart
>
> >> > Ron de Bruin wrote:
> >> >> Hi Bart
>
> >> >> A:
>
> >> >> This will add a Sum formula in column C
>
> >> >> Range("C" & Rows.Count).End(xlUp).Offset(2, 0).FormulaR1C1 = "=SUM(R1C:R[-2]C)"
>
> >> >> B:
>
> >> >> One way
>
> >> >> You can fill in the sheet names in the array that you want to exclude
>
> >> >>     For Each Sh In Basebook.Worksheets
> >> >>         If Sh.Name <> Newsh.Name And Sh.Visible Then
>
> >> >>             If IsError(Application.Match(Sh.Name, _
> >> >>                             Array("Sheet1", "Sheet3"), 0)) Then
>
> >> >>                 ColNum = 1
> >> >>                 RwNum = RwNum + 1
> >> >>                 'Copy the sheet name in the A column
> >> >>                 Newsh.Cells(RwNum, 1).Value = Sh.Name
>
> >> >>                 For Each myCell In Sh.Range("A1,D5:E5,Z10")    '<--Change the range
> >> >>                     ColNum = ColNum + 1
> >> >>                     Newsh.Cells(RwNum, ColNum).Formula = _
> >> >>                     "='" & Sh.Name & "'!" & myCell.Address(False, False)
> >> >>                 Next myCell
>
> >> >>             End If
>
> >> >>         End If
> >> >>     Next Sh
>
> >> >> --
>
> >> >> Regards Ron de Bruin
> >> >>http://www.rondebruin.nl/tips.htm
>
> >> >> "AA Arens" <bartvandon...@gmail.com> wrote in messagenews:1185615255.528629.86460@i13g2000prf.googlegroups.com...
> >> >> > A) I use Ron de Bruin's script "Copy records with the same value in a
> >> >> > column to a new sheet or workbook". How to add VB code that totalize
> >> >> > one of the columns and write the value under the last row?
>
> >> >> > See the scripthttp://www.rondebruin.nl/copy5.htm
>
> >> >> > B) How to exclude sheet "name" from being summerized?
>
> >> >> > See the sript:http://www.rondebruin.nl/summary.htm
>
> >> >> > Bart
>
> > Ron, I uses the automatic one: Copy_With_AdvancedFilter_To_Worksheets()

Ron, thanks. I will follow up this weekend.

0
7/30/2007 12:44:26 PM
Reply:

Similar Artilces:

Auto adjusting # rows between 2 worksheets
I have a spreadsheet where I import daily data into worksheet A. Sometimes there will be 5 rows of data and sometimes 1000+. Then inside the spreadsheet I have Worksheet B that runs some formulas based on Worksheet A. My question is how do I get Worksheet B to automatically extend or reduce the # of rows to match Worksheet A? Please help or give me some direction! thanks Kevin, You can have Spreadsheet B adjust the number of formula's programattically by cycling through the maximum used range in Spreadsheet A and inspecting individual cells for formula's. The followin...

System does not auto create Transfer In during Inter-store transfe #2
Yes, I have set the HQ Configuration option to auto generate transfer. The problem is store B can auto generate transfer in doc but store A can not. Any other HQ configuration cause this problem? Any other setting on store table that i overlook? anyone else can help me? regards Dennis "David" wrote: > Did you set the HQ Configuration options to auto generate transfers? > > > > "Dennis" wrote: > > > Hi, > > > > my customer running 2 stores and both store having the same interstore > > transfer option setting. my problem is 1 o...

Formatting based on total in Footer
I am working on a report which summarizes orders from different customers over a certain time frame. I have a text box which totals the orders for each customer in the footer for the customer. I would like to show the customer header and the detailed order information only if the header total exceeds a certain amount. Example customer A 3 orders for a total $5,500 Customer B 2 orders for a total of $3,500, customer C 1 order for $7500. I would like the group header, the detail and the footer to show up for customers A and C, but not B, as the total is below $5,000. I have the follow...

Help with total page number and number of copies
I'm trying to have displayed, somewhere in the footer, the total numbe of pages printed (rather than just the total number of pages); so if have one sheet and print it twelve times it say the page total i twelve on the sheet -- Message posted from http://www.ExcelForum.com Hi which Excel version do you have?. There should be a symbol in the custom header/footer section to insert this information -- Regards Frank Kabel Frankfurt, Germany > I'm trying to have displayed, somewhere in the footer, the total > number of pages printed (rather than just the total number of pages);...

Auto Reply
I do not have an exchange server and, as such, do not have the out of office assistant. I have followed the auto-reply instructions to the letter (several times!) and "Cannot reply to message" keeps coming up even though outlook does not say why. The Rule keeps turning itself off and I do not know why. Help! If you don't have an exchange server then clearly you can't have Auto-Reply in Outlook because there must be a system that can recognize you even if you are not online. There are no Auto-Reply instructions that will work if you don't have MS Exchan...

Cross Site Scripting Access is denied
Hello, I have an external ASP .NET page embebbed in a iframe on CRM 3.0, the page make calculations and set a value in a TextBox inside the CRM form using JavaScript, the url of the iframe is set in the OnLoad event: document.all.IFRAME_TOTALCOTIZACION.src="http://localhost/AdvancedSearch/CotizacionTotal.aspx?oId=" + crmFormSubmit.crmFormSubmitId.value ; All work ok when i use localhost to set the url of the page and i execute the page logging in my computer ( do calculations and set value), but when i execute from another computer of the local network using the browser not f...

Event Scripting Agent
Hi, I have managed to get a simple script to work on our exchange server which changes the message class from one type to another. the new message class is stored in the Organisatinal Forms Library. The problem I have is messages are taking anywhere between 5 and 25 seconds to change message class. I have tried this on a mailbox and public folder and the same delay occours. Is this by design? Is there anyway to speed it up? Cheers Mike. ...

Excel Continuous Running Total
I posted a message earlier and have received a partial solution. I want to keep track of how much stock prices go up or down with a running total of how much they go up or down over several days until the direction changes. For example, if price go up 10 on Mon, 20 on Tues, 30 on Thurs and down 10 on Fri I want my running total column to show a positive number of 60 and then a red number of 10 and continue adding the amount of the total of the down days until the market shows an up day. The formula I am now using total the first and second day but does not do a running total count if t...

Grand Total for Rows not Working in a Pivot Table
Hi, I'm working with a Pivot Table in Excel 2007 and for some reason I can't get the Grand Total for Rows to appear. I tried the usual step of Pivot Table Tools > Design > Grand Totals > On for Rows but nothing happens. The table doesn't appear any different from ones I've created before: Year Product 1 Product 2 2009 200 500 2008 150 400 The Grand Totals do work for the columns but not the rows. Any suggestions? Thanks so much! Tiffany Hi Tiffany Do you have Year as a Row Label, Product as a Column Label and y...

Found out how to disable Auto Preview in IMAP
I noticed that a lot of people have had the problem where Auto Preview keeps coming up with new messages in their IMAP folder. And the MVP's solution wasn't working. On the "Folder List" highlight the "Inbox" for the IMAP folder that is giving you the AutoPreview Problem. Click View >> Current View >> Customize Current View >> Other Settings >> under the 'auto preview' section change the radio button to "no auto preview" This should work! ...

auto send with attachment
I have to send a couple emails every day with an Excel sheet. Annoying. Does Outlook 2007 have the ability to auto-send emails with attachments on a given schedule? If yes, where do I start looking for details? thanks! Check for message automation plug-ins for Outlook at Slipstick Systems (www.slipstick.com). There is a plugin for Outlook that performs message automation. Just remember, much of that stuff is shareware and freeware, so expect bugs, and make sure the product documentation explains how to remove the plugin before you install it. --Dave "shank&q...

Running Totals in Report Writer
Hello: I have calculated fields in the SOP Blank Packing Slip modified in Report Writer in that, among other things, displays the quantity available for each lot number that is assigned to each line item. I need to compare the quantity available for each lot with the total quantity ordered for that line item. So, let's say that there is a quantity of 40 of item 111 at lot A for a quantity of 34 and at lot B for a quantity of 72. In this case, lot A will pull 34 and lot B will pull only 6. This totals 40. In another example, there's a quantity of 75 for item 222 at lot A f...

auto-insert rows?
Is there any way to automatically insert a row? For example, I have a spreadsheet that displays totals in the 40th row for all the data in the columns above it (1-39). Now that I am to my 40th row of data, I either have to skip below my total bar (which screws up my formulae) or insert a row just above it, which is becoming a pain as I add numerous rows a day. Can't I have my totals automatically go downward? Or have them float somewhere visible? If you have access to MacXL or to XL2003, use the List Manager - it's designed to do just that. In article <9W3Gc.8588$yy1.6090@newsr...

number error showing in total column in print preview.
I am trying to print a form, but in print preview the total column is showing a number error. I have expanded the column and it is still showing number error. Please advise on how this error could be solved. ...

Totaling text cells
Is it possible to autosum the total of cells that contain only text, in a worksheet that contains cells with only text and cells with only numbers? Do you mean that you want to *COUNT* cells that contain a specific TEXT entry? Try something like this... =COUNTIF(A2:A100,"Yes") That will count cells in the range A2:A100 that contain Yes. -- Biff Microsoft Excel MVP "Malcolm" <Malcolm@discussions.microsoft.com> wrote in message news:98256116-B5D1-42EA-A087-D1CD9EA4A591@microsoft.com... > Is it possible to autosum the total of cells that c...

Auto Post GL and AR batch
Hi, Can we set the auto post GL and AR batch ? Through macro I can set the auto post batch but for some reason if that batch is not available than Great Plains post the next available batch. Thanks, You are experiencing this problem, because you are using your mouse to select the batch. When doing that, GP records the batch list position. If the batch you wanted was in position 3, but no longer exists, whatever batch is in position 3 will be used. In order to avoid this, don't lookup the batch name. Instead, type it in. One other thing, though, is that if the batch that is ...

Auto Accept Agent #4
I've installed the agent and it is running as a service, however I receive an error when I try to register a mailbox with the service... Any mailbox, not just the resource mailbox. The message is: Registering emeeting@domain.net with Auto Accept Agent. Error # 8002802B: Error: Unable to get event binding path for emeeting@domain.net ...

sql server 2000: most frequented tables -> script wanted
hallo, could someone please provide me a script, which throws the most frequented tables used within a sql server 2000 database? i am aware of one (see below) for sql server 2005, but due to new functionalities within sql server 2005 it can not be backported. SELECT TOP 20 db_name(database_ID), object_name(object_id), leaf_insert_count, leaf_delete_Count, leaf_update_count, range_scan_count, row_lock_count FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL) WHERE db_name(database_ID)='<Datenbank-Name>' ORDER BY leaf_update_count desc --oder ...

Inventory items should auto fill description in Req mgmt
Inventory items should auto fill the description field in requisition management but only if the description field is blank. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid...

Report totals and grouping
I have an access 2003 file that looks like this: Land Vehicle Water vehicle Air vehicle Car Boat Plane Bike Yacht Jet Truck Raft Blimp Car Yacht Plane Bike Raft Jet Bike Boat Plane etc. I'd like the report to look like: Land Vehicle Bike 3 Car 2 Truck 1 Water Vehicle Boat 2 Yacht 2 Raft 3 Air Vehicle Plane 3 Jet 2 Blimp 1 I cannot figure out how to do this. Any suggestions are appreciated. Therm...

Average download size of auto updates
Can anyone give me an estimate of how many MB in downloads per day on average auto updates will produce? Or alternatively how to track the size of downloaded updates on a daily basis? I'm trying to monitor closely the downloads on our system and therefore want to have these numbers. I know manual control of updates is an option but since I usually have very little clue what the updates are about I'm an auto update person. Apart from Windows I also get occasional Java and Adobe updates: any advice on the likely size of these would be welcome too. joeth wrote: > Can a...

tracking total portfolio value
How do you track the total value of a portfolio or investment account over time, on a daily basis? I want to be able to get a line chart of my portfolio value. I have dug through all of the reports and tried to create custom ones, but all I have been able to get is a line chart of monthly values, not daily. I know that the daily total is stored, because on the investing home page it shows the total value for 30 days ago. For instance, today it is showing the value for 7/28/07. ...

Sort creating total errors
I have a spreadsheet with about 20 columns and 8,000 rows. I have numerical data in most of the columns corresponding to people. Like this: John 9 6 Debbie 9 5 James 8 5 Total 26 16 If I highlight all columns and do a sort by the person's name, the total number at the bottom changes even if the data has not changed. Do you know why this is happening? Thanks in advance for any help! What are the formulas you used in those total cells? excel idiot wrote: > > I have a spreadsheet with about 20 columns and 8,000 rows. I have numerica...

Total PC Defender
Would anyone know of a surefire method of removing Total PC Defender & Trojan.Injector.GT ? I think I've been able to remove Total PC Defender (partially), but not the Trojan. AVG free still isn't allowed to update, Malewarebytes wont run, and my administrative rights have changed... wont allow me to install Windows Defender. I'm hoping someone has written a stand alone DOS utility or removal tool... would be nice. Kindof pulling my hair out over here! Thanks for any help. Richard in VA +++++++++++ Richard In Va. wrote: > Would anyone know o...

Customizing Auto Warning Messages
We have mailbox limit's in place and have opted for the Warning messages to be sent. Is there a way to customize what the message says? We would like the message to be specific per our corporate standards. Any help would be greatly appreciated. PLEASE DISREGARD THIS...I FOUND AN ANSWER RIGHT AFTER I POSTED THIS. ""Nia"" wrote: > We have mailbox limit's in place and have opted for the Warning messages to > be sent. Is there a way to customize what the message says? We would like > the message to be specific per our corporate standards. Any hel...