我有 500 个包含数据的 Excel 文件。我会将所有这些数据合并到一个文件中。
实现此目标的任务列表:
- 我想循环遍历文件夹中的所有文件
- 打开文件,
- 复制此范围“B3:I102”
- 将其粘贴到活动工作簿的第一张工作表中
- 重复但在下面粘贴新数据
我已经完成了任务 1-4,但我需要任务 5 的帮助,最后一点 - 将数据粘贴到现有数据下并使其动态。我在代码中用“####”突出显示了这一点。
这是我从其他人的问题中整理出来的代码:)
关于如何执行此操作有什么建议吗?
Sub LoopThroughFiles()
Dim MyObj As Object,
MySource As Object,
file As Variant
Dim wbThis As Workbook 'workbook where the data is to be pasted, aka Master file
Dim wbTarget As Workbook 'workbook from where the data is to be copied from, aka Overnights file
Dim LastRow As Long
Dim sht1 As Worksheet
Dim sht2 As Worksheet
'set to the current active workbook (the source book, the Master!)
Set wbThis = ActiveWorkbook
Set sht1 = wbThis.Sheets("Sheet1")
Folder = "\\dne\ldc\research-dept\3 CEEMEA\15. EMB\Turkey\TLC Overnight & Weekly Reports\weekly (majeed)\"
Fname = Dir(Folder)
While (Fname <> "")
Set wbTarget = Workbooks.Open(Filename:=Folder & Fname)
wbTarget.Activate
Range("b3:i102").Copy
wbThis.Activate
'################################
'NEED HELP HERE. I GET A ERROR HERE. NEEDS TO BE MORE DYNAMIC.
sht1.Range("b1:i100").PasteSpecial
Fname = Dir
'close the overnight's file
wbTarget.Close
Wend
End Sub
我认为使用变体比复制方法有用。
Sub LoopThroughFiles()
Dim MyObj As Object, MySource As Object
file As Variant
Dim wbThis As Workbook 'workbook where the data is to be pasted, aka Master file
Dim wbTarget As Workbook 'workbook from where the data is to be copied from, aka Overnights file
Dim LastRow As Long
Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim vDB As Variant
'set to the current active workbook (the source book, the Master!)
Set wbThis = ActiveWorkbook
Set sht1 = wbThis.Sheets("Sheet1")
Folder = "\\dne\ldc\research-dept\3 CEEMEA\15. EMB\Turkey\TLC Overnight & Weekly Reports\weekly (majeed)\"
Fname = Dir(Folder)
While (Fname <> "")
Set wbTarget = Workbooks.Open(Filename:=Folder & Fname)
vDB = wbTarget.Sheets(1).Range("b3:i102")
'################################
'NEED HELP HERE. I GET A ERROR HERE. NEEDS TO BE MORE DYNAMIC.
sht1.Range("b" & Rows.Count).End(xlUp)(2).Resize(UBound(vDB, 1), UBound(vDB, 2)) = vDB
Fname = Dir
'close the overnight's file
wbTarget.Close
Wend
End Sub
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)