Macros the same but not acting the same

T

tahrah

I have three macros (thanks to Jim's help). They are all pretty much
set up the same but one of them is acting odd. I have a open_quotes
macro, open_samples macro, orders_mtd macro (all listed below). The
open_quotes macro and orders_mtd macro work just fine and when they
print, they print the row 4 which is the column headings. The
open_samples macro works fine but when it prints, does NOT print row 4
which is the column headings. I stepped through the macro and on the
fourth run of the following code it hides row 4:

For Each rngCell In Range("AD1", Cells(lngRow - 1, "AD"))
If Len(rngCell.Value) > 0 Then
rngCell.EntireRow.Hidden = True
End If
Next 'rngCell

What it's supposed to be doing is finding ONLY the rows "without" dates
in them. So just the samples that have NOT shipped. So if it finds a
cell in column AD with something in it, it hides it. BUT, it should be
leaving anything from rows 1,2,3,4 alone.

Here are the three macros:

Sub Open_Quotes()
' Open_Quotes Macro
' Macro recorded 1/8/2007 by Tahrah Hunt
' Keyboard Shortcut: Ctrl+Shift+Q
Dim lngRow As Long
Rows("1:3").EntireRow.Hidden = True
Columns("A:I").EntireColumn.Hidden = True
Columns("K:K").EntireColumn.Hidden = True
Columns("M:M").EntireColumn.Hidden = True
Columns("T:T").EntireColumn.Hidden = True
Columns("V:AM").EntireColumn.Hidden = True
Rows("5:2001").Sort Key1:=Range("Q5"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
lngRow = Range("Q2001").End(xlUp).Row + 1
Rows(lngRow & ":2001").EntireRow.Hidden = True
For Each rngCell In Range("U1", Cells(lngRow - 1, "U"))
If rngCell.Value = "order received" Then
rngCell.EntireRow.Hidden = True
End If
Next 'rngCell
For Each rngCell In Range("U1", Cells(lngRow - 1, "U"))
If rngCell.Value = "order received" Then
rngCell.EntireRow.Hidden = True
End If
Next 'rngCell
For Each rngCell In Range("U1", Cells(lngRow - 1, "U"))
If rngCell.Value = "Order received" Then
rngCell.EntireRow.Hidden = True
End If
Next 'rngCell
For Each rngCell In Range("U1", Cells(lngRow - 1, "U"))
If rngCell.Value = "ORDER RECEIVED" Then
rngCell.EntireRow.Hidden = True
End If
Next 'rngCell
For Each rngCell In Range("U1", Cells(lngRow - 1, "U"))
If rngCell.Value = "Order Received" Then
rngCell.EntireRow.Hidden = True
End If
Next 'rngCell
ActiveSheet.PageSetup.PrintArea = Rows("4:" & lngRow - 1).Address
'?
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
.LeftHeader = "PAGE NO. &P"
.CenterHeader = "OPEN QUOTES"
.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
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
Sub Open_Samples()
' Open_Samples Macro
' Macro recorded 1/8/2007 by Tahrah Hunt
' Keyboard Shortcut: Ctrl+Shift+S
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("N:U").EntireColumn.Hidden = True
Columns("AH:AM").EntireColumn.Hidden = True
Columns("AA:AB").EntireColumn.Hidden = True
Columns("AE:AF").EntireColumn.Hidden = True
Rows("5:2001").Sort Key1:=Range("Y5"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
lngRow = Range("Y2001").End(xlUp).Row + 1
Rows(lngRow & ":2001").EntireRow.Hidden = True
For Each rngCell In Range("AD1", Cells(lngRow - 1, "AD"))
If Len(rngCell.Value) > 0 Then
rngCell.EntireRow.Hidden = True
End If
Next 'rngCell
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
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


I appreciate any help.

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
Sum the remaining rows and place the result in the row below the last 6
Trying to delete rows and it's not working 4
Hide columns and rows then separate the spreadsheet into different files 1
Code to sort rows getting hung up by merged cells 0
Code to Create Chart 0
Runtime error 1004 Application-defined or object defined error" on data sort 1
VB Printing problem 0

Top