Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Creating a total line
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 88
Default Creating a total line
Im trying to Have a total line that is created below the last line of data.
I never know how many records there are going to be each month here is the
code that i would like to intergrate it with.

Sub NetPIlIQ()

Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
With Worksheets("TOEPIEXP")
Set rng = Range("A1").CurrentRegion
rng.AutoFilter Field:=24, Criteria1:="0"
Set rng2 = .AutoFilter.Range
Set rng2 = rng2.Offset(1, 0).Resize( _
rng2.Rows.Count - 1)
Set rng3 = .Range("B:B,E:E,V:V,X:X,AD:AD").EntireColumn
Set rng1 = Intersect(rng2.EntireRow, rng3)
End With
Set rng4 = Worksheets("NetPILIQ") _
..Cells(Rows.Count, 1).End(xlUp)(2)
If rng4.Row < 6 Then
Set rng4 = Worksheets("NetPILIQ").Range("A6")
rng1.Copy rng4
End If
Selection.AutoFilter 'removes autofilter
End Sub

Any help would be great.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200609/1

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default Creating a total line
Sub Copydata()
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim rng5 as Range
With Worksheets("NetPILIQ")
.Range("A7:IV65536").Clear
End With
With Worksheets("TOEPIEXP")
..AutoFilterMode = False
Set rng = .Range("A1").CurrentRegion
rng.AutoFilter Field:=24, Criteria1:="0"
Set rng2 = .AutoFilter.Range
Set rng2 = rng2.Offset(1, 0).Resize( _
rng2.Rows.Count - 1)
Set rng3 = .Range("B:B,E:E,V:V,X:X,AD:AD").EntireColumn
Set rng1 = Intersect(rng2.EntireRow, rng3)
End With
Set rng4 = Worksheets("NetPILIQ") _
..Cells(Rows.Count, 1).End(xlUp)(2)
If rng4.Row < 6 Then
Set rng4 = Worksheets("NetPILIQ").Range("A6")
rng1.Copy rng4
End If
Worksheets("TOEPIEXP").AutoFilterMode = False
set rng5 =Worksheets("NetPILIQ").cells(rows.count,1).End(xl up)(2)
rng5.Resize(1,5).FormulaR1C1 = "=R6C:R[-1]C"
End Sub

--
Regards,
Tom Ogilvy


"jln via OfficeKB.com" wrote:

Im trying to Have a total line that is created below the last line of data.
I never know how many records there are going to be each month here is the
code that i would like to intergrate it with.

Sub NetPIlIQ()

Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
With Worksheets("TOEPIEXP")
Set rng = Range("A1").CurrentRegion
rng.AutoFilter Field:=24, Criteria1:="0"
Set rng2 = .AutoFilter.Range
Set rng2 = rng2.Offset(1, 0).Resize( _
rng2.Rows.Count - 1)
Set rng3 = .Range("B:B,E:E,V:V,X:X,AD:AD").EntireColumn
Set rng1 = Intersect(rng2.EntireRow, rng3)
End With
Set rng4 = Worksheets("NetPILIQ") _
.Cells(Rows.Count, 1).End(xlUp)(2)
If rng4.Row < 6 Then
Set rng4 = Worksheets("NetPILIQ").Range("A6")
rng1.Copy rng4
End If
Selection.AutoFilter 'removes autofilter
End Sub

Any help would be great.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200609/1


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,092
Default Creating a total line
Tom forgot the SUM function in that last line:

rng5.Resize(1, 5).FormulaR1C1 = "=Sum(R6C:R[-1]C)"

Mike F
"Tom Ogilvy" wrote in message
...
Sub Copydata()
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim rng5 as Range
With Worksheets("NetPILIQ")
.Range("A7:IV65536").Clear
End With
With Worksheets("TOEPIEXP")
.AutoFilterMode = False
Set rng = .Range("A1").CurrentRegion
rng.AutoFilter Field:=24, Criteria1:="0"
Set rng2 = .AutoFilter.Range
Set rng2 = rng2.Offset(1, 0).Resize( _
rng2.Rows.Count - 1)
Set rng3 = .Range("B:B,E:E,V:V,X:X,AD:AD").EntireColumn
Set rng1 = Intersect(rng2.EntireRow, rng3)
End With
Set rng4 = Worksheets("NetPILIQ") _
.Cells(Rows.Count, 1).End(xlUp)(2)
If rng4.Row < 6 Then
Set rng4 = Worksheets("NetPILIQ").Range("A6")
rng1.Copy rng4
End If
Worksheets("TOEPIEXP").AutoFilterMode = False
set rng5 =Worksheets("NetPILIQ").cells(rows.count,1).End(xl up)(2)
rng5.Resize(1,5).FormulaR1C1 = "=R6C:R[-1]C"
End Sub

--
Regards,
Tom Ogilvy


"jln via OfficeKB.com" wrote:

Im trying to Have a total line that is created below the last line of
data.
I never know how many records there are going to be each month here is
the
code that i would like to intergrate it with.

