显示工作表类的所有数据失败

Question

我试图让用户打开两个文件"UID地图"one_answers";Vl_lookup"并将其内容复制到另一个名为"SNS_Zone Stock Report - east"的文件中;其中一个文件可能包含或不包含筛选过的数据，但对其应用了筛选

Sub copy_data()
Dim uid_map As String
MsgBox "Select UID Map workbook"
uid_map = Application.GetOpenFilename()
Workbooks.Open uid_map
Workbooks("UID map.xlsx").Activate
Workbooks("UID Map.xlsx").Worksheets("Corrected prevail map").ShowAllData 'error:showalldata of worksheet  class failed 
Workbooks("UID map.xlsx").Worksheets("Corrected prevail map").Range("A:I").Copy _
Workbooks("SNS_Zone Stock Report - East.xlsm").Worksheets("Map").Range("A1") 'subscript out of range
Dim vlookup_formula As String
MsgBox "Select Vl_formula workbook"
vlookup_formula = Application.GetOpenFilename()
Workbooks.Open vlookup_formula
Workbooks("Vl_formula.xlsx").Worksheets("Vl_formula").Range("A:I").Copy ( _
Workbooks("SNS_Zone Stock Report - East.xlsm").Worksheets("Vl_formula").Range("A1"))

End Sub

Answer 1

导入列

• 不是测试。

代码

Option Explicit
Sub copy_data()

' Destination Workbook
Dim dwb As Workbook
' If this is the workbook containing this code, then use...
'Set dwb = ThisWorkbook
' ... instead of:
Set dwb = Workbooks("SNS_Zone Stock Report - East.xlsm")

' First Source Workbook
MsgBox "Select UID Map workbook"
Application.ScreenUpdating = False
Dim uid_map As String: uid_map = Application.GetOpenFilename()
Dim swb As Workbook: Set swb = Workbooks.Open(uid_map)
With swb.Worksheets("Corrected prevail map")
If .AutoFilterMode Then
.ShowAllData
End If
.Range("A:I").Copy dwb.Worksheets("Map").Range("A1")
End With
swb.Close SaveChanges:=False
Application.ScreenUpdating = True

' Second Source Workbook
MsgBox "Select Vl_formula workbook"
Application.ScreenUpdating = False
Dim vlookup_formula As String
vlookup_formula = Application.GetOpenFilename()
Set swb = Workbooks.Open(vlookup_formula)
With swb.Worksheets("Vl_formula")
.Range("A:I").Copy dwb.Worksheets("Vl_formula").Range("A1")
End With
swb.Close SaveChanges:=False
Application.ScreenUpdating = True

' Inform.
MsgBox "Data imported.", vbInformation, "Success"
End Sub