Excel loop error -- 25 rows okay, but 30 too many

[[email protected]]

I have a function that copies rows from a "template" spreadsheet then
paste the template information on a number of worksheets as determined
by a cell value. The problem is the macro works fine for the range
(A24:BR52) but doesnt work for (A24:BR55).

By doesn't work I mean the following: The location where the macro is
run from is separate sheet from any of the pasting destinations and
template data (i.e. a third worksheet). Yet when the range hits ~30
lines, the data is pasted into the macro sheet's location --- and into
cells beginning at C16 (a really strange location as the others paste
beginning in A24) -- as well as the proper destination on the other
sheets. Is there a programming error by me or does VB have a
glitch/memory problem??

THANK YOU VERY MUCH FOR ANY HELP -- here is the relevant code, with all
the code appearing at the very bottom.

--------------Relevant
code-------------------------------------------------

If Target > old Then 'The target number
of countries, is it increasing?
For i = old To Target ' for the increase from current # of
countries to target
Sheet8.Range("A24:BR90").Copy
Sheet1.Cells(i + BeginRow, 1).EntireRow.Hidden = False


'unhide the additional input rows
Sheets("Country " & i).Activate
ActiveSheet.Visible = True 'unhide the
additional input worksheets
ActiveSheet.Range("A24:BR90").PasteSpecial
ActiveSheet.Range("A1").Select
Sheet8.Application.CutCopyMode = False

Next i


--------------------------------------------------------------(All the
code)--------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'Do nothing if more than one cell is changed or content deleted
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

If Target.Address = "$D$9" Then
'Ensure target is a number before proceeding
If IsNumeric(Target) Then
'Stop any possible runtime errors and halting code
On Error Resume Next
'Turn off ALL events so macro does not put the code
into a loop.
Application.EnableEvents = False

Application.ScreenUpdating = False ' removes flicker when
macro's run

Dim old As Integer
old = Cells(351, 24) ' reads in the previous number of
countries (before the selection for more/less countries)

BeginRow = 15 ' --> CHANGE NUMBER - One less than first row of
country input rows

If Target > old Then 'The target number of countries, is it
increasing?


For i = old To Target ' for the increase from current # of
countries to target
Sheet8.Range("A24:BR90").Copy
'A24:BR510'Sheet1.Cells(i + BeginRow,
1).EntireRow.Hidden = False 'unhide the additional input rows
Sheets("Country " & i).Activate
ActiveSheet.Visible = True 'unhide the additional
input worksheets
ActiveSheet.Range("A24:BR90").PasteSpecial
ActiveSheet.Range("A1").Select
Sheet8.Application.CutCopyMode = False

Next i

'remove copy area on template from memory

'For j = old To Target
'Sheet1.Cells(j + BeginRow, 1).EntireRow.Hidden = False
'unhide the additional input rows
'Next j


ElseIf Target < old Then 'The target number of countries, is
it decreasing?

For i = Target To old ' for the decrease from current # of
countries
Sheet1.Cells(i + BeginRow + 1, 1).EntireRow.Hidden =
True ' Hide the extra input rows
'Sheets("Country " & i).Range("A24:BR510").Clear
Sheets("Country " & i + 1).Visible = False ' hide the
extra worksheets
Next i


' For the one country situation hide mutualized row,
allocation columns,
' mutualized worksheet and Summary-all worksheet


End If

Sheet1.Select

If Target = 1 Then
Cells(46, 1).EntireRow.Hidden = True
Cells(1, 9).EntireColumn.Hidden = True
Cells(1, 10).EntireColumn.Hidden = True
Cells(1, 23).EntireColumn.Hidden = True
Cells(1, 24).EntireColumn.Hidden = True
Sheet2.Visible = False
Sheet6.Visible = False
Sheet3.Rows(12).Hidden = True
Sheet3.Rows(13).Hidden = True
Sheet3.Rows("53:78").RowHeight = 1.5
Sheet3.Rows("120:162").RowHeight = 1.5
Sheet3.Rows("53:78").EntireRow.Hidden = True
Sheet3.Rows("120:162").EntireRow.Hidden = True

Else
Cells(46, 1).EntireRow.Hidden = False
Cells(1, 9).EntireColumn.Hidden = False
Cells(1, 10).EntireColumn.Hidden = False
Cells(1, 23).EntireColumn.Hidden = False
Cells(1, 24).EntireColumn.Hidden = False
Sheet2.Visible = True
Sheet6.Visible = True
Sheet3.Rows(12).Hidden = False
Sheet3.Rows(13).Hidden = False
Sheet3.Rows("53:78").RowHeight = 12.75
Sheet3.Rows("120:162").RowHeight = 12.75
Sheet3.Rows("53:78").EntireRow.Hidden = False
Sheet3.Rows("120:162").EntireRow.Hidden = False
End If

Cells(351, 24) = Target ' records the number of countries
currently selected

Application.ScreenUpdating = True ' undoes the stopping of the
flicker
'Turn events back on
Application.EnableEvents = True
'Allow run time errors again
On Error GoTo 0
End If
End If




End Sub

[lexcel]

Sorry to say, but your code is very hard to read.
I would suggest you rewrite it from scratch, using named ranges,
constants and variables instead of all these cell references and row
numbers.