See below (and smack your forehead). This is an example of why
programmers advise that you always use Option Explicit.

Alan Beban

Snowfire wrote:
Thanks for the suggestions but both fail or give an error message.....
This is the full macro...

Sub Test()
' Dim Destrange As Range, LastRowData As Range, LastRowTotals As
Range
Dim Smallrng As Range
Dim Newsh As Worksheet
Dim Ash As Worksheet
Dim Lr As Long

Application.ScreenUpdating = False

Set Ash = ActiveSheet
Set Newsh = Worksheets.Add
Ash.Select

Lr = 1

' LastRowData = Range("H1:T9") '&
ActiveSheet.Cells(Cells.Rows.Count, 8).End(xlUp).Row)
' LastRowTotals = Range("B3:E5") ' &
ActiveSheet.Cells(Cells.Rows.Count, 2).End(xlUp).Row)
Set LastRowData = Range("H1:T9") '&
ActiveSheet.Cells(Cells.Rows.Count, 8).End(xlUp).Row)
setLastRowTotals = Range("B3:E5") ' & <---------------Change to Set LastRowTotals
ActiveSheet.Cells(Cells.Rows.Count, 2).End(xlUp).Row)

For Each Smallrng In Ash.Range(LastRowTotals, LastRowData).Areas
Smallrng.Copy
Set Destrange = Newsh.Cells(Lr, 1)
Destrange.PasteSpecial xlPasteValues
Destrange.PasteSpecial xlPasteFormats
Lr = Lr + Smallrng.Rows.Count + 1
Next Smallrng


With Newsh.PageSetup
.Orientation = xlLandscape
End With
Newsh.Columns.AutoFit

Newsh.PrintPreview 'PrintOut

Application.DisplayAlerts = False
Newsh.Delete
Application.DisplayAlerts = True

Application.ScreenUpdating = True

End Sub

I know the start of the ranges I need to capture, just need to find
the last row each time which works but can't get it to work in the for/
each statement?