Spent Hours Looking for help on this Board, but...

Posted by Craig H. on March 15, 2001 6:40 PM

Would any of you Excel masters be kind enough to have a look at my macro? I am an embryo at this, but trying to learn. What I am looking to do is select a range in the following column(which changes by the day)and have it stop at a blank cell. There has been somewhat close answers on the board, but can't quite get it to gel. Would be most grateful.
Craig

Sub PrintLoop()
'
' PrintLoop Macro
' Macro recorded 2/20/2001 by Craig Hansen
'

'
Do Until ActiveCell.Value = ""
Set MyRange = Sheets("Excess2").Range("J466:J820")
For Each Cell In MyRange
Cell.Copy
Application.Goto Reference:="DNUM"
ActiveSheet.Paste
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("A2").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next
Loop
End Sub

Posted by Dave Hawley on March 15, 2001 9:47 PM

PrintLoop Macro Macro recorded 2/20/2001 by Craig Hansen

Hi Craig

Try this:

Sub PrintLoop()
Dim MyRange As Range

Set MyRange = Sheets("Excess2").Range("J466")

Range(MyRange, MyRange.End(xlDown)).Copy _
Destination:=Range("DNUM")
Application.Goto Range("DNUM"), True
Application.CutCopyMode = False
Range("DNUM").HorizontalAlignment = xlCenter
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

End Sub

OzGrid Business Applications

Posted by Ivan Moala on March 15, 2001 10:52 PM

PrintLoop Macro Macro recorded 2/20/2001 by Craig Hansen

Craig
Will the following work for you;

Set MyRange = Sheets("EXCESS2").Range("J466:J820")

For Each cell In MyRange
If cell.Value &LT;&GT; "" Then
cell.Copy
Application.Goto Reference:="DNUM"
ActiveSheet.Paste
With Selection
.Font.Bold = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("A2").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If
Next

End Sub

Ivan