AutoFilter and Sub-total

M

manfareed

Hi ,

I have a spreadsheet with a summary sheet with 3 branch tabs.
I have the following code which filters for the word "visits" in the 3
branch tabs and subtotals the values next to the word and places the total in
the summary sheet.

Sub Filter_All_Visits()
Dim ws As Object

For Each ws In Worksheets
If ws.name <> "Summary" And ws.name <> "Totals by Branch" Then
ws.Range("B2").AutoFilter Field:=2, Criteria1:="Visits"
End If
Next ws

Sheets("Summary").Select
Range("C5").Select
ActiveCell.FormulaR1C1 =
"=SUBTOTAL(9,Birmingham!R[-2]C:R[5000]C,Reading!R[-2]C:R[5000]C,Nottingham!R[-2]C:R[5000]C)"
Range("C5").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("D5").Select
ActiveCell.FormulaR1C1 = _

"=SUBTOTAL(9,Birmingham!R[-2]C:R[5000]C,Reading!R[-2]C:R[5000]C,Nottingham!R[-2]C:R[5000]C)"
Range("D5").Select

Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

For Each ws In Worksheets
If ws.name <> "Summary" And ws.name <> "Totals by Branch" Then

ws.Range("B2").AutoFilter

End If


End Sub

My question is how do I make the code more dynamic i.e. if more branch tabs
are added. Also in the subtotal I have the row value as "5000". I want to do
away with putting this in.

Thanks
 
M

Michael Arch

I believe if you add new tabs it will be ok, because you have the statement
For Each ws In Worksheets

This ensures that it will loop through the whole book, and do whatever
unless the sheet name is Totals by branch or Summary.
The 5000 indicates where it will stop placing the subtotal, so you can
change that to whatever number you want. I believe your code is pretty
dynamic. If you have something else in mind...

You can get rid of the select statement and do this:
Range("C5").FormulaR1C1 =
"=SUBTOTAL(9,Birmingham!R[-2]C:R[5000]C,Reading!R[-2]C:R[5000]C,Nottingham!R[-2]C:R[5000]C)"
 
M

manfareed

Hi Michael,

Thanks for your prompt reply.

I think you have picked on the part which will give me problems

Range("C5").FormulaR1C1 =
"=SUBTOTAL(9,Birmingham!R[-2]C:R[5000]C,Reading!R[-2]C:R[5000]C,Nottingham!R[-2]C:R[5000]C)"
Click to expand...

Birmingham , Reading and Nottingham are the 3 current branches.
What if a new branch is added? I do not want to write the branch name in the
code. Someone else will be using the spreadsheet and they would expect the
totals to be correct.

Also the subtotals will never need to go to row 5000 but will vary dependent
on what has been input..

Is there a way to go to the last row and subtotal.

Thanks,

Manir

Michael Arch said:
I believe if you add new tabs it will be ok, because you have the statement
For Each ws In Worksheets

This ensures that it will loop through the whole book, and do whatever
unless the sheet name is Totals by branch or Summary.
The 5000 indicates where it will stop placing the subtotal, so you can
change that to whatever number you want. I believe your code is pretty
dynamic. If you have something else in mind...

You can get rid of the select statement and do this:
Range("C5").FormulaR1C1 =
"=SUBTOTAL(9,Birmingham!R[-2]C:R[5000]C,Reading!R[-2]C:R[5000]C,Nottingham!R[-2]C:R[5000]C)"




--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




manfareed said:
Hi ,

I have a spreadsheet with a summary sheet with 3 branch tabs.
I have the following code which filters for the word "visits" in the 3
branch tabs and subtotals the values next to the word and places the total in
the summary sheet.

Sub Filter_All_Visits()
Dim ws As Object

For Each ws In Worksheets
If ws.name <> "Summary" And ws.name <> "Totals by Branch" Then
ws.Range("B2").AutoFilter Field:=2, Criteria1:="Visits"
End If
Next ws

Sheets("Summary").Select
Range("C5").Select
ActiveCell.FormulaR1C1 = _
"=SUBTOTAL(9,Birmingham!R[-2]C:R[5000]C,Reading!R[-2]C:R[5000]C,Nottingham!R[-2]C:R[5000]C)"
Range("C5").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("D5").Select
ActiveCell.FormulaR1C1 = _

"=SUBTOTAL(9,Birmingham!R[-2]C:R[5000]C,Reading!R[-2]C:R[5000]C,Nottingham!R[-2]C:R[5000]C)"
Range("D5").Select

Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

For Each ws In Worksheets
If ws.name <> "Summary" And ws.name <> "Totals by Branch" Then

ws.Range("B2").AutoFilter

End If


End Sub

My question is how do I make the code more dynamic i.e. if more branch tabs
are added. Also in the subtotal I have the row value as "5000". I want to do
away with putting this in.

Thanks
Click to expand...
Click to expand...
 
M

Michael Arch

Try this:

For Each ws In Worksheets
If ws.name <> "Summary" And ws.name <> "Totals by Branch" Then
ws.Range("B2").AutoFilter Field:=2, Criteria1:="Visits"
End If
iLastRow= Range("A65536").end(xlup).Row
mystring = "=SUBTOTAL(9," & ws.Name & "!R[-2]C:R[" & iLastRow & "]C," &
mystring
Next ws
Sheets("Summary").Select
Range("C5").FormulaR1C1 = mystring
The rest of your code goes here.

--If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




manfareed said:
Hi Michael,

Thanks for your prompt reply.

I think you have picked on the part which will give me problems

Range("C5").FormulaR1C1 =
"=SUBTOTAL(9,Birmingham!R[-2]C:R[5000]C,Reading!R[-2]C:R[5000]C,Nottingham!R[-2]C:R[5000]C)"
Click to expand...

Birmingham , Reading and Nottingham are the 3 current branches.
What if a new branch is added? I do not want to write the branch name in the
code. Someone else will be using the spreadsheet and they would expect the
totals to be correct.

Also the subtotals will never need to go to row 5000 but will vary dependent
on what has been input..

Is there a way to go to the last row and subtotal.

Thanks,

Manir

Michael Arch said:
I believe if you add new tabs it will be ok, because you have the statement
For Each ws In Worksheets

This ensures that it will loop through the whole book, and do whatever
unless the sheet name is Totals by branch or Summary.
The 5000 indicates where it will stop placing the subtotal, so you can
change that to whatever number you want. I believe your code is pretty
dynamic. If you have something else in mind...

You can get rid of the select statement and do this:
Range("C5").FormulaR1C1 =
"=SUBTOTAL(9,Birmingham!R[-2]C:R[5000]C,Reading!R[-2]C:R[5000]C,Nottingham!R[-2]C:R[5000]C)"




--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




manfareed said:
Hi ,

I have a spreadsheet with a summary sheet with 3 branch tabs.
I have the following code which filters for the word "visits" in the 3
branch tabs and subtotals the values next to the word and places the total in
the summary sheet.

Sub Filter_All_Visits()
Dim ws As Object

For Each ws In Worksheets
If ws.name <> "Summary" And ws.name <> "Totals by Branch" Then
ws.Range("B2").AutoFilter Field:=2, Criteria1:="Visits"
End If
Next ws

Sheets("Summary").Select
Range("C5").Select
ActiveCell.FormulaR1C1 = _
"=SUBTOTAL(9,Birmingham!R[-2]C:R[5000]C,Reading!R[-2]C:R[5000]C,Nottingham!R[-2]C:R[5000]C)"
Range("C5").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("D5").Select
ActiveCell.FormulaR1C1 = _

"=SUBTOTAL(9,Birmingham!R[-2]C:R[5000]C,Reading!R[-2]C:R[5000]C,Nottingham!R[-2]C:R[5000]C)"
Range("D5").Select

Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

