Excel VBA常见问题:从文件夹内汇入其他工作表表格怎么样修改宏?

2015-04-14 12:28 阅读 102 次 评论关闭

Excel VBA常见问题:从文件夹内汇入其他工作表表格。汇入范围为第五行、第L列。如汇入范围改为第三行、第R列。怎样修改此宏?

Public Sub Feed_in2()
Dim Row_dn, Row_dn1, i, j, k, m As Integer
Dim Path1, Str1 As String
Dim wb As Workbook
Row_dn = [B65536].End(xlUp).Row
Path1 = Application.ActiveWorkbook.Path
Str1 = ActiveWorkbook.Name
k = 5

With Application
.EnableEvents = False
.ScreenUpdating = False
If Row_dn >= 5 Then
Range("B5:L" & Row_dn).ClearContents
End If
With .FileSearch
.NewSearch
.LookIn = Path1
.FileType = msoFileTypeExcelWorkbooks
If .Execute <= 1 Then
MsgBox "files no found": Exit Sub
Else
For m = 1 To .FoundFiles.Count
Str2 = Split(.FoundFiles(m), "\")
n1 = UBound(Str2)
Str2 = Str2(n1)
If Str2 <> Str1 Then
Set wb = Workbooks.Open((Path1 & "\" & Str2), True, True)
Row_dn1 = wb.Sheets(1).[B65536].End(xlUp).Row
For i = 5 To Row_dn1
For j = 2 To 12
Workbooks(Str1).Sheets(1).Cells(k, j) _
= wb.Sheets(1).Cells(i, j)
Next j
k = k + 1
Next i
wb.Close False
Set wb = Nothing
End If
Next m
End If
End With
.EnableEvents = True
End With
End Sub

解答:

除了B65536中的5,其余5都改成3;将Range("B5:L" & Row_dn)改成Range("B5:R" & Row_dn);将For j = 2 To 12改成For j = 2 To 17。

版权声明:本文著作权归原作者所有,欢迎分享本文,谢谢支持!
转载请注明:Excel VBA常见问题:从文件夹内汇入其他工作表表格怎么样修改宏? | 猎微网

评论已关闭!