צפה בוידאו - כיצד לפצל כל גיליון Excel לקבצים נפרדים
אם יש לך חוברת עבודה של Excel עם דפי עבודה רבים, אין דרך קלה לפצל כל אחד מהגיליונות לקבצי Excel נפרדים ולשמור בנפרד.
זה עשוי להיות נחוץ בעת גיליונות של חודשים או אזורים או מוצרים/לקוחות שונים וברצונך לקבל במהירות חוברת עבודה נפרדת לכל גיליון (כקובץ אקסל או כמסמכי PDF).
אמנם יש דרך ידנית לפצל גיליונות לחוברות עבודה נפרדות ולאחר מכן לשמור אותם, אך היא לא יעילה ומועדת לשגיאות.
במדריך זה, אני אתן לך קוד VBA פשוט שבו תוכל להשתמש כדי לפצל במהירות (תוך כמה שניות) את כל גליונות העבודה לקבצים נפרדים משלהם ולאחר מכן לשמור אותם בכל תיקיה שצוין.
פיצול כל גליון עבודה לקובץ Excel נפרד
נניח שיש לך חוברת עבודה כפי שמוצג להלן ובה יש לך דף עבודה לכל חודש.
כדי לפצל גיליונות אלה לקובץ Excel נפרד, תוכל להשתמש בקוד VBA להלן:
'קוד נוצר על ידי Sumit Bansal מ- trumpexcel.com Sub SplitEachWorksheet () Dim FPath As String FPath = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False for all ws In ThisWorkbook.Sheets ws.Copy Application.ActiveWorkbook.SaveAs שם קובץ: = FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False הבא Application.DisplayAlerts = True Application.ScreenUpdating = Sub End True
ישנם מספר דברים שעליך לוודא לפני השימוש בקוד VBA לעיל:
- צור תיקייה שבה ברצונך לקבל את כל הקבצים שהתקבלו.
- שמור את קובץ ה- Excel הראשי (הכולל את כל גליונות העבודה שאתה רוצה כקבצים נפרדים) בתיקיה זו.
לאחר שתעשה זאת, תוכל להכניס את קוד ה- VBA לעיל בקובץ ולהריץ את הקוד.
הקוד לעיל נכתב באופן שבו הוא אוסף את מיקום התיקיה באמצעות נתיב הקובץ (בו מופעל הקוד). זו הסיבה שחשוב קודם כל לשמור את הקובץ בתיקייה ולאחר מכן להשתמש בקוד זה.
כיצד פועל קוד VBA הקוד לעיל משתמש בלולאה פשוטה של Next Next שעוברת בכל גליון עבודה, יוצרת עותק של גליון העבודה בחוברת עבודה של Excel ולאחר מכן שומרת את חוברת העבודה הזו של Excel בתיקיה שצוינה (שהיא זהה שיש לה את הקובץ הראשי עם כל הפריטים גיליונות).
להלן השלבים למיקום קוד VBA זה בחוברת העבודה של Excel (אלה יהיו זהים לכל שאר השיטות המוצגות במדריך זה):
איפה לשים את הקוד הזה?
להלן השלבים למיקום הקוד בעורך Visual Basic שבו ניתן לבצע אותו:
- לחץ על הכרטיסייה מפתחים.
- בקבוצת הקוד, לחץ על האפשרות Visual Basic. פעולה זו תפתח את עורך VB. [אתה יכול גם להשתמש בקיצור המקשים - ALT + F11]
- בעורך VB, לחץ באמצעות לחצן העכבר הימני על כל אחד מהאובייקטים של חוברת העבודה שאתה עובד עליהם.
- העבר את הסמן מעל האפשרות הוספה
- לחץ על מודול. זה יכניס מודול חדש
- לחץ פעמיים על אובייקט המודול. זה יפתח את חלון הקוד של המודול
- העתק את קוד ה- VBA שסופק למעלה והדבק אותו בחלון קוד המודול.
- בחר כל שורה בקוד ולחץ על כפתור ההפעלה הירוק בסרגל הכלים להפעלת קוד המאקרו VBA.
השלבים שלעיל היו מפצלים באופן מיידי את גליונות העבודה לקבצי Excel נפרדים ושומרים אותם. זה לוקח רק שנייה אם יש לך מספר דפי עבודה פחות. במקרה שיש לך הרבה, זה עלול לקחת זמן.
השם של כל קובץ שנשמר זהה לזה של שם הגיליון שהיה לו בקובץ הראשי.
מכיוון שהצבת קוד VBA בחוברת העבודה של Excel, עליך לשמור זאת בפורמט .XLSM (שהוא הפורמט המאופשר למאקרו). זה יבטיח שהמאקרו יישמר ויפעל כאשר תפתח את הקובץ הבא.
שים לב שהשתמשתי בשורות Application.ScreenUpdating = שקר ו Application.DisplayAlerts = שקר בקוד כך שהכל יקרה במגמה האחורית ולא יראה דברים קורים על המסך שלך. ברגע שהקוד פועל ומפצל את הסדינים ושומר אותם, אנו מחזירים אותם ל- TRUE.
כשיטה מומלצת, מומלץ ליצור עותק גיבוי של הקובץ הראשי (הכולל את הגיליונות שברצונך לפצל). זה יבטיח שלא תאבד את הנתונים שלך במקרה שמשהו ישתבש או אם Excel יחליט להיות איטי או לקרוס.
חלוק כל גליון עבודה ושמור כמסמכי PDF נפרדים
אם ברצונך לפצל את דפי העבודה ולשמור אותם כקובצי PDF במקום קובצי Excel, תוכל להשתמש בקוד שלהלן:
'קוד נוצר על ידי Sumit Bansal מ- trumpexcel.com Sub SplitEachWorksheet () Dim FPath As String FPath = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False for each ws In ThisWorkbook.Sheets ws.Copy Application.ActiveSheet.ExportAsFixed סוג: = xlTypePDF, שם קובץ: = FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False הבא Application.DisplayAlerts = True Application.ScreenUpdating = Sub End True
דברים שעליך לוודא לפני השימוש בקוד זה:
- צור תיקיה שבה ברצונך לקבל את כל הקבצים שהתקבלו.
- שמור את קובץ ה- Excel הראשי (הכולל את כל גליונות העבודה שאתה רוצה כקבצים נפרדים) בתיקיה זו.
הקוד לעיל פיצל כל גיליון בקובץ האקסל ושמור אותו כקובץ PDF באותה תיקייה שבה שמרת את קובץ ה- Excel הראשי.
חלוק רק את גליונות העבודה המכילים מילה/ביטוי לקבצי Excel נפרדים
במקרה שיש לך הרבה גיליונות בחוברת עבודה ואתה רק רוצה לפצל רק את אותם גיליונות שיש בהם טקסט ספציפי, תוכל לעשות זאת גם כן.
לדוגמה, נניח שיש לך קובץ אקסל שבו אתה מנתח מספר שנים ולכל גיליון בקובץ יש את מספר השנה כקידומת. משהו כפי שמוצג להלן:
כעת, נניח שאתה רוצה לפצל את כל הסדינים לשנים 2020-2022 ולשמור אותם כקובצי Excel נפרדים. לשם כך, עליך לבדוק איכשהו את השם של כל גליון עבודה ויש לפצל ולשמור את אותם גיליונות הכוללים את המספר 2021-2022, ואת השאר ישאר ללא פגע.
ניתן לעשות זאת באמצעות קוד המאקרו VBA הבא:
'קוד נוצר על ידי Sumit Bansal מ- trumpexcel.com Sub SplitEachWorksheet () Dim FPath כמחרוזת Dim TextttoFind As String TexttoFind = "2020" FPath = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False עבור כל ספר זה. Sheets If InStr (1, ws.Name, TexttoFind, vbBinaryCompare) 0 ואז ws.Copy Application.ActiveWorkbook.SaveAs שם קובץ: = FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False End If Next Application.DisplayAlerts = True Application.ScreenUpdating = Sub End True
בקוד לעיל, השתמשתי במשתנה TexttoFind, שהוקצה ל "2020" בתחילת הדרך.
לאחר מכן קוד VBA משתמש בלולאה For Next ב- VBA כדי לעבור על כל גליון עבודה ולאחר מכן לבדוק את השם של כל פונקציית INSTR של גליון העבודה. פונקציה זו בודקת אם יש שם המילה2021-2022 בשם גליון העבודה או לא. אם כן, הוא יחזיר מספר מיקום שבו הוא מוצא את הטקסט הזה (שהוא 2021-2022 במקרה זה).
ואם הוא לא מוצא את הטקסט אותו אנו מחפשים, הוא מחזיר 0.
זה משמש עם התנאי IF Then. אז אם שם גיליון מכיל בתוכו את מחרוזת הטקסט 2021-2022, הוא יפוצל וישמור כקובץ נפרד. ואם אין לו מחרוזת טקסט זו, תנאי IF לא יתקיים ושום דבר לא יקרה.
