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

[halem2]

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

 

[Nick Hodge]

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
(e-mail address removed)