|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
本帖最后由 山风 于 2013-1-9 15:36 编辑
求助:N个工作表指定列提取汇总.rar
(103.46 KB, 下载次数: 140)
Private Sub CommandButton1_Click() '提取其他工作簿数据
Application.ScreenUpdating = False
n = ThisWorkbook.Path & "\分表\"
m = Dir(n & "*.xls", vbNormal) 'Dir读取文件名
Do While Len(m) > 0
a = Range("A65536").End(xlUp).Row
Set Wb = GetObject(n & m)
With Wb.Sheets("Index Constituents Data")
j = .UsedRange.Rows.Count '分表行数
Range("A" & a + 1).Resize(j - 1, 1) = .Range("A2:A" & j).Value
Range("B" & a + 1).Resize(j - 1, 2) = .Range("E2:F" & j).Value
Range("D" & a + 1).Resize(j - 1, 1) = .Range("I2:I" & j).Value
Wb.Close False
End With
Set Wb = Nothing
m = Dir()
Loop
b = Range("A65536").End(xlUp).Row
Range("B2:B" & b).NumberFormatLocal = "000000"
MsgBox "导入完毕"
Application.ScreenUpdating = True
End Sub
|
|