Hi Srinivasan,

Try:

'==========================
Sub Open_To_Close()
Dim ClosingRange As Range
Dim OpeningRange As Range
Dim i As Long

Set ClosingRange = ActiveSheet.Range( _
"g9:g12, g17:g22, g28:g39," _
& "g45 ,g48 ,g62: g84 , g89: g98 , g106," _
& "g112: g114 , g142: g147 ,g149: g152 ," _
& "g157: g168 , g170: g176 , g178: g185 ," _
& "g189: g195 ")

Set OpeningRange = Worksheets(ActiveSheet.Index + 1). _
Range("e9:e12, e17:e22, e28:e39," _
& "e45 ,e48 ,e62: e84 , e89: e98 , e106," _
& "e112: e114 , e142: e147 ,e149: e152 ," _
& "e157: e168 , e170: e176 , e178: e185 ," _
& "e189: e195 ")

For i = 1 To ClosingRange.Areas.Count
OpeningRange.Areas(i).Value = ClosingRange.Areas(i).Value
Next i

End Sub

'<<==========================

Or, more simply:

'==========================
Sub Open_To_Close2()
Dim ClosingRange As Range
Dim OpeningRange As Range
Dim destAddress As String
Dim i As Long

On Error Resume Next
Set ClosingRange = ActiveSheet.Range("G9:G195"). _
SpecialCells(xlConstants)
On Error GoTo 0

If Not ClosingRange Is Nothing Then
destAddress = ClosingRange.Offset(, -2).Address
Set OpeningRange = ActiveSheet.Next.Range(destAddress)

For i = 1 To ClosingRange.Areas.Count
OpeningRange.Areas(i).Value = ClosingRange.Areas(i).Value
Next i
End If

End Sub

'<<==========================


Or, alternatively, perhaps
:
'==========================
Sub Open_To_Close3()
Dim ClosingRange As Range
Dim OpeningRange As Range

Set ClosingRange = ActiveSheet.Range("G9:G195")
Set OpeningRange = ActiveSheet.Next.Range("E9:E195")

ClosingRange.Copy OpeningRange

On Error Resume Next
OpeningRange.SpecialCells(xlCellTypeFormulas).Clea r
On Error GoTo 0

End Sub

'<<==========================


---
Regards,
Norman



"srinivasan" wrote in message
...
I got this code from the community . When I give the range value from
G9:G195, it works fine. When I modified the range to exclude formulas as
detailed below it gives run time error message 450. I request to be
informed what went wrong with this?

Sub Open_To_Close()
Dim ClosingRange As Range
Dim OpeningRange As Range
Set ClosingRange = ActiveSheet.Range("g9:g12", "g17:g22", "g28:g39",
"g45",
"g48", "g62:g84", "g89:g98", "g106", "g112:g114", "g142:g147",
"g149:g152",
"g157:g168", "g170:g176", "g178:g185", "g189:g195")
Set OpeningRange = Worksheets(ActiveSheet.Index + 1).Range("e9:e12",
"e17:e22", "e28:e39", "e45", "e48", "e62:e84", "e89:e98", "e106",
"e112:e114", "e142:e147", "e149:e152", "e157:e168", "e170:e176",
"e178:e185",
"e189:e195")
OpeningRange.Value = ClosingRange.Value
End Sub

Thanks in advance