The reason is that excel doesn't like selecting things on a sheet
which isn't the active sheet.
Also important in this case, from excel help:
------------------------------------------------------------
Range and Cells
Use Range(cell1, cell2), where cell1 and cell2 are range objects that
specify the start and end cells, to return a Range object. The
following example sets the border line style for cells A1:J10.
With Worksheets(1)
.Range(.Cells(1, 1),.Cells(10, 10)).Borders.LineStyle = xlThick
End With
Notice the period in front of each occurrence of the Cells property.
The period is required if the result of the preceding With statement
is to be applied to the Cells property— in this case, to indicate that
the cells are on worksheet one (without the period, the Cells property
would return cells on the active sheet).
-------------------------------------------------------------
Those last 5 words..
The quickest solution, as far as coding time goes, is to activate the
sheet before referring to those cells:
Sub RevPopulateDatabaseCorpCpn()
Dim a As Date
Dim z As Double 'count row# for sheet DatabaseCorpCpn
z = 2
a = Sheets("RevDatabaseCorpCpn").Cells(2, 1).Value
Do While Sheets("DatabaseCorpCpn").Cells(z, 1).Value <= a
z = z + 1
Loop
Do While Sheets("DatabaseCorpCpn").Cells(z, 1).Value > a
Sheets("RevDatabaseCorpCpn").Activate
Sheets("RevDatabaseCorpCpn").Cells(2, 1).Select
Selection.EntireRow.Insert Shift:=xlDown
Sheets("DatabaseCorpCpn").Activate
Sheets("DatabaseCorpCpn").Range(Cells(z, 1), Cells(z, 96)).Select
Selection.Copy
Sheets("RevDatabaseCorpCpn").Activate
Sheets("RevDatabaseCorpCpn").Range(Cells(2, 1), Cells(2, 96)).Select
ActiveSheet.Paste
a = Sheets("RevDatabaseCorpCpn").Cells(2, 1).Value
z = z + 1
Loop
End Sub
However, you could then do away with the explicit worksheet refs.:
Sub RevPopulateDatabaseCorpCpn()
Dim a As Date
Dim z As Double 'count row# for sheet DatabaseCorpCpn
z = 2
a = Sheets("RevDatabaseCorpCpn").Cells(2, 1).Value
Do While Sheets("DatabaseCorpCpn").Cells(z, 1).Value <= a
z = z + 1
Loop
Do While Sheets("DatabaseCorpCpn").Cells(z, 1).Value > a
Sheets("RevDatabaseCorpCpn").Activate
Cells(2, 1).Select
Selection.EntireRow.Insert Shift:=xlDown
Sheets("DatabaseCorpCpn").Activate
Range(Cells(z, 1), Cells(z, 96)).Select
Selection.Copy
Sheets("RevDatabaseCorpCpn").Activate
Range(Cells(2, 1), Cells(2, 96)).Select
ActiveSheet.Paste
a = Cells(2, 1).Value
z = z + 1
Loop
End Sub
Throw in a Application.Screenupdating=False and that gets rid of your
screen flicker.
I'd prefer to do away with all the sheet activating and range
selecting. This means no screen flicker to consider, much faster
execution (5 times faster), and successful execution regardless of
which sheet is active (even if it's not one of the two).
While I was at it I'd define the worksheets as objects to make the
code more readable.
Sub RevPopulateDatabaseCorpCpn()
Dim a As Date
Dim z As Double 'count row# for sheet DatabaseCorpCpn
Dim shtFrom As Worksheet, shtTo As Worksheet
Set shtFrom = Sheets("DatabaseCorpCpn")
Set shtTo = Sheets("RevDatabaseCorpCpn")
z = 2
a = shtTo.Cells(2, 1).Value
Do While shtFrom.Cells(z, 1).Value <= a
z = z + 1
Loop
Do While shtFrom.Cells(z, 1).Value > a
shtTo.Cells(2, 1).EntireRow.Insert Shift:=xlDown
shtTo.Range(shtTo.Cells(2, 1), shtTo.Cells(2, 96)) = _
shtFrom.Range(shtFrom.Cells(z, 1), shtFrom.Cells(z, 96)).Value
a = shtTo.Cells(2, 1).Value
z = z + 1
Loop
End Sub
Finally,
shtTo.Range(shtTo.Cells(2, 1), shtTo.Cells(2, 96)) = _
shtFrom.Range(shtFrom.Cells(z, 1), shtFrom.Cells(z, 96)).Value
can be replaced with
shtFrom.Range(shtFrom.Cells(z, 1), shtFrom.Cells(z, 96)) _
.Copy shtTo.Range(shtTo.Cells(2, 1), shtTo.Cells(2, 96))
which I *think* might copy formulae rather than values but if there
are no formulae it shouldn't be any different.