I am using this code from the user group to print separate areas of a
spreadsheet to one page.........
For Each Smallrng In Ash.Range("H1:T5, B3:E5").Areas
Smallrng.Copy
Set Destrange = Newsh.Cells(Lr, 1)
Destrange.PasteSpecial xlPasteValues
Destrange.PasteSpecial xlPasteFormats
Lr = Lr + Smallrng.Rows.Count + 1
Next Smallrng

The above works but I need to replace the ranges H1:T5 and B3:E5 with
variables to allow for changing ranges.
I have tried the obvious method of (for example) Range1 = "H1:T5" and
replaced the section of code with the variable but it fails to work. I
can't seam to get the right syntax to make it work.
Please embarrass me by pointing out the obvious solution.

hi
try this
instead of .....Range1 = "H1:T5"
use this.........range1 = Range("H1:T5")

regards
FSt1

"Snowfire" wrote:

I am using this code from the user group to print separate areas of a
spreadsheet to one page.........
For Each Smallrng In Ash.Range("H1:T5, B3:E5").Areas
Smallrng.Copy
Set Destrange = Newsh.Cells(Lr, 1)
Destrange.PasteSpecial xlPasteValues
Destrange.PasteSpecial xlPasteFormats
Lr = Lr + Smallrng.Rows.Count + 1
Next Smallrng

The above works but I need to replace the ranges H1:T5 and B3:E5 with
variables to allow for changing ranges.
I have tried the obvious method of (for example) Range1 = "H1:T5" and
replaced the section of code with the variable but it fails to work. I
can't seam to get the right syntax to make it work.
Please embarrass me by pointing out the obvious solution.

Snowfire wrote:
I am using this code from the user group to print separate areas of a
spreadsheet to one page.........
For Each Smallrng In Ash.Range("H1:T5, B3:E5").Areas
Smallrng.Copy
Set Destrange = Newsh.Cells(Lr, 1)
Destrange.PasteSpecial xlPasteValues
Destrange.PasteSpecial xlPasteFormats
Lr = Lr + Smallrng.Rows.Count + 1
Next Smallrng

The above works but I need to replace the ranges H1:T5 and B3:E5 with
variables to allow for changing ranges.
I have tried the obvious method of (for example) Range1 = "H1:T5" and
replaced the section of code with the variable but it fails to work. I
can't seam to get the right syntax to make it work.
Please embarrass me by pointing out the obvious solution.


Set Range1 = Range("H1:T5")

Alan Beban

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") ' &
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?

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?

Thanks Alan.... "couldn't see the wood for the trees"