当我按下一个按钮时,我在主工作表上有以下代码,它会通过我想要的文件夹并打开和关闭应该从中提取数据的工作表。
这是我用于从母版上的按钮打开和关闭文件的代码。我需要帮助为以下代码中的空间###CODE GOES HERE 编写代码。我一直在拔头发。
Public Sub test()
Dim wbk As Workbook
Dim Filename As String
Dim Path As String
Dim Wb1 As Workbook, wb2 As Workbook
Path = "\\ttsnas02\user_mdocs$\tdf8273\Documents\Rob\External supplier timesheet\CSV Supplier Main\Inbox folder\" 'CHANGE PATH
Filename = Dir(Path & "*.xl??")
Do While Len(Filename) > 0 'IF NEXT FILE EXISTS THEN
Set wbk = Workbooks.Open(Path & Filename)
'
' ###CODE GOES HERE
'
wbk.Close True
Filename = Dir
Loop
End Sub
你能帮我写一个代码吗
'###CODE GOES HERE' 的此代码需要通过检查特定列中是否存在值来从打开的工作表行中获取数据。例如,如果L12中有数据,那么在开张表中,它会复制主表a2 c2和e2中的J8 J9和L12。
然后在起始页中,它检查 L13。如果有值,它将 J8 J9 和 L13 复制到 a3 c3 和 e3。
然后在打开的工作表中检查 L14 ...
直到 L20
然后关闭打开的工作簿并打开文件夹中的下一个工作簿。检查同一个表:如果L12中有数据。然后在开张表中,它将复制J8 J9和主表中的L12放在下一个空闲行中。
这是供应商时间表
这应该做你想做的。该代码用于 master 和打开索引为 1 的第一个工作表。如果需要,更改索引:
Public Sub test()
Dim wksMaster As Worksheet
Dim wks As Worksheet
Dim rng As Range
Dim i As Integer
Dim wkb As Workbook
Dim Filename As String
Dim Path As String
Dim Wb1 As Workbook, wb2 As Workbook
Path = "\\ttsnas02\user_mdocs$\tdf8273\Documents\Rob\External supplier timesheet\CSV Supplier Main\Inbox folder\" 'CHANGE PATH
Filename = Dir(Path & "*.xl??")
' bind the master worksheet to access it later on
' change index if needed
Set wksMaster = ActiveWorkbook.Worksheets(1) ' or ThisWorkbook
i = 2
Do While Len(Filename) > 0 'IF NEXT FILE EXISTS THEN
Set wkb = Workbooks.Open(Path & Filename)
' loop through range in worksheet with index 1 (the first)
' change index if needed
With wkb.Worksheets(1)
For Each rng In .Range("L12:L20")
' if there is a value in the cell
If rng <> vbNullString Then
wksMaster.Range("A" & i) = .Range("J8")
wksMaster.Range("C" & i) = .Range("J9")
wksMaster.Range("E" & i) = rng
' increment i
i = i + 1
End If
Next
End With
wkb.Close True
Filename = Dir
Loop
End Sub
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句