For Each ws In Worksheets
If ws.name <> "Summary" And ws.name <> "Totals by Branch" Then

ws.Range("B2").AutoFilter

End If


End Sub

My question is how do I make the code more dynamic i.e. if more branch tabs
are added. Also in the subtotal I have the row value as "5000". I want to do
away with putting this in.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
M

manfareed

Many Thanks

I will try it and let you know by Friday

Michael Arch said:
Try this:

For Each ws In Worksheets
If ws.name <> "Summary" And ws.name <> "Totals by Branch" Then
ws.Range("B2").AutoFilter Field:=2, Criteria1:="Visits"
End If
iLastRow= Range("A65536").end(xlup).Row
mystring = "=SUBTOTAL(9," & ws.Name & "!R[-2]C:R[" & iLastRow & "]C," &
mystring
Next ws
Sheets("Summary").Select
Range("C5").FormulaR1C1 = mystring
The rest of your code goes here.

--If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




manfareed said:
Hi Michael,

Thanks for your prompt reply.

I think you have picked on the part which will give me problems

Range("C5").FormulaR1C1 =
"=SUBTOTAL(9,Birmingham!R[-2]C:R[5000]C,Reading!R[-2]C:R[5000]C,Nottingham!R[-2]C:R[5000]C)"
Click to expand...

Birmingham , Reading and Nottingham are the 3 current branches.
What if a new branch is added? I do not want to write the branch name in the
code. Someone else will be using the spreadsheet and they would expect the
totals to be correct.

Also the subtotals will never need to go to row 5000 but will vary dependent
on what has been input..

Is there a way to go to the last row and subtotal.

Thanks,

Manir

Michael Arch said:
I believe if you add new tabs it will be ok, because you have the statement
For Each ws In Worksheets

This ensures that it will loop through the whole book, and do whatever
unless the sheet name is Totals by branch or Summary.
The 5000 indicates where it will stop placing the subtotal, so you can
change that to whatever number you want. I believe your code is pretty
dynamic. If you have something else in mind...

You can get rid of the select statement and do this:
Range("C5").FormulaR1C1 =
"=SUBTOTAL(9,Birmingham!R[-2]C:R[5000]C,Reading!R[-2]C:R[5000]C,Nottingham!R[-2]C:R[5000]C)"




--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




:

Hi ,

I have a spreadsheet with a summary sheet with 3 branch tabs.
I have the following code which filters for the word "visits" in the 3
branch tabs and subtotals the values next to the word and places the total in
the summary sheet.

Sub Filter_All_Visits()
Dim ws As Object

For Each ws In Worksheets
If ws.name <> "Summary" And ws.name <> "Totals by Branch" Then
ws.Range("B2").AutoFilter Field:=2, Criteria1:="Visits"
End If
Next ws

Sheets("Summary").Select
Range("C5").Select
ActiveCell.FormulaR1C1 = _
"=SUBTOTAL(9,Birmingham!R[-2]C:R[5000]C,Reading!R[-2]C:R[5000]C,Nottingham!R[-2]C:R[5000]C)"
Range("C5").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("D5").Select
ActiveCell.FormulaR1C1 = _

"=SUBTOTAL(9,Birmingham!R[-2]C:R[5000]C,Reading!R[-2]C:R[5000]C,Nottingham!R[-2]C:R[5000]C)"
Range("D5").Select

Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

For Each ws In Worksheets
If ws.name <> "Summary" And ws.name <> "Totals by Branch" Then

ws.Range("B2").AutoFilter

End If


End Sub

My question is how do I make the code more dynamic i.e. if more branch tabs
are added. Also in the subtotal I have the row value as "5000". I want to do
away with putting this in.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
M

Michael Arch

Im sorry I forgot the comma effect, try this ammended string:
For Each ws In Worksheets
ilastrow = Range("A65536").End(xlUp).Row
mystring = ws.Name & "!R[-2]C:R[" & ilastrow & "]C," & mystring
Next
mystring = "=Subtotal(9," & Mid(mystring, 1, Len(mystring) - 1) & ")"
Sheets("Summary").Select
Range("C5").FormulaR1C1 = mystring
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




Michael Arch said:
Try this:

For Each ws In Worksheets
If ws.name <> "Summary" And ws.name <> "Totals by Branch" Then
ws.Range("B2").AutoFilter Field:=2, Criteria1:="Visits"
End If
iLastRow= Range("A65536").end(xlup).Row
mystring = "=SUBTOTAL(9," & ws.Name & "!R[-2]C:R[" & iLastRow & "]C," &
mystring
Next ws
Sheets("Summary").Select
Range("C5").FormulaR1C1 = mystring
The rest of your code goes here.

--If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




manfareed said:
Hi Michael,

Thanks for your prompt reply.

I think you have picked on the part which will give me problems

Range("C5").FormulaR1C1 =
"=SUBTOTAL(9,Birmingham!R[-2]C:R[5000]C,Reading!R[-2]C:R[5000]C,Nottingham!R[-2]C:R[5000]C)"
Click to expand...

Birmingham , Reading and Nottingham are the 3 current branches.
What if a new branch is added? I do not want to write the branch name in the
code. Someone else will be using the spreadsheet and they would expect the
totals to be correct.

Also the subtotals will never need to go to row 5000 but will vary dependent
on what has been input..

Is there a way to go to the last row and subtotal.

Thanks,

Manir

Michael Arch said:
I believe if you add new tabs it will be ok, because you have the statement
For Each ws In Worksheets

This ensures that it will loop through the whole book, and do whatever
unless the sheet name is Totals by branch or Summary.
The 5000 indicates where it will stop placing the subtotal, so you can
change that to whatever number you want. I believe your code is pretty
dynamic. If you have something else in mind...

You can get rid of the select statement and do this:
Range("C5").FormulaR1C1 =
"=SUBTOTAL(9,Birmingham!R[-2]C:R[5000]C,Reading!R[-2]C:R[5000]C,Nottingham!R[-2]C:R[5000]C)"




--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




:

Hi ,

I have a spreadsheet with a summary sheet with 3 branch tabs.
I have the following code which filters for the word "visits" in the 3
branch tabs and subtotals the values next to the word and places the total in
the summary sheet.

Sub Filter_All_Visits()
Dim ws As Object

For Each ws In Worksheets
If ws.name <> "Summary" And ws.name <> "Totals by Branch" Then
ws.Range("B2").AutoFilter Field:=2, Criteria1:="Visits"
End If
Next ws

Sheets("Summary").Select
Range("C5").Select
ActiveCell.FormulaR1C1 = _
"=SUBTOTAL(9,Birmingham!R[-2]C:R[5000]C,Reading!R[-2]C:R[5000]C,Nottingham!R[-2]C:R[5000]C)"
Range("C5").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("D5").Select
ActiveCell.FormulaR1C1 = _

"=SUBTOTAL(9,Birmingham!R[-2]C:R[5000]C,Reading!R[-2]C:R[5000]C,Nottingham!R[-2]C:R[5000]C)"
Range("D5").Select

Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

For Each ws In Worksheets
If ws.name <> "Summary" And ws.name <> "Totals by Branch" Then

ws.Range("B2").AutoFilter

End If


End Sub

My question is how do I make the code more dynamic i.e. if more branch tabs
are added. Also in the subtotal I have the row value as "5000". I want to do
away with putting this in.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
M

manfareed

Hi Michael,

The sub-total does not quite work.

Only the top filtered value on each sheet is subtotalled i.e. in C3. I need
it to total together all filtered values.

Code works if a new sheet is added.

Thanks

