跨工作簿执行VBA滚动定位后，如何避免源工作簿单元格偏移？

问题原因 #

你的宏在激活Workbook B的工作表并执行滚动操作后，返回Workbook A时未恢复其原本的滚动状态，导致A的工作表也跳转到相同单元格。

解决方案 #

在宏启动时先记录Workbook A的激活工作表及滚动位置，完成Workbook B的操作后再恢复这些状态即可。

修改后的完整代码 #

' 新增变量保存Workbook A的初始状态
Dim wbA As Workbook
Dim wsA As Worksheet
Dim scrollRowA As Long
Dim scrollColA As Long

' 记录Workbook A的激活表和滚动位置
Set wbA = ThisWorkbook
Set wsA = wbA.ActiveSheet
scrollRowA = wbA.Windows(1).ScrollRow
scrollColA = wbA.Windows(1).ScrollColumn

Dim wb As Workbook
Dim Source As String
Dim ws1 As Worksheet
Dim ws2 As Worksheet

Dim Field_Row1 As Long
Dim FirstRow1 As Long
Dim LastColumn1 As Long
Dim LastRow1 As Long

Dim FirstRow2 As Long
Dim LastColumn2 As Long
Dim LastRow2 As Long

Dim ws1Rng As Range
Dim ws2Rng As Range

Source = wbA.Range("F12").Value ' 明确引用Workbook A的单元格

Set wb = Workbooks(Source)

Set ws1 = wb.Worksheets("Pivot - Full Extract")
Set ws2 = wb.Worksheets("Full Extract")

Field_Row1 = Application.WorksheetFunction.Match("taxlot", ws1.Columns(3), 0)
FirstRow1 = Field_Row1 + 1
LastColumn1 = ws1.Cells(Field_Row1, ws1.Columns.Count).End(xlToLeft).Column
LastRow1 = ws1.Cells(ws1.Rows.Count, "C").End(xlUp).Row - 1

FirstRow2 = 2
LastColumn2 = ws2.Cells(1, ws2.Columns.Count).End(xlToLeft).Column
LastRow2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row ' 修正原代码中误用ws1.Rows.Count的错误

ws1Rows = LastRow1 - FirstRow1
ws2Rows = LastRow2 - FirstRow2

C = ws1Rows - ws2Rows

Set ws1Rng = ws1.Range(ws1.Cells(FirstRow1, LastColumn1), ws1.Cells(LastRow1, LastColumn1))
Set ws2Rng = ws2.Range(ws2.Cells(FirstRow2, LastColumn2), ws2.Cells(LastRow2, LastColumn2))

ws1MVTotal = Application.WorksheetFunction.Sum(ws1Rng)
ws2MVTotal = Application.WorksheetFunction.Sum(ws2Rng)

If ws1MVTotal = ws2MVTotal Then
    MsgBox "Same MV Total"
ElseIf ws1MVTotal <> ws2MVTotal And C = 0 Then
    ws1.Range("C" & FirstRow1, ws1.Cells(LastRow1, LastColumn1)).Copy ' 修正原代码中FirtRow1的拼写错误
    ws2.Range("A2", ws2.Cells(LastRow2, LastColumn2)).PasteSpecial Paste:=xlPasteValues
    MsgBox "Hey"
    Application.CutCopyMode = False
ElseIf Not C = 0 Then
    MsgBox "Error - Unequal Rows or Columns", vbInformation
End If

' 执行Workbook B的滚动操作
ws1.Activate
Application.Goto ws1.Cells(ws1.Parent.Windows(1).ScrollRow, ws1.Parent.Windows(1).ScrollColumn), 1

' 恢复Workbook A的初始状态
wsA.Activate
wbA.Windows(1).ScrollRow = scrollRowA
wbA.Windows(1).ScrollColumn = scrollColA

MsgBox "Done"

关键修改说明 #

• 记录并恢复初始状态：通过变量保存Workbook A的激活工作表、滚动行和列，操作完成后重置这些参数，避免A的视图被修改。
• 明确对象引用：所有单元格、工作表操作都指定父对象，防止因激活状态变化导致的引用错误。
• 修正原代码笔误：修复了LastRow2中工作表引用错误、FirtRow1拼写错误等问题。
• 若需要更隐蔽的操作，可在宏开头添加Application.ScreenUpdating = False，结尾添加Application.ScreenUpdating = True，屏蔽操作过程中的界面切换。

内容的提问来源于stack exchange，提问作者Jobe