目前正在寫巨集,其功能為
將多個活頁簿的檔案(Ex:A/B/C/D)使用vlookup的方式整合到一個新的活頁簿中(Ex:甲)
所有的活頁簿都存放於同一個資料夾
希望巨集執行完後活頁簿甲如下圖
以下則是我寫的巨集程式
Sub allfolder()巨集執行完後結果會變成所有的行列都是同一個檔案的資訊(Ex:活頁簿A)
Dim tWB As Workbook
Set tWB = ThisWorkbook
Dim oWB As Object
Set oWB = ThisWorkbook.Sheets("output")
Dim folderpath As String
folderpath = ThisWorkbook.Path
Dim fileextension As String
fileextension = ".xlsm"
Dim filename As Variant
filename = Dir(folderpath & "\使用量*" & fileextension)
Application.ScreenUpdating = False
Do While filename <> " " '還需要修正
Dim targetbook As Workbook
Set targetbook = _
Workbooks.Open(folderpath & "\" & filename)
'針對開啟檔案要做的事情
'1.互V
oWB.Activate
Set currentcell = Range("A1")
For a = 1 To 200
For b = 1 To 50
Cells(currentcell.Column + a, 2+b) = "=VLOOKUP(RC1,[" & filename & "]InvData!C1:C50,button!R2C4,0)"
Next b
Next a
targetbook.Close
filename = Dir()
Loop
Application.ScreenUpdating = True
MsgBox "Done"
End Sub
猜想是迴圈的錯誤但不知該如何修改
請各位大大指點或是有無更好的做法?
謝謝各位QAQ