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

You need to put your ranges and commas all inside one set of quotes:

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,e 89:e98,e106,e112:e114,e142:e147,e149:e152,e157:e16 8,e170:e176,e178:e185,e189:e195")
OpeningRange.Value = ClosingRange.Value


"srinivasan" wrote:

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

Mr Charlie,

No it is not working. it gives value of cell g9 in all the cells. It has to
be fine tuned, I think.

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

ok try this:

Dim ClosingCell As Range

For Each ClosingCell In
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")
Worksheets(ActiveSheet.Index + 1).Cells(ClosingCell.Row, ClosingCell.Column
- 2) = ClosingCell
Next ClosingCell


"srinivasan" wrote:

Mr Charlie,

No it is not working. it gives value of cell g9 in all the cells. It has to
be fine tuned, I think.

Thank a lot Mr Norman & Charlie for your immediate response. The first code
of Mr Norman works fine and I will give a try for the others.

Regards,

srinivasan.V