Question

excel高手请进~ ,帮忙优化vba代码

刚接触Vba都不懂，有什么好的方式优化以下VBA功能？

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i, j As Integer
i = Target.Row
j = Target.Column
Application.Calculation = xlManual
Application.EnableEvents = False
If i = 1005 And j = 7 Then
For i = 4 To 1003
Sheet1.Cells(i, 1) = Sheet1.Cells(i, 1).Value + 1
Sheet1.Cells(i, 2) = Sheet1.Cells(i + 1, 2)
Next i
Sheet1.Cells(1003, 2) = Sheet1.Cells(1005, 7)
End If
Application.Calculation = xlAutomatic
Application.EnableEvents = True

End Sub

代码功能：A4~A1003 是序号，
B4~B1003 是变量数据

G列1005行单元格输入一个新数据后触发事件，发生即（A4、B4）取消一个最早序号和数据，（A1003，B1003)新增一个最新序号和数据，然后序号和数据同步在固定（A列和B列4~1003行）范围内变化。

有其他3张工作表引用这两列变化的数据，Excel运行变得非常慢。还不如我用手工的速度快。我用手工复制 (A5:B1003) 粘贴到 (A4:B1002) 再在 A1003 和 B1003 填入新数据，只有几秒的操作过程。而用VBA就要几十秒钟或更长。
因需要频繁输入新数据，有什么好的方式重写优化以上代码呢？

（非常抱歉~财富悬赏值用完了）

Answer 1

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i, j As Integer
i = Target.Row
j = Target.Column

Application.Calculation = xlManual
Application.EnableEvents = False

If i = 1005 And j = 7 Then
'x1 不可以 Dim
x1 = Sheet1.Range("A4,B1003")

For i = 4 To 1003
x1(i, 1) = x1(i, 1) + 1
x1(i, 2) = x1(i + 1, 2)
Next i

x1(1003, 2) = Sheet1.Cells(1005, 7)
Sheet1.Range("A4:B1003") = x1
End If
Application.Calculation = xlAutomatic
Application.EnableEvents = True

End Sub

Answer 2

试试这个
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = "$G$5" Then
Range("A5:B1003").Copy
Range("A4").Select
ActiveSheet.Paste
Range("A1003") = Range("A1002") + 1
Range("B1003") = Target
End If
Application.EnableEvents = True
End Sub