Run Time Error '9':subscript out of range

[meggie]

Hi,
I am realativley new user of VBA and am having trouble getting cells on one
page equal to the particular cells in the other page. I previosuly had the
code set up for cells on the same page equaling each other and the code was
running fine. I now get a Run time Error '9':sbscript out of range I have
checked and made sure that the page name is correct however am still getting
this error. Any help would be fantastic

Sub herdparameters()

For i = 1 To 20

Sheets("With hmodel")(8, 3).Value = Sheets("With herd").Select(46 + i,
3).Value

Sheets("With hmodel")(12, 3).Value = Sheets("With herd")(46 + i, 23).Value

Sheets("with hmodel")(13, 3).Value = Sheets("with herd")(46 + i, 29).Value

Sheets("with hmodel")(11, 6).Value = Sheets("with herd")(46 + i, 15).Value

Sheets("with hmodel")(12, 6).Value = Sheets("with herd")(46 + i, 10).Value

Calculate

Next

End Sub

 

[Gary''s Student]

With
or
with
??

 

[FSt1]

hi
not sure what you are trying to do but it looks like you have syntax
problems.
maybe this would work better.
Sub herdparameters()

For i = 1 To 20

Sheets("with hmodel").Cells(8, 3).Value = Sheets("with herd").Cells(46 + i,
3).Value

Sheets("With hmodel").Cells(12, 3).Value = Sheets("With herd").Cells(46 + i,
23).Value

Sheets("with hmodel").Cells(13, 3).Value = Sheets("with herd").Cells(46 + i,
29).Value

Sheets("with hmodel").Cells(11, 6).Value = Sheets("with herd").Cells(46 + i,
15).Value

Sheets("with hmodel").Cells(12, 6).Value = Sheets("with herd").Cells(46 + i,
10).Value

Calculate

Next

End Sub

regards
FSt1

 

[Jacob Skaria]

Try the below..

Sub herdparameters()

Dim ws1 As Worksheet
Dim ws2 As Worksheet

Set ws1 = Sheets("With hmodel")
Set ws2 = Sheets("With herd")

For i = 1 To 20
ws1.Cells(8, 3).Value = ws2.Cells(46 + i, 3).Value
ws1.Cells(12, 3).Value = ws2.Cells(46 + i, 23).Value
ws1.Cells(13, 3).Value = ws2.Cells(46 + i, 29).Value
ws1.Cells(11, 6).Value = ws2.Cells(46 + i, 15).Value
ws1.Cells(12, 6).Value = ws2.Cells(46 + i, 10).Value

Calculate
Next

End Sub

 

[Dave Peterson]

You should copy the code from the VBE and paste into your message.

The code you posted won't work.

Sheets("With hmodel")(8, 3).Value = Sheets("With herd").Select(46 + i, 3).Value
Is not correct.

maybe you meant:
Sheets("With hmodel").cells(8, 3).Value _
= Sheets("With herd").cells(46 + i, 3).Value

But that doesn't explain the subscript error. I'm guessing that one (or more)
of the sheet name(s) is spelled incorrectly.

Or the wrong workbook is active????

 

[Dave Peterson]

Upper/lower case won't matter in this case.

With
or
with
??

 

[meggie]

Thanks Jacob that solves my problem perfectly.
Thanks Again