T
tahrah
I just did a simple macro by using the record function. I am trying to
create a macro that hides certain columns, then on the columns that are
left, it hides the rows of records with no information in a certain
field. Then it sorts the remaining items and resets the print area,
then prints. This is what I've come up with so far but it DOES NOT
hide the rows of records with no information in the cell (cell would be
Column Q). Also, it's resetting the print area but the printout is
still coming out BLANK???? I appreciate any help you can offer.
Sub Open_Quotes()
'
' Open_Quotes Macro
' Macro recorded 1/5/2007 by Tahrah Hunt
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
Rows("3:5").Select
Selection.EntireRow.Hidden = True
Columns("A:I").Select
Selection.EntireColumn.Hidden = True
Columns("K:K").Select
Selection.EntireColumn.Hidden = True
Columns("M:M").Select
Selection.EntireColumn.Hidden = True
Columns("T:T").Select
Selection.EntireColumn.Hidden = True
Columns("V:AM").Select
Selection.EntireColumn.Hidden = True
ActiveWindow.LargeScroll ToRight:=-1
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 100
ActiveWindow.ScrollRow = 132
ActiveWindow.ScrollRow = 218
ActiveWindow.ScrollRow = 264
ActiveWindow.ScrollRow = 322
ActiveWindow.ScrollRow = 440
ActiveWindow.ScrollRow = 511
ActiveWindow.ScrollRow = 579
ActiveWindow.ScrollRow = 636
ActiveWindow.ScrollRow = 704
ActiveWindow.ScrollRow = 851
ActiveWindow.ScrollRow = 923
ActiveWindow.ScrollRow = 1001
ActiveWindow.ScrollRow = 1119
ActiveWindow.ScrollRow = 1169
ActiveWindow.ScrollRow = 1223
ActiveWindow.ScrollRow = 1273
ActiveWindow.ScrollRow = 1370
ActiveWindow.ScrollRow = 1413
ActiveWindow.ScrollRow = 1452
ActiveWindow.ScrollRow = 1513
ActiveWindow.ScrollRow = 1538
ActiveWindow.ScrollRow = 1563
ActiveWindow.ScrollRow = 1584
ActiveWindow.ScrollRow = 1609
ActiveWindow.ScrollRow = 1631
ActiveWindow.ScrollRow = 1656
ActiveWindow.ScrollRow = 1677
ActiveWindow.ScrollRow = 1702
ActiveWindow.ScrollRow = 1717
ActiveWindow.ScrollRow = 1731
ActiveWindow.ScrollRow = 1742
ActiveWindow.ScrollRow = 1752
ActiveWindow.ScrollRow = 1759
ActiveWindow.ScrollRow = 1770
ActiveWindow.ScrollRow = 1788
ActiveWindow.ScrollRow = 1802
ActiveWindow.ScrollRow = 1817
ActiveWindow.ScrollRow = 1831
ActiveWindow.ScrollRow = 1842
ActiveWindow.ScrollRow = 1852
ActiveWindow.ScrollRow = 1860
ActiveWindow.ScrollRow = 1867
ActiveWindow.ScrollRow = 1877
ActiveWindow.ScrollRow = 1892
ActiveWindow.ScrollRow = 1902
ActiveWindow.ScrollRow = 1913
ActiveWindow.ScrollRow = 1928
ActiveWindow.ScrollRow = 1942
ActiveWindow.ScrollRow = 1953
ActiveWindow.ScrollRow = 1967
ActiveWindow.ScrollRow = 1974
Rows("7:2001").Select
Range("A2001").Activate
Selection.Sort Key1:=Range("Q7"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("S22").Select
ActiveWindow.LargeScroll Down:=5
ActiveWindow.ScrollRow = 104
ActiveWindow.ScrollRow = 107
ActiveWindow.ScrollRow = 114
ActiveWindow.ScrollRow = 118
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 125
ActiveWindow.ScrollRow = 129
ActiveWindow.ScrollRow = 136
ActiveWindow.ScrollRow = 139
ActiveWindow.ScrollRow = 143
ActiveWindow.ScrollRow = 146
ActiveWindow.ScrollRow = 150
ActiveWindow.ScrollRow = 154
ActiveWindow.ScrollRow = 150
ActiveWindow.ScrollRow = 146
ActiveWindow.ScrollRow = 143
ActiveWindow.ScrollRow = 139
Range("A6:U143").Select
Range("U143").Activate
ActiveSheet.PageSetup.PrintArea = "$A$6:$U$143"
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$A$6:$U$143"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.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.EntireColumn.Hidden = False
Selection.EntireRow.Hidden = False
Range("B6").Select
End Sub
create a macro that hides certain columns, then on the columns that are
left, it hides the rows of records with no information in a certain
field. Then it sorts the remaining items and resets the print area,
then prints. This is what I've come up with so far but it DOES NOT
hide the rows of records with no information in the cell (cell would be
Column Q). Also, it's resetting the print area but the printout is
still coming out BLANK???? I appreciate any help you can offer.
Sub Open_Quotes()
'
' Open_Quotes Macro
' Macro recorded 1/5/2007 by Tahrah Hunt
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
Rows("3:5").Select
Selection.EntireRow.Hidden = True
Columns("A:I").Select
Selection.EntireColumn.Hidden = True
Columns("K:K").Select
Selection.EntireColumn.Hidden = True
Columns("M:M").Select
Selection.EntireColumn.Hidden = True
Columns("T:T").Select
Selection.EntireColumn.Hidden = True
Columns("V:AM").Select
Selection.EntireColumn.Hidden = True
ActiveWindow.LargeScroll ToRight:=-1
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 100
ActiveWindow.ScrollRow = 132
ActiveWindow.ScrollRow = 218
ActiveWindow.ScrollRow = 264
ActiveWindow.ScrollRow = 322
ActiveWindow.ScrollRow = 440
ActiveWindow.ScrollRow = 511
ActiveWindow.ScrollRow = 579
ActiveWindow.ScrollRow = 636
ActiveWindow.ScrollRow = 704
ActiveWindow.ScrollRow = 851
ActiveWindow.ScrollRow = 923
ActiveWindow.ScrollRow = 1001
ActiveWindow.ScrollRow = 1119
ActiveWindow.ScrollRow = 1169
ActiveWindow.ScrollRow = 1223
ActiveWindow.ScrollRow = 1273
ActiveWindow.ScrollRow = 1370
ActiveWindow.ScrollRow = 1413
ActiveWindow.ScrollRow = 1452
ActiveWindow.ScrollRow = 1513
ActiveWindow.ScrollRow = 1538
ActiveWindow.ScrollRow = 1563
ActiveWindow.ScrollRow = 1584
ActiveWindow.ScrollRow = 1609
ActiveWindow.ScrollRow = 1631
ActiveWindow.ScrollRow = 1656
ActiveWindow.ScrollRow = 1677
ActiveWindow.ScrollRow = 1702
ActiveWindow.ScrollRow = 1717
ActiveWindow.ScrollRow = 1731
ActiveWindow.ScrollRow = 1742
ActiveWindow.ScrollRow = 1752
ActiveWindow.ScrollRow = 1759
ActiveWindow.ScrollRow = 1770
ActiveWindow.ScrollRow = 1788
ActiveWindow.ScrollRow = 1802
ActiveWindow.ScrollRow = 1817
ActiveWindow.ScrollRow = 1831
ActiveWindow.ScrollRow = 1842
ActiveWindow.ScrollRow = 1852
ActiveWindow.ScrollRow = 1860
ActiveWindow.ScrollRow = 1867
ActiveWindow.ScrollRow = 1877
ActiveWindow.ScrollRow = 1892
ActiveWindow.ScrollRow = 1902
ActiveWindow.ScrollRow = 1913
ActiveWindow.ScrollRow = 1928
ActiveWindow.ScrollRow = 1942
ActiveWindow.ScrollRow = 1953
ActiveWindow.ScrollRow = 1967
ActiveWindow.ScrollRow = 1974
Rows("7:2001").Select
Range("A2001").Activate
Selection.Sort Key1:=Range("Q7"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("S22").Select
ActiveWindow.LargeScroll Down:=5
ActiveWindow.ScrollRow = 104
ActiveWindow.ScrollRow = 107
ActiveWindow.ScrollRow = 114
ActiveWindow.ScrollRow = 118
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 125
ActiveWindow.ScrollRow = 129
ActiveWindow.ScrollRow = 136
ActiveWindow.ScrollRow = 139
ActiveWindow.ScrollRow = 143
ActiveWindow.ScrollRow = 146
ActiveWindow.ScrollRow = 150
ActiveWindow.ScrollRow = 154
ActiveWindow.ScrollRow = 150
ActiveWindow.ScrollRow = 146
ActiveWindow.ScrollRow = 143
ActiveWindow.ScrollRow = 139
Range("A6:U143").Select
Range("U143").Activate
ActiveSheet.PageSetup.PrintArea = "$A$6:$U$143"
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$A$6:$U$143"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.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.EntireColumn.Hidden = False
Selection.EntireRow.Hidden = False
Range("B6").Select
End Sub