|
有2张相同结构的工作簿,其中一张工作簿(取数源)的数据是用公式取的其他多个表的数据,现在想在汇总表工作簿中取“取数源”工作簿的内容,但2个工作簿除要填写的单元格以外都被锁定了,要取数值又不能一个一个粘贴,我是新手,写了段代码,但运行太慢了,还请高手帮忙修改或者写一段,非常感谢,祝新年快乐,附上我的代码
Sub 汇总华东()
Dim arr1, arr2, arr3
Dim wb As Workbook
Dim i As Integer, j As Integer
Application.ScreenUpdating = False
arr = Array("h", "j", "l", "n", "p", "t", "v", "x", "z", "ab", "ad", "af", "ah")
For j = 0 To 12
For i = 18 To 161 Step 13
Set wb = GetObject("E:\取数源.xls")
arr1 = wb.Sheets("华东").Range(arr(j) & i)
arr2 = wb.Sheets("华东").Range(arr(j) & i + 2).Resize(4, 1)
arr3 = wb.Sheets("华东").Range(arr(j) & i + 7)
With Sheets("华东")
.Range(arr(j) & i) = arr1
.Range(arr(j) & i + 2).Resize(4, 1) = arr2
.Range(arr(j) & i + 7) = arr3
End With
Next i
Next j
Windows(wb.Name).Visible = True
wb.Close False
Set wb = Nothing
Application.ScreenUpdating = True
End Sub
本帖最后由 rxj_0414 于 2012-1-16 21:35 编辑
因为表格中数据已被保护,所以不必担心会写错数据,只需加句错误处理代码即可。
- Sub 汇总华东rxj11()
- Dim arrRSC
- Dim wb As Workbook
- Application.ScreenUpdating = False
- Set wb = GetObject("E:\取数源.xls")
- arrRSC = wb.Sheets("华东").Range("A1:AK173").Value
- On Error Resume Next
- Sheets("华东").Range("A1:AK173") = arrRSC
- wb.Close False
- Set wb = Nothing
- Application.ScreenUpdating = True
- End Sub
复制代码
|
|