האם קרה לכם שהייתם צריכים לאחד מספר גיליונות בקובץ אקסל ושאלתם את עצמכם איך אפשר לעשות זאת במהירות?

נכון – אם מדובר ב- 2-3 גיליונות, כנראה שגם אנחנו פשוט היינו עושים העתק-הדבק.

אבל מה עושים כאשר יש 60 גיליונות שצריך לאחד לגיליון אחד? במדריך זה נסביר לכם כיצד תעשו זאת במהירות ובקלות רבה על ידי שימוש בפקודת מאקרו (VBA), כפי שניתן לראות בהדגמה כאן:

מה זה מאקרו ואיך אני משתמש בזה?

כאמור, השיטה שנציג במדריך זה כוללת שימוש בפקודות מאקרו. פקודות מאקרו הן בעצם שורות קוד שמהוות חלק משפת התכנות שקיימת באקסל, אשר קוראים לה VBA או בשמה המלא – Visual Basic for Applications.

למרבה המזל, לא צריך להיות מתכנת כדי לדעת להשתמש בשפה הזו – אלא רק צריך לדעת איך להעתיק את הקוד שנצרף מיד, להדביק אותו במקום הנכון – ופשוט להריץ את הקוד ולראות את הקסם קורה לנגד עיניכם.

על מנת לחסוך לכם זמן רב, כתבנו מדריך שמסביר מה זה מאקרו וכיצד מריצים אותו, ותוכלו לקרוא אותו כאן. זה ממש פשוט, אנחנו מבטיחים 🙂

אוקיי, הבנתי מה זה מאקרו, איך מריצים את הקוד?

בקוד המצורף להלן, נבצע העתקה-הדבקה של כל הגיליונות באקסל, אל תוך גיליון חדש שנקרא לו “combine”.

שימו לב – פקודות מאקרו הן בלתי הפיכות ולפעמים עלולות להשתבש, ולא ניתן לחזור אחורה לאחר הרצת הקוד. לכן – לפני תחילת עבודה עם הקוד המצורף, אנו ממליצים בחום לגבות את כל עבודתכם. זה יחסוך לכם עוגמת נפש רבה. 

כדי להריץ את הקוד, נבצע את השלבים הבאים:

1. 1. ניכנס לקובץ שבו מופיעים כל הגיליונות שאנו רוצים לאחד
   2. ניצור גיליון חדש, אשר נתן לו את השם combine (באותיות קטנות)
   3. נלחץ ALT+F11 לצורך פתיחת ה- Visual Basic Editor
   4. נלחץ Insert – Module
   5. נדביק בתיבה הלבנה שנפתחה את הקוד הבא (לגולשים מהטלפון הנייד – יש לגלול שמאלה בשורות הבאות כדי לראות את מקטע הקוד המלא):

Sub Append_sheets_all()
Dim LastRow, LastRowWs As Long
Dim ws_combine_name As String
Dim ws As Worksheet

LastRow = 1
'You can change the name of the combine sheet in the next column:
ws_combine_name = "combine"

For Each ws In ThisWorkbook.Worksheets
If ws.Name <> ws_combine_name Then
With Worksheets(ws.Name)
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
LastRowWs = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Else
LastRowWs = 1
End If
End With
ws.Activate
Range(Cells(1, 1), Cells(LastRowWs, 1)).EntireRow.Copy
Worksheets(ws_combine_name).Cells(LastRow, 1).PasteSpecial xlPasteValuesAndNumberFormats
Worksheets(ws_combine_name).Cells(LastRow, 1).PasteSpecial xlPasteFormats

With Worksheets(ws_combine_name)
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
If LastRowWs = 1 Then
LastRow = 2 + .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Else
LastRow = 1 + .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
End If
Else
LastRow = LastRow + 1
End If
End With
End If
Next ws
Worksheets(ws_combine_name).Activate
Range("a1").Select
Application.CutCopyMode = False
End Sub

1. כל שנותר עתה הוא להריץ את הקוד על ידי לחיצה על מקש F5 או על ידי לחיצה על המשולש הירוק בתפריט. התוצאה שנקבל כתוצאה מאיחוד כל הגיליונות תוצג בגיליון combine.

דגשים

1. הקוד הזה מאחד את הגיליונות בצורה “מטומטמת” – זאת אומרת, הוא לא משמיט שורות כותרת שחוזרות על עצמן בגיליונות השונים. לצורך ביצוע איחודים יותר מורכבים, אנו מציעים להשתמש בשיטות אחרות, למשל באמצעות שימוש ב- Power Query (נרחיב על כך במאמר בקרוב).
2. על מנת למנוע סיבוכים, הקוד מעתיק ומדביק את הנתונים מהגיליונות בתור ערכים – כך שהנוסחאות לא נשמרות.
3. אם ברצונכם לקרוא לגיליון האיחוד בשם אחר מאשר combine, שנו את הערך המופיע בשורה 8 בקוד מ- “combine” לכל דבר אחר, לדוגמא: “all_sheets” או “new_sheet”
4. הקוד שומר על העיצוב של הגיליונות מהם העתקנו את התאים. במידה ואינכם מעוניינים בשימור העיצוב, השמיטו את שורה 28 (אשר כתוב בה xlPasteFormats) במקטע הקוד. ניתן להשמיט את השורה על ידי מחיקתה, או הוספת גרש בודד לפני.
5. הקוד מחפש בכל גיליון שהוא מעתיק ממנו את השורה האחרונה בה מופיע טקסט מסוים. אם לאחר שורה זו יש עיצוב מסוים שאינו מכיל נתונים (לדוגמא תא ריק שצבעתם בצהוב) – השורות הללו לא יועתקו.