Sub NetPIlIQ()

Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
With Worksheets("TOEPIEXP")
Set rng = Range("A1").CurrentRegion
rng.AutoFilter Field:=24, Criteria1:="0"
Set rng2 = .AutoFilter.Range
Set rng2 = rng2.Offset(1, 0).Resize( _
rng2.Rows.Count - 1)
Set rng3 = .Range("B:B,E:E,V:V,X:X,AD:AD").EntireColumn
Set rng1 = Intersect(rng2.EntireRow, rng3)
End With
Set rng4 = Worksheets("NetPILIQ") _
.Cells(Rows.Count, 1).End(xlUp)(2)
If rng4.Row < 6 Then
Set rng4 = Worksheets("NetPILIQ").Range("A6")
rng1.Copy rng4
End If
Selection.AutoFilter 'removes autofilter
End Sub

Any help would be great.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200609/1




Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Creating a total line
Thanks for the correction.

--
Regards,
Tom Ogilvy

"Mike Fogleman" wrote in message
m...
Tom forgot the SUM function in that last line:

rng5.Resize(1, 5).FormulaR1C1 = "=Sum(R6C:R[-1]C)"

Mike F
"Tom Ogilvy" wrote in message
...
Sub Copydata()
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim rng5 as Range
With Worksheets("NetPILIQ")
.Range("A7:IV65536").Clear
End With
With Worksheets("TOEPIEXP")
.AutoFilterMode = False
Set rng = .Range("A1").CurrentRegion
rng.AutoFilter Field:=24, Criteria1:="0"
Set rng2 = .AutoFilter.Range
Set rng2 = rng2.Offset(1, 0).Resize( _
rng2.Rows.Count - 1)
Set rng3 = .Range("B:B,E:E,V:V,X:X,AD:AD").EntireColumn
Set rng1 = Intersect(rng2.EntireRow, rng3)
End With
Set rng4 = Worksheets("NetPILIQ") _
.Cells(Rows.Count, 1).End(xlUp)(2)
If rng4.Row < 6 Then
Set rng4 = Worksheets("NetPILIQ").Range("A6")
rng1.Copy rng4
End If
Worksheets("TOEPIEXP").AutoFilterMode = False
set rng5 =Worksheets("NetPILIQ").cells(rows.count,1).End(xl up)(2)
rng5.Resize(1,5).FormulaR1C1 = "=R6C:R[-1]C"
End Sub

--
Regards,
Tom Ogilvy


"jln via OfficeKB.com" wrote:

Im trying to Have a total line that is created below the last line of
data.
I never know how many records there are going to be each month here is
the
code that i would like to intergrate it with.

Sub NetPIlIQ()

Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
With Worksheets("TOEPIEXP")
Set rng = Range("A1").CurrentRegion
rng.AutoFilter Field:=24, Criteria1:="0"
Set rng2 = .AutoFilter.Range
Set rng2 = rng2.Offset(1, 0).Resize( _
rng2.Rows.Count - 1)
Set rng3 = .Range("B:B,E:E,V:V,X:X,AD:AD").EntireColumn
Set rng1 = Intersect(rng2.EntireRow, rng3)
End With
Set rng4 = Worksheets("NetPILIQ") _
.Cells(Rows.Count, 1).End(xlUp)(2)
If rng4.Row < 6 Then
Set rng4 = Worksheets("NetPILIQ").Range("A6")
rng1.Copy rng4
End If
Selection.AutoFilter 'removes autofilter
End Sub

Any help would be great.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200609/1






Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default Creating a total line
This is how you can calculate the next free row

Set NextFree = Cells(Rows.Count, "B").End(xlUp).Offset(1,0)

couldn't work out where you would add it, or what to sum.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jln via OfficeKB.com" <u25956@uwe wrote in message
news:662ddeef07949@uwe...
Im trying to Have a total line that is created below the last line of
data.
I never know how many records there are going to be each month here is the
code that i would like to intergrate it with.

Sub NetPIlIQ()

Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
With Worksheets("TOEPIEXP")
Set rng = Range("A1").CurrentRegion
rng.AutoFilter Field:=24, Criteria1:="0"
Set rng2 = .AutoFilter.Range
Set rng2 = rng2.Offset(1, 0).Resize( _
rng2.Rows.Count - 1)
Set rng3 = .Range("B:B,E:E,V:V,X:X,AD:AD").EntireColumn
Set rng1 = Intersect(rng2.EntireRow, rng3)
End With
Set rng4 = Worksheets("NetPILIQ") _
Cells(Rows.Count, 1).End(xlUp)(2)
If rng4.Row < 6 Then
Set rng4 = Worksheets("NetPILIQ").Range("A6")
rng1.Copy rng4
End If
Selection.AutoFilter 'removes autofilter
End Sub

Any help would be great.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200609/1



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating a total percentage from coloured cells 1cainr Excel Worksheet Functions 1 January 24th 11 10:56 PM
Equals Sub Total Line Jennifer Excel Worksheet Functions 1 June 19th 09 05:27 AM
Creating a formula that calculates a total cell Growers Excel Discussion (Misc queries) 4 August 1st 06 09:47 PM
Sort creating total errors excel idiot Excel Discussion (Misc queries) 7 January 16th 05 03:21 PM
Total Line in Report Soniya[_2_] Excel Programming 0 January 6th 04 06:29 AM


All times are GMT +1. The time now is 07:11 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement