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

H

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) -:confused: 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

:eek:
 
N

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top