Subscript out or Ranger Error 9 - I'm lost here!

Hi folks:

I'm getting an error when running this script. Mind you I have been running it for 3 weeks without errors and I have NOT made any changes.

Any help welcome. I have two opened workbooks and have several sheets in each. I need to copy from one workbook to the other specific cell ranges.

========================================
Sub PasteRanges()

Const rng1 = "D9"
Const rng2 = "O6:O8"
Const rng3 = "A17:A25"
Const rng4 = "G18:G25"
Const rng5 = "I16:AC16"
Const rng6 = "I21:AC25"
Const rng7 = "AG17"
Const rng8 = "AG21:AG26"
'
If ActiveWorkbook.Name <> "Forecast by Cost All MASTER MACRO.xls" Then
'Assume ACTIVE sheet is SOURCE sheet
With ThisWorkbook.Worksheets(ActiveSheet.Name) - I get error here --
.Range(rng1).Value = ActiveSheet.Range(rng1).Value
.Range(rng2).Value = ActiveSheet.Range(rng2).Value
.Range(rng3).Value = ActiveSheet.Range(rng3).Value
.Range(rng4).Value = ActiveSheet.Range(rng4).Value
.Range(rng5).Value = ActiveSheet.Range(rng5).Value
.Range(rng6).Value = ActiveSheet.Range(rng6).Value
.Range(rng7).Value = ActiveSheet.Range(rng7).Value
.Range(rng8).Value = ActiveSheet.Range(rng8).Value
'.Range(rng9).Value = ActiveSheet.Range(rng9).Value
End With
Else:
Exit Sub
'
End If
End Sub

Halem

You'll need to do some careful debugging. A 'subscript out of range error'
is thrown when Excel doesn't have the referenced item in a collection or
array. So it is saying that it doesn't have a worksheet called
activesheet.name. That could be because something else is active perhaps
but you can run the code to this point by using F8 to step through and when
you get to this line type

?activesheet.name

and press enter, this will give you what Excel 'thinks' the name is

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
[email protected]HIS


"halem2" <[email protected]> wrote in
message news:[email protected]...
>
> Hi folks:
>
> I'm getting an error when running this script. Mind you I have been
> running it for 3 weeks without errors and I have NOT made any changes.
>
>
> Any help welcome. I have two opened workbooks and have several sheets
> in each. I need to copy from one workbook to the other specific cell
> ranges.
>
> ========================================
> Sub PasteRanges()
>
> Const rng1 = "D9"
> Const rng2 = "O6:O8"
> Const rng3 = "A17:A25"
> Const rng4 = "G18:G25"
> Const rng5 = "I16:AC16"
> Const rng6 = "I21:AC25"
> Const rng7 = "AG17"
> Const rng8 = "AG21:AG26"
> '
> If ActiveWorkbook.Name <> "Forecast by Cost All MASTER MACRO.xls" Then
> 'Assume ACTIVE sheet is SOURCE sheet
> With ThisWorkbook.Worksheets(ActiveSheet.Name) - I get
> error here --
> Range(rng1).Value = ActiveSheet.Range(rng1).Value
> Range(rng2).Value = ActiveSheet.Range(rng2).Value
> Range(rng3).Value = ActiveSheet.Range(rng3).Value
> Range(rng4).Value = ActiveSheet.Range(rng4).Value
> Range(rng5).Value = ActiveSheet.Range(rng5).Value
> Range(rng6).Value = ActiveSheet.Range(rng6).Value
> Range(rng7).Value = ActiveSheet.Range(rng7).Value
> Range(rng8).Value = ActiveSheet.Range(rng8).Value
> '.Range(rng9).Value = ActiveSheet.Range(rng9).Value
> End With
> Else:
> Exit Sub
> '
> End If
> End Sub
>
>
>
>
> --
> halem2
> ------------------------------------------------------------------------
> halem2's Profile:
> http://www.excelforum.com/member.php...fo&userid=9930
> View this thread: http://www.excelforum.com/showthread...hreadid=547631
>