Michael Arch said:
Im sorry I forgot the comma effect, try this ammended string:
For Each ws In Worksheets
ilastrow = Range("A65536").End(xlUp).Row
mystring = ws.Name & "!R[-2]C:R[" & ilastrow & "]C," & mystring
Next
mystring = "=Subtotal(9," & Mid(mystring, 1, Len(mystring) - 1) & ")"
Sheets("Summary").Select
Range("C5").FormulaR1C1 = mystring
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




Michael Arch said:
Try this:

For Each ws In Worksheets
If ws.name <> "Summary" And ws.name <> "Totals by Branch" Then
ws.Range("B2").AutoFilter Field:=2, Criteria1:="Visits"
End If
iLastRow= Range("A65536").end(xlup).Row
mystring = "=SUBTOTAL(9," & ws.Name & "!R[-2]C:R[" & iLastRow & "]C," &
mystring
Next ws
Sheets("Summary").Select
Range("C5").FormulaR1C1 = mystring
The rest of your code goes here.

--If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




manfareed said:
Hi Michael,

Thanks for your prompt reply.

I think you have picked on the part which will give me problems

Range("C5").FormulaR1C1 =
"=SUBTOTAL(9,Birmingham!R[-2]C:R[5000]C,Reading!R[-2]C:R[5000]C,Nottingham!R[-2]C:R[5000]C)"

Birmingham , Reading and Nottingham are the 3 current branches.
What if a new branch is added? I do not want to write the branch name in the
code. Someone else will be using the spreadsheet and they would expect the
totals to be correct.

Also the subtotals will never need to go to row 5000 but will vary dependent
on what has been input..

Is there a way to go to the last row and subtotal.

Thanks,

Manir

:

I believe if you add new tabs it will be ok, because you have the statement
For Each ws In Worksheets

This ensures that it will loop through the whole book, and do whatever
unless the sheet name is Totals by branch or Summary.
The 5000 indicates where it will stop placing the subtotal, so you can
change that to whatever number you want. I believe your code is pretty
dynamic. If you have something else in mind...

You can get rid of the select statement and do this:
Range("C5").FormulaR1C1 =
"=SUBTOTAL(9,Birmingham!R[-2]C:R[5000]C,Reading!R[-2]C:R[5000]C,Nottingham!R[-2]C:R[5000]C)"




--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




:

Hi ,

I have a spreadsheet with a summary sheet with 3 branch tabs.
I have the following code which filters for the word "visits" in the 3
branch tabs and subtotals the values next to the word and places the total in
the summary sheet.

Sub Filter_All_Visits()
Dim ws As Object

For Each ws In Worksheets
If ws.name <> "Summary" And ws.name <> "Totals by Branch" Then
ws.Range("B2").AutoFilter Field:=2, Criteria1:="Visits"
End If
Next ws

Sheets("Summary").Select
Range("C5").Select
ActiveCell.FormulaR1C1 = _
"=SUBTOTAL(9,Birmingham!R[-2]C:R[5000]C,Reading!R[-2]C:R[5000]C,Nottingham!R[-2]C:R[5000]C)"
Range("C5").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("D5").Select
ActiveCell.FormulaR1C1 = _

"=SUBTOTAL(9,Birmingham!R[-2]C:R[5000]C,Reading!R[-2]C:R[5000]C,Nottingham!R[-2]C:R[5000]C)"
Range("D5").Select

Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

For Each ws In Worksheets
If ws.name <> "Summary" And ws.name <> "Totals by Branch" Then

ws.Range("B2").AutoFilter

End If


End Sub

My question is how do I make the code more dynamic i.e. if more branch tabs
are added. Also in the subtotal I have the row value as "5000". I want to do
away with putting this in.

Thanks
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cleaner coding to repeat process 7
Totals An Easier Way?? 5
Code execution is correct but generates Error :The file you aretrying to open is in a different form 3
Create Detail Sheets from Pivot Table 1
Error 2007 0
R[1]C[1] with a variable 2
cannot insert row in vba 1
how do I write a loop macro? 9

Top