|
使用VBA套用其他表格数据,为何一些可以套过来一些不行呢?
编程大致如下表格《套用表格及数据有些888代替》,数值无误,可以复制单元格后去另外一个表格搜索得到
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count <> 1 Then Exit Sub
If Not (Target.Column = 4 Or Target.Column = 5 Or Target.Column = 6 Or Target.Column = 7 Or Target.Column = 8 Or Target.Column = 9) Or Len(Target) = 0 Then Exit Sub
Dim wb As Workbook, ws As Worksheet, i As Integer
Dim wb2 As Workbook
If Target.Column = 4 Then
For i = 1 To Workbooks.Count
If Workbooks(i).Name Like "88888.*" Then Set wb = Workbooks(i): Exit For
Next
If wb Is Nothing Then
Dim path As String
path = ThisWorkbook.path & "/8888888.xlsx"
If Len(Dir(path)) > 0 Then
Set wb2 = ThisWorkbook
'Application.ScreenUpdating = False
Set wb = Workbooks.Open(path)
'wb2.Activate
'Application.ScreenUpdating = True
wb2.Activate
'MsgBox wb2.Name
Else: MsgBox "未找到工作表"
End If
End If
If Not wb Is Nothing Then
For i = 1 To wb.Sheets.Count
If wb.Sheets(i).Name Like "Sheet1" Then
Set ws = wb.Sheets(i): Exit For
Else: MsgBox "找不到'Sheet1'"
End If
Next
End If
If Not ws Is Nothing Then
Dim r As Long
r = 4
ListBox_dy.Clear
Do Until Len(ws.Cells(r, 1)) = 0
If ws.Cells(r, 1) Like Target Then
ListBox_dy.AddItem "888 " & ws.Cells(r, 3)
ListBox_dy.AddItem "888 " & ws.Cells(r, 4)
ListBox_dy.AddItem "888 " & ws.Cells(r, 5)
ListBox_dy.AddItem "888 " & ws.Cells(r, 6)
ListBox_dy.AddItem "8888 " & ws.Cells(r, 7)
ListBox_dy.AddItem "8888 " & ws.Cells(r, 9)
ListBox_dy.Top = Target.Top
ListBox_dy.Left = Target.Left + Target.Width
ListBox_dy.Visible = True
Exit Do
End If
r = r + 1
Loop
End If |
|