Sum the remaining rows and place the result in the row below the last

T

tahrah

With the help of Jim, I've got three new macros that work great. The
one below finds only the orders received from January 2007 and hides
the remaining rows and columns that I don't need. Now I need to sum
the orders for January in column AM and I'd like the sum to go in the
row just below the last order. Please help how would I do this? I
tried several of the other macros listed in the group and they didn't
work.

Sub Orders_MTD()
'
' Orders_MTD Macro
' Macro recorded 1/8/2007 by Tahrah Hunt
'
' Keyboard Shortcut: Ctrl+Shift+O
'
Dim lngRow As Long
Rows("3:5").EntireRow.Hidden = True
Columns("A:E").EntireColumn.Hidden = True
Columns("G:I").EntireColumn.Hidden = True
Columns("K:K").EntireColumn.Hidden = True
Columns("M:AG").EntireColumn.Hidden = True
lngRow = Range("AK2001").End(xlUp).Row + 1
Rows(lngRow & ":2001").EntireRow.Hidden = True
Selection.AutoFilter Field:=37, Criteria1:=">=1/1/2007",
Operator:=xlAnd

With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = Rows("6:" & lngRow - 1).Address
'?
With ActiveSheet.PageSetup
.LeftHeader = "PAGE NO. &P"
.CenterHeader = "ORDERS MONTH-TO-DATE"
.RightHeader = "&D, &T"
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 4
.PrintErrors = xlPrintErrorsDisplayed
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter Field:=37
Cells.Select
Selection.EntireRow.Hidden = False
Selection.EntireColumn.Hidden = False
Rows("7:2002").Select
Selection.Sort Key1:=Range("A7"), Order1:=xlAscending,
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
Range("B6").Select
End Sub
 
T

Tom Ogilvy

Sub Orders_MTD()
'
' Orders_MTD Macro
' Macro recorded 1/8/2007 by Tahrah Hunt
'
' Keyboard Shortcut: Ctrl+Shift+O
'
Dim lngRow As Long
Rows("3:5").EntireRow.Hidden = True
Columns("A:E").EntireColumn.Hidden = True
Columns("G:I").EntireColumn.Hidden = True
Columns("K:K").EntireColumn.Hidden = True
Columns("M:AG").EntireColumn.Hidden = True
lngRow = Range("AK2001").End(xlUp).Row + 1
Rows(lngRow & ":2001").EntireRow.Hidden = True
Selection.AutoFilter Field:=37, Criteria1:=">=1/1/2007",
Operator:=xlAnd

cells(2003,"AM").Formula = "=Subtotal(9,$AM$1:$AM$2001)"

With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = Rows("6:" & lngRow - 1).Address
'?
With ActiveSheet.PageSetup
.LeftHeader = "PAGE NO. &P"
.CenterHeader = "ORDERS MONTH-TO-DATE"
.RightHeader = "&D, &T"
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 4
.PrintErrors = xlPrintErrorsDisplayed
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter Field:=37
Cells.Select
Selection.EntireRow.Hidden = False
Selection.EntireColumn.Hidden = False
Rows("7:2002").Select
Selection.Sort Key1:=Range("A7"), Order1:=xlAscending,
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
Range("B6").Select
End Sub
 
T

Tom Ogilvy

IF
cells(2003,"AM").Formula = "=Subtotal(9,$AM$1:$AM$2001)"

doesn't work properly then try

Cells(2003,"AM").Formula = "=Subtotal(9,$AM$1:$AM" & lastrow -1 & ")"
 
T

tahrah

Tom,

Thx for the help. This one worked and I can see the subtotal when the
macro is running, but it's not printing:

cells(2003,"AM").Formula = "=Subtotal(9,$AM$1:$AM$2001)"

Any ideas why?

Regards,
Tahrah
 
T

tahrah

Tom,

Here's the whole macro. Like I said, I can see the subtotal when the
macro is running. It's on the screen. But the subtotal is not
printing.

Sub Orders_MTD()
'
' Orders_MTD Macro
' Macro recorded 1/8/2007 by Tahrah Hunt
'
' Keyboard Shortcut: Ctrl+Shift+O
'
Dim lngRow As Long
Rows("1:3").EntireRow.Hidden = True
Columns("A:E").EntireColumn.Hidden = True
Columns("G:I").EntireColumn.Hidden = True
Columns("K:K").EntireColumn.Hidden = True
Columns("M:AG").EntireColumn.Hidden = True
Rows("5:2001").Sort Key1:=Range("AK5"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
Selection.AutoFilter Field:=37, Criteria1:=">=1/1/2007",
Operator:=xlAnd
lngRow = Range("AK2001").End(xlUp).Row + 1
Rows(lngRow & ":2001").EntireRow.Hidden = True
Cells(2003, "AM").Formula = "=Subtotal(9,$AM$1:$AM$2001)"
ActiveSheet.PageSetup.PrintArea = Rows("4:" & lngRow - 1).Address
'?
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
.LeftHeader = "PAGE NO. &P"
.CenterHeader = "ORDERS MONTH-TO-DATE"
.RightHeader = "&D, &T"
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 4
.PrintErrors = xlPrintErrorsDisplayed
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter Field:=37
Cells.Select
Selection.EntireRow.Hidden = False
Selection.EntireColumn.Hidden = False
Rows("5:2002").Select
Selection.Sort Key1:=Range("A5"), Order1:=xlAscending,
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
Range("B4").Select
End Sub
 
T

Tom Ogilvy

ActiveSheet.PageSetup.PrintArea = Rows("4:" & lngRow - 1).Address

sets the print area. you would need to make it something like

ActiveSheet.PageSetup.PrintArea = Rows("4:2003).Address

the rows beyond lngRow and 2001 should be hidden, so this shouldn't create a
problem.
 
T

tahrah

Tom - Thank you! That worked great. I don't know what I'd do without
you guys here on Google Groups. This place is great!

Regards,
Tahrah
 

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

Trying to hide all rows where no data is in columns AQ-AV 2
Trying to delete rows and it's not working 4
VBA group the number in the Column and Copy to the new worksheet. 0
Setting Print Range 2
Printing-don't need half this code but which half?:-) 5
Set print area based on last row with a given value 3
Excel Page Setup on selected worksheets 2
Copy rows where cells contain red and black font. 2

Top