前景提要
上一節呢,我們學習了針對工作簿欄位內容不同,順序也不同的情況下的資料彙總的操作,在那
節發表
之後呢,也有一些小夥伴私信我說,上一節的程式碼還存在著一些問題,比方說
1。
雖然我們成功的彙總了資料,但是僅僅是針對一個工作簿有一個工作表的情況下彙總的,如果一個工作簿有多個工作表,這個程式碼還需要在擴充套件下
2。
雖然成功的彙總了資料,但是很明顯,資料是彙總了,但是一眼看下去,並不知道資料是哪裡來的,後期校對資料的話,也非常的頭疼。
仔細想想,大家說的還非常有道理的,那麼針對上面的兩個問題呢,我們今天就來繼續完善下
最終實現的效果是:
1。
增加針對多工作表的彙總
2。
在彙總的資料中,增加資料來源,
方便後期的校對
場景說明
OK
,既然已經有了目標了,那就很好辦了
直接看看怎麼實現
第二點,增加資料來源非常簡單,我們在最終寫入資料的時候,我們在增加兩列,分別寫入工作簿的名稱和工作表的名稱即可。
第一點,針對多個工作表的資料彙總,那麼就是要遍歷迴圈工作表了。
這個是我們後面第
7
章的重點。
先來說下,工作表的遍歷,其實和工作簿的遍歷一樣,都是利用
for each
迴圈就可以實現了。
非常的簡單
那麼,直接上程式碼吧。
程式碼區
Sub test24()
Application。DisplayAlerts = False
Dim sth As Worksheet, sth1 As Worksheet, rng1 As Range, rng As Range, sbook As Workbook, sb As Workbook, arr, arr1, arrt()
Set sbook = ThisWorkbook
pathn = ThisWorkbook。Path
Set sth1 = ActiveSheet
Set rng1 = Application。InputBox(“請選擇表頭區域”, “表頭區域的確認”, , , , , , 8)
arr1 = rng1
len1 = UBound(arr1, 2)
f = Dir(pathn & “\”)
Do While f <> “”
l1 = Cells(Rows。Count, 1)。End(xlUp)。Row
If f <> “5-25。xlsm” Then
For Each sb In Workbooks
If sb。Name = f Then
GoTo line
End If
Next sb
Workbooks。Open (pathn & “\” & f)
For Each sth In ActiveWorkbook。Worksheets
sth。Activate
l = Cells(1, 1)。End(xlDown)。Row
With ActiveSheet。UsedRange
arr = 。Rows(1)
l2 = UBound(arr, 2)
Set rng = 。Find(What:=“合計”, LookIn:=xlValues, _
LookAt:=xlWhole, SearchDirection:=xlPrevious)
rngRow = rng。Row
End With
If l > rngRow Then
For i = 1 To l2
On Error Resume Next
num = WorksheetFunction。Match(arr(1, i), arr1, 0)
If Err。Number = 0 Then
ReDim Preserve arrt(1 To rngRow - 2, 1 To len1)
For j = 1 To rngRow - 2
arrt(j, num) = Cells(j + 1, num)
Next j
End If
Next i
Else
For i = 1 To l2
On Error Resume Next
num = WorksheetFunction。Match(arr(1, i), arr1, 0)
If Err。Number = 0 Then
ReDim Preserve arrt(1 To l - 1, 1 To len1)
For j = 1 To l - 1
arrt(j, num) = Cells(j + 1, i)
Next j
End If
Next i
End If
l1 = sth1。Cells(Rows。Count, 1)。End(xlUp)。Row
sth1。Cells(l1 + 1, 1)。Resize(UBound(arrt), 1) = ActiveWorkbook。Name
sth1。Cells(l1 + 1, 2)。Resize(UBound(arrt), 1) = ActiveSheet。Name
sth1。Cells(l1 + 1, 3)。Resize(UBound(arrt), len1) = arrt
Erase arrt
Next sth
ActiveWorkbook。Close True
End If
line:
f = Dir()
Application。DisplayAlerts = True
Loop
End Sub
程式碼又增加了一些,是不是
很
方呢?
其實不用方,因為我講解的思維都是迴圈漸進,由易而難的,所以本節的程式碼雖然長,但是基本上是在上一節的程式碼的接觸上新增了一點點程式碼而已。並不難。
掌握方法才是最重要的
只有掌握了
方法,才可以針對現有的程式碼進行改動,你才知道要改動那裡,增加哪裡。
其實今天就增加了上面這麼短短的兩部分的程式碼,並不負責,先來看看最終的效果。
程式碼解析
這樣的效果應該是非常的完美了。
有了工作簿和工作表名稱,這樣就有了資料來源,方便後期的更改和校對
同時也實現了針對多個工作表的處理。
因為大致的框架邏輯都是我們之前的框架,所以,這裡我們就直接針對今天新增的部分進行講解。
首先,我們要求非常的明確,需要針對多個工作表進行迴圈
工作表是什麼呢?工作表和工作簿又有什麼區別呢?
容我偷個懶,放
個之前
章節的原圖
換成程式碼就是
工作簿:
workbook
工作表:
worksheet
想要遍歷工作表呢。依然是我們熟悉的
for each
迴圈
for
each
sth
in
ActiveWorkbook。Worksheets
注意:
這裡的
ActiveWorkbook。Worksheets
,後面一定要帶
‘s’
,他代表的就是所有的工作表的集合。
遍歷每個工作表,去尋找資料的方法,和上一節就完全一樣了,程式碼都沒有做任何的改動。
唯一不同的就是最後寫入的時候,要增加工作表名稱
這裡我們不打亂陣列的佈局,直接在最後寫入
其實也可以在上面的陣列中寫入,不過這樣處理陣列就比較的麻煩
這裡我們保持程式碼的連貫性,所以選擇在最後直接一次性寫入,更加的方便移動