كيفية تقسيم مصنف لفصل ملفات Excel في Excel؟

قد تحتاج إلى تقسيم مصنف كبير لفصل ملفات Excel مع حفظ كل ورقة عمل من المصنف كملف Excel فردي. على سبيل المثال ، يمكنك تقسيم مصنف إلى عدة ملفات Excel فردية ثم تسليم كل ملف إلى شخص مختلف للتعامل معه. من خلال القيام بذلك ، يمكنك جعل بعض الأشخاص يتعاملون مع بيانات محددة ، والحفاظ على أمان بياناتك. ستقدم هذه المقالة طرقًا لتقسيم مصنف كبير لفصل ملفات Excel استنادًا إلى كل ورقة عمل.

قم بتقسيم مصنف لفصل ملفات Excel عن طريق النسخ واللصق
قم بتقسيم مصنف لفصل ملفات Excel باستخدام ميزة النقل أو النسخ
قم بتقسيم مصنف لفصل ملفات Excel برمز VBA
قم بتقسيم مصنف لفصل ملفات Excel / PDF / CSV / TXT باستخدام Kutools for Excel بسهولة

قم بتقسيم مصنف لفصل ملفات Excel عن طريق النسخ واللصق

في المعتاد ، باستخدام نسخ القيادة و لصق يمكن للأمر حفظ مصنف كملف Excel منفصل يدويًا. أولاً ، حدد ورقة العمل بأكملها التي تريد حفظها كملف منفصل ، وقم بإنشاء مصنف جديد ، ثم قم بلصقه في المصنف الجديد ، في النهاية يحفظه.

هذه طريقة سهلة الاستخدام إذا كنت بحاجة إلى تقسيم أوراق عمل قليلة فقط كملفات منفصلة. ومع ذلك ، يجب أن يستغرق تقسيم العديد من أوراق العمل عن طريق النسخ واللصق يدويًا وقتًا طويلاً ومرهقًا.

قم بتقسيم مصنف لفصل ملفات Excel باستخدام ميزة النقل أو النسخ

ستقدم هذه الطريقة ميزة النقل أو النسخ لنقل الأوراق المحددة أو نسخها إلى مصنف جديد وحفظها كمصنف منفصل. الرجاء القيام بما يلي:

1. حدد الأوراق في شريط علامة تبويب الورقة ، وانقر بزر الماوس الأيمن ، وحدد نقل أو نسخ من قائمة السياق. انظر لقطة الشاشة:

ملاحظة: تحتجز CTRL المفتاح ، يمكنك تحديد عدة أوراق غير متجاورة بالنقر فوقها واحدة تلو الأخرى في شريط علامة تبويب الورقة ؛ تحتجز تغير المفتاح ، يمكنك تحديد عدة أوراق متجاورة بالنقر فوق الأول والأخير في شريط علامة تبويب الورقة.

2. في مربع الحوار نقل أو نسخ ، حدد (كتاب جديد) من للحجز القائمة المنسدلة ، تحقق من قم بإنشاء نسخة الخيار، وانقر فوق OK زر. انظر لقطة الشاشة:

3. الآن يتم نسخ جميع الأوراق المحددة إلى مصنف جديد. انقر قم بتقديم > توفير لحفظ المصنف الجديد.

قم بتقسيم مصنف بسرعة لفصل ملفات Excel / PDF / TXT / CSV في Excel

عادة يمكننا تقسيم مصنف إلى ملفات Excel فردية بامتداد نقل أو نسخ ميزة في Excel. لكن كوتولس ل إكسيل انقسام وركبوك يمكن أن تساعدك الأداة المساعدة في تقسيم مصنف بسهولة وحفظ كل ورقة عمل كملف PDF / TEXT / CSV منفصل أو مصنف في Excel. كامل الميزات نسخة تجريبية مجانية لمدة 30 يومًا!
إعلان تقسيم المصنف التفوق

كوتولس ل إكسيل - يتضمن أكثر من 300 أداة يدوية لبرنامج Excel. نسخة تجريبية مجانية كاملة الميزات لمدة 30 يومًا ، لا يلزم وجود بطاقة ائتمان! احصل عليه الآن

قم بتقسيم مصنف لفصل ملفات Excel برمز VBA

يمكن أن تساعدك التعليمات البرمجية VBA التالية في تقسيم أوراق عمل متعددة من المصنف الحالي بسرعة لفصل ملفات Excel ، يرجى القيام بما يلي:

1. قم بإنشاء مجلد جديد للمصنف الذي تريد تقسيمه ، لأن ملفات Excel المنقسمة ستبقى في نفس المجلد مثل هذا المصنف الرئيسي.

2. اضغط باستمرار على ALT + F11 في Excel ، ويفتح ملف ميكروسوفت فيسوال باسيك للتطبيقات نافذة.

3. انقر إدراج > وحدة، والصق التعليمة البرمجية التالية في نافذة الوحدة النمطية.

فبا: تقسيم مصنف إلى عدة مصنفات وحفظه في نفس المجلد

Sub Splitbook()
'Updateby20140612
Dim xPath As String
xPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In ThisWorkbook.Sheets
    xWs.Copy
    Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xlsx"
    Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

4. اضغط على F5 مفتاح لتشغيل هذا الرمز. ويتم تقسيم المصنف إلى ملفات Excel منفصلة في نفس المجلد باستخدام المصنف الأصلي. انظر لقطة الشاشة:

ملاحظة: إذا كانت إحدى الأوراق تحمل نفس الاسم مع المصنف ، فلن يعمل VBA هذا.

قم بتقسيم مصنف لفصل ملفات Excel / PDF / CSV / TXT باستخدام Kutools for Excel بسهولة

إذا كان لديك كوتولس ل إكسيل مثبتة انقسام وركبوك يمكن للأداة تقسيم أوراق عمل متعددة كملفات Excel منفصلة بشكل ملائم وسريع بنقرات قليلة فقط.

1. بعد التثبيت كوتولس ل إكسيل، اضغط هنا كوتولس بلس > انقسام وركبوك ، انظر لقطة الشاشة:

2. في انقسام وركبوك في مربع الحوار ، قم بالعمليات التالية:
(1) يتم فحص جميع أسماء أوراق العمل افتراضيًا. إذا كنت لا تريد تقسيم بعض أوراق العمل ، فيمكنك إلغاء تحديدها ؛
(2) تحقق من حفظ نوع الخيار.
(3) من حفظ كنوع القائمة المنسدلة ، اختر نوع ملف واحد تريد تقسيمه وحفظه.
(4) ثم انقر فوق الانقسام .

ملاحظة: إذا كنت تريد تجنب تقسيم أوراق العمل المخفية أو الفارغة ، فيمكنك التحقق من ملف تخطي أوراق العمل المخفية or تخطي أوراق العمل الفارغة مربع.

3. في مربع الحوار Browse For Folder ، يرجى تحديد مجلد وجهة لحفظ الملفات المنقسمة المنفصلة ، والنقر فوق OK .

الآن يتم حفظ أوراق العمل المحددة كمصنفات منفصلة جديدة. يتم تسمية كل مصنف جديد باسم ورقة العمل الأصلية. انظر لقطة الشاشة:

كوتولس ل إكسيل's انقسام وركبوك تسهل الأداة تقسيم المصنف النشط إلى ملفات Excel فردية (يحتوي ملف واحد على ورقة عمل واحدة) أو ملفات CSV أو ملفات TXT أو ملفات PDF حسب حاجتك. يمكنك التعيين لتخطي جميع أوراق العمل الفارغة والمخفية. احصل على نسخة تجريبية مجانية!

عرض توضيحي: قم بتقسيم أو حفظ كل ورقة عمل من مصنف واحد كملفات منفصلة من Excel / txt / csv / pdf

كوتولس ل إكسيل يتضمن أكثر من 300 أداة يدوية لبرنامج Excel ، يمكن تجربتها مجانًا دون قيود في غضون 30 يومًا. تنزيل وتجربة مجانية الآن!

أفضل أدوات إنتاجية المكتب

Kutools for Excel يحل معظم مشاكلك ويزيد إنتاجيتك بنسبة 80٪

إعادة استخدام: أدخل بسرعة الصيغ المعقدة والرسوم البيانية وأي شيء استخدمته من قبل ؛ تشفير الخلايا مع كلمة السر إنشاء قائمة بريدية وإرسال رسائل البريد الإلكتروني ...
سوبر فورميولا بار (بسهولة تحرير أسطر متعددة من النص والصيغة) ؛ تخطيط القراءة (قراءة وتحرير أعداد كبيرة من الخلايا بسهولة) ؛ لصق في النطاق المصفى...
دمج الخلايا / الصفوف / الأعمدة دون فقدان البيانات ؛ تقسيم محتوى الخلايا ؛ ادمج الصفوف / الأعمدة المكررة... منع تكرار الخلايا؛ قارن النطاقات...
حدد مكرر أو فريد صفوف حدد صفوف فارغة (جميع الخلايا فارغة) ؛ البحث الفائق والبحث الغامض في العديد من المصنفات. تحديد عشوائي ...
نسخة طبق الأصل خلايا متعددة بدون تغيير مرجع الصيغة ؛ إنشاء المراجع تلقائيًا إلى أوراق متعددة أدخل الرموز النقطية، مربعات الاختيار والمزيد ...
استخراج النص، إضافة نص ، إزالة حسب الموضع ، إزالة الفضاء؛ إنشاء وطباعة المجاميع الفرعية لترحيل الصفحات ؛ التحويل بين محتوى الخلايا والتعليقات...
سوبر تصفية (حفظ وتطبيق مخططات التصفية على أوراق أخرى) ؛ فرز متقدم حسب الشهر / الأسبوع / اليوم ، التكرار والمزيد ؛ مرشح خاص بواسطة bold، italic ...
اجمع بين المصنفات وأوراق العمل؛ دمج الجداول على أساس الأعمدة الرئيسية ؛ تقسيم البيانات إلى أوراق متعددة; تحويل دفعة xls و xlsx و PDF...
أكثر من 300 ميزة قوية. يدعم Office / Excel 2007-2019 و 365. يدعم جميع اللغات. سهولة النشر في مؤسستك أو مؤسستك. الميزات الكاملة نسخة تجريبية مجانية لمدة 30 يومًا. ضمان استرداد الأموال لمدة 60 يومًا.

اقرأ أكثر... تحميل مجاني ... شراء...

يجلب Office Tab الواجهة المبوبة إلى Office ، ويجعل عملك أسهل بكثير

تمكين التحرير والقراءة المبوبة في Word و Excel و PowerPointوالناشر والوصول و Visio والمشروع.
فتح وإنشاء مستندات متعددة في علامات تبويب جديدة من نفس النافذة ، بدلاً من النوافذ الجديدة.
يزيد إنتاجيتك بنسبة 50٪ ، ويقلل مئات النقرات بالماوس كل يوم!

اقرأ أكثر... تحميل مجاني ... شراء...

107 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

You are guest

or post as a guest, but your post won't be published automatically.

Loading comment... The comment will be refreshed after 00:00.

To post as a guest, your comment is unpublished.
Waqa · 22 days ago
xWs.Copy

showing error in this area
- Reply
To post as a guest, your comment is unpublished.
Jakub · 3 months ago
Hello, I just tried the split data function based on the first column (about 90 partners). Half of the worksheets are named correctly while the other just have number of the sheet eventhough there is name of the partner in the column. Any help, please?
- Reply
To post as a guest, your comment is unpublished.
Sudarshan · 9 months ago
i have a data of 5 worksheets. Every worksheet has a common column (Branch). Can i convert the data into different excel files based on respective branches
- Reply
- To post as a guest, your comment is unpublished.
  kellytte · 9 months ago
  
  Hi Sudarshan,
  Kutools for Excel has an amazing feature – Split Data, which can quickly split data from a range or a sheet to multiple sheets based on values in the specified column. And these sheets are saved in a new workbook.
  
  You can apply the Split Data feature to split each sheet based on the specified common column.
  After splitting, you can apply the Combine Worksheets feature, also provided by Kutools for Excel, and combine all same name worksheets into one workbook.
  
  Reply
To post as a guest, your comment is unpublished.
prateeksha · 1 years ago
excel which i want to split is having 3 spread sheet my requirement is to split the excel as per the sheet 1 and remaning to sheet to be contant when file split
- Reply
- To post as a guest, your comment is unpublished.
  kellytte · 1 years ago
  
  Hi prateeksha,
  In the fourth method on this webpage, the Split Workbook feature of Kutools for Excel is recommended, which will split every specified worksheet to individual PDF/CSV/TEXT/Workbooks.
  
  Reply
To post as a guest, your comment is unpublished.
Kevin · 1 years ago
Hey there,
I was wondering if we can use this macro to split the workbook into csv files
I've changed the VBA as below, replacing "xlsx" with "csv", but it doesn't work:
Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".csv"
Thanks for your help!
- Reply
- To post as a guest, your comment is unpublished.
  kellytte · 1 years ago
  
  Hi Kevin,
  It recommends trying the fourth method to solve your problem. Kutools for Excel can be freely trial for 30 days. And the Split Workbook feature of Kutools for Excel can solve the problem easily.
  
  Reply
To post as a guest, your comment is unpublished.
exceluser25 · 1 years ago
Very helpful code. Just had one question. How can I make these excel files read-only. I tried the below but it did not work.

Application.ActiveWorkbook.ChangeFileAccess Mode:=xlReadOnly
- Reply
To post as a guest, your comment is unpublished.
Denise Wood · 1 years ago
Hello,
Thank you so much for this code. I have used it a few additions successfully many times. However it will no longer work. It saves one file with the name Sheet 1 and it is blank. Any assistance would be greatly appreciated. Thank you!
- Reply
To post as a guest, your comment is unpublished.
Suraj · 1 years ago
how can i undo it?
I mean i split all the sheets, worked on it and now I want all the sheets just like before. PL help
- Reply
To post as a guest, your comment is unpublished.
Richie · 1 years ago
Wow. This is a very nice elaboration. you saved the day.
- Reply
To post as a guest, your comment is unpublished.
Gerdy · 1 years ago
If I wanted to split the workbook's collection worksheets by fives into workbooks, is that possible? Meaning, If I have 100 worksheets in a workbook, I want to automate using 5 worksheets at a time to make a workbook, which will end with 20 workbooks being made. How do you tweak the code to do this?
- Reply
To post as a guest, your comment is unpublished.
Gborka · 1 years ago
VBA works. If not - Probable errors root cause: This VBA cannot handle HIDDEN SHEETS or if one of the sheets has the same name with the workbook.
- Reply
- To post as a guest, your comment is unpublished.
  kellytte · 1 years ago
  
  Hi Gborka,
  You can use the Split Workbook feature of Kutools for Excel, which can export hidden worksheets, and export all worksheets no matter what sheet names are.
  
  Reply
To post as a guest, your comment is unpublished.
Влад · 2 years ago
Спасибо большое, всё работает.
- Reply
To post as a guest, your comment is unpublished.
Denise · 2 years ago
Will this VBA work for Mac? It works perfectly for my PC but my manager uses a Mac.
- Reply
- To post as a guest, your comment is unpublished.
  Salim · 2 years ago
  
  Yes!! I just used it on Mac. However you will need to change some thing. The back slash into front slash /. I have already done it. Copy paste it. It will work. Let me know if it doesn't.
  
  Sub Splitbook()
  'Updateby20140612
  Dim xPath As String
  xPath = Application.ActiveWorkbook.Path
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  For Each xWs In ThisWorkbook.Sheets
  xWs.Copy
  Application.ActiveWorkbook.SaveAs Filename:=xPath & "/" & xWs.Name & ".xlsx"
  Application.ActiveWorkbook.Close False
  Next
  Application.DisplayAlerts = True
  Application.ScreenUpdating = True
  End Sub
  
  Reply
To post as a guest, your comment is unpublished.
Ambarella · 2 years ago
The version vba code is not working right, I have a "macros" file where I keep all my macros and have it open when I'm going to use them, and I'm running this one in the file that I need to be splitted but it splits the macros file.
- Reply
To post as a guest, your comment is unpublished.
User1 · 2 years ago
Thank you!
- Reply
To post as a guest, your comment is unpublished.
M. Rameez · 2 years ago
I found this really help full. Thanks! :)
- Reply
To post as a guest, your comment is unpublished.
alfonso · 2 years ago
getting runtime error 1004
- Reply
- To post as a guest, your comment is unpublished.
  Christopher Lacey · 2 years ago
  
  I am as well, I have used this several times in the past but it is not working now
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Gborka · 1 years ago
  
  1004 error root cause: This VBA cannot handle hidden sheets or if one of the sheets has the same name with the workbook.
  
  Reply
To post as a guest, your comment is unpublished.
Web7 · 2 years ago
Does anyone know how to make this code work for a mac?
It is showing an error when trying to locate the document.
- Reply
- To post as a guest, your comment is unpublished.
  Dan Turney · 2 years ago
  
  Change "\" to "/" worked for me. Wow!
  
  Reply
To post as a guest, your comment is unpublished.
Ronnie · 2 years ago
Amazing script and instructions. Just saved me from splitting 30+Worksheets to separate files
- Reply
- To post as a guest, your comment is unpublished.
  Kim · 2 years ago
  
  Same! This worked perfectly for me. Saved me a lot of effort. Thank you!
  
  Reply
To post as a guest, your comment is unpublished.
judy.smith5919@gmail.com · 3 years ago
Please disregard my previous post. I have resolved my issue.
- Reply
To post as a guest, your comment is unpublished.
judy.smith5919@gmail.com · 3 years ago
I am using the split data into worksheets based on a column with 27 items in that column. The results are 54 worksheets 2 for each item. The only difference I can see is based on one of the columns in the range. For example:

Once the split has occured, John Doe has 2 worksheets, one named John Doe and another named Sheet32. Each sheet has information for John Doe, but based on a column "Status" (Billed, Open Order, Salesforce), the information is split. The named worksheet has Billed & Open Order information and the Sheet32 has Salesforce information.

Any idea what I am doing wrong?
- Reply
To post as a guest, your comment is unpublished.
Sreejith · 3 years ago
That really helped. Thank you.
- Reply
To post as a guest, your comment is unpublished.
Gabriel · 3 years ago
With the addition of code from comment below it works realy nice.
- Reply
To post as a guest, your comment is unpublished.
Paulo Corcino · 3 years ago
Sub Splitbook()
'Updateby20140612
Dim xPath As String
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim xWs As Worksheet

xPath = Application.ActiveWorkbook.Path
Set Sourcewb = ActiveWorkbook

With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With

' For Each xWs In ThisWorkbook.Sheets
For Each xWs In Sourcewb.Worksheets

If xWs.Visible = -1 Then
xWs.Copy
Set Destwb = ActiveWorkbook
If Destwb.Sheets(1).ProtectContents = False Then
With Destwb.Sheets(1).UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
End With
Application.CutCopyMode = False
End If
With Destwb
.SaveAs Filename:=xPath & "\" & xWs.Name & ".xlsx"
.Close False
End With
End If
Next

With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With

MsgBox "You can find the files in " & xPath

End Sub
- Reply
To post as a guest, your comment is unpublished.
Emmanuel · 3 years ago
Hi, can I add or modify the filename?
- Reply
To post as a guest, your comment is unpublished.
CMG · 3 years ago
Hi, can I get VBA to split a file with multiple tabs into separate files and save in their respective folders? i.e. If I have a report with 189 tabs each tab for different clients; can I separate those tabs and save each file in a folder specific to that client? The report varies with the number of tabs through out the month.
- Reply
To post as a guest, your comment is unpublished.
monica.arroyaveb@gmail.com · 3 years ago
Hi - Can I get VBA to split a file with multiple tabs into files with multiple tabs? i.e. if I have a file with 2 tabs and data in both tabs with a field called customer -100 customers-, can I get VBA to create one file per customer -100 files- with two tabs each?
- Reply
To post as a guest, your comment is unpublished.
pippa hatt · 3 years ago
help ive done this loads of times however today its saying an error on the Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xlsx" what am i doing wrong
- Reply
- To post as a guest, your comment is unpublished.
  LR · 3 years ago
  
  You may need to change ".xlsx" on line 9 to ".xls" instead
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Eben · 3 years ago
  
  It does that because of the spaces for indentation on lines 8 - 10. Also kept me busy a while to figure out why it's doing that now.
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Mrs Phillipa L Hatt · 2 years ago
  
  been using this for a while and now having a run time error 1004 then highlighting in yellow line 8 xWs.copy any ideas please
  
  Reply
To post as a guest, your comment is unpublished.
Kelley · 3 years ago
Hi - instead of splitting each worksheet into a new workbook, is there a way to split groups of worksheets into new workbooks? Ex. Sheet1, Sheet2, Sheet3 go into a workbook, and Sheet4, Sheet5, and Sheet6 go to a second workbook, rather than six new workbooks?
- Reply
To post as a guest, your comment is unpublished.
tony bones · 4 years ago
need a vba code to copy an entire workbook (all tabs) and have files saved based on cell range name by state?
- Reply
To post as a guest, your comment is unpublished.
imran ch · 4 years ago
thank you very much www.extendoffice.com . very simple and well define post
- Reply
To post as a guest, your comment is unpublished.
Chris · 4 years ago
Hi, I have a document of 10 sheets, but I only want the same 8 sheets to copy out each time I hit the command button. What changes can I make to the code (bearing in mind I am a complete amateur in Excel) to get each sheet to adopt the name of the main file, then add the sheet name at the end?

So as an example the file is called "TESTING" and it has a sheet called "EUROPE". When the macro runs, I want the Europe sheet to be copied out and called "TESTING - EUROPE.xls".
- Reply
To post as a guest, your comment is unpublished.
adnan · 4 years ago
Hi
The coding works well but I want the header also to come on each and every sheet. As it's skipped the header in all data...
- Reply
To post as a guest, your comment is unpublished.
Karissa Brandhagen · 4 years ago
How can I get this .xlsx file to save as .csv when split?
- Reply
To post as a guest, your comment is unpublished.
Nikk · 4 years ago
Thank you so much! It worked after I unhid the rest of the workbook. This is awesome!
- Reply
To post as a guest, your comment is unpublished.
Tomm · 4 years ago
2 questions.

1. How can you adjust the code the choose the file path of where it is saved? As opposed to just saving wherever the original file is.

2. How do you change the code to choose what column it creates the tabs from (i.e. in this code it is reading out of column A). I want to be able to choose which column it reads off if. Thanks!
- Reply
To post as a guest, your comment is unpublished.
Jessica P. · 4 years ago
Thank you very much for this.Saved me weeks of sweat...

Regards:Jessica
Active consumer on customerso
- Reply
To post as a guest, your comment is unpublished.
Anjali · 4 years ago
Hi, i have one quarry i.e. i have 15000 lines of data in one excel sheet and i want to split that data into multiple excel files where as each file contains 99 lines of data. Anyone can help me. Please share your thoughts.....
- Reply
To post as a guest, your comment is unpublished.
DS · 4 years ago
This VBA code worked perfectly. Thank you for sharing your wealth of knowledge.
- Reply
To post as a guest, your comment is unpublished.
Brad · 4 years ago
The macro should be updated to include an unhide-all-worksheets piece (including the very-hidden worksheets):

Sub UnhideAllSheets()
'Unhide all sheets in workbook.
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws
End Sub

Sub Splitbook()
'Updateby20140612
Dim xPath As String
xPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In ThisWorkbook.Sheets
xWs.Copy
Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xlsx"
Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
- Reply
To post as a guest, your comment is unpublished.
Brad · 4 years ago
IT SHOULD BE NOTED THAT THE VBA MACRO ABOVE WON'T WORK IF YOU HAVE A HIDDEN EXCEL SHEET, OR A "VERY-HIDDEN" EXCEL SHEET.

YOU MAY NOT BE AWARE THAT YOU HAVE A "VERY-HIDDEN" EXCEL SHEET. TO CHECK, HIT ALT+F11 TO OPEN MICROSOFT VISUAL BASIC (MACRO EDITOR). CLICK ON EACH WORKSHEET UNDER THE MICROSOFT EXCEL OBJECTS AND MAKE SURE THAT THE "Visible" ATTRIBUTE IN THE "Properties" SECTION SHOWS "-1 = xlSheetVisible". IF YOU SPIT AN EXCEL FILE OUT OF ORACLE, FOR EXAMPLE, YOU'LL SEE A WORKSHEET NAMED "BneLog" with visibility = "2 - xlSheetVeryHidden". This took me forever to figure out, lol. Hope it helps!
- Reply
- To post as a guest, your comment is unpublished.
  David · 4 years ago
  
  Great tip! I was beating my head against a wall until this occurred to me. I saw previous comments about hidden sheets but until I realized an existing macro was "very hidden", it didn't make sense to me. On to the next project!
  
  Reply
To post as a guest, your comment is unpublished.
sunil · 4 years ago
Used this and it worked. Just what I needed. Thanks
- Reply
To post as a guest, your comment is unpublished.
Jignesh S Thakrar · 4 years ago
Hi

I'm have master file were data consist for multiple city would like know is their any macros or any formula which keeps this orignal file and also create & update new workbook city area wise
- Reply
To post as a guest, your comment is unpublished.
Snehalata Gupta · 4 years ago
Hello all,

I need to reciprocate the same thing.

there are lots of excel sheets and i want it te 1st sheet of it in single work book,
Please Help!!!!
- Reply
To post as a guest, your comment is unpublished.
Bakhty · 4 years ago
VBA script worked like a charm, got my 80+ new excel files
- Reply
To post as a guest, your comment is unpublished.
shanojirao · 4 years ago
Hi All,

I need a help from you all,

I've to share the reports to multiple team depending on the brands they work for.

Eg: brand name:
Apple
Samsung
and many more around 60 brands.

there will be a sales dump.

If I want to run a macro, and spilt into multiple excel files what would be the procedure.

Kindly help me.

Thanks in Advance.

Regards,
Shan
- Reply
To post as a guest, your comment is unpublished.
Rogier van Meggelen · 5 years ago
The code works great. Can you also use this to save the tabs as pdf files.
It can't figure this out :)
- Reply
To post as a guest, your comment is unpublished.
Rogier van Meggelen · 5 years ago
The code works great! is it also possible to save the different tabs as a pdf? I tried but the i can't get it to work :)
- Reply
To post as a guest, your comment is unpublished.
Anandaraj.M · 5 years ago
Thanks for your valuable support
- Reply
To post as a guest, your comment is unpublished.
Stephanie · 5 years ago
The VBA code worked beautifully! It saved me so much time. Thank you for sharing!
- Reply

تسجيل الدخول إلى حسابك

استعادة كلمة المرور