Note: The other languages of the website are Google-translated. Back to English
Log på  \/ 
x
or
x
Tilmeld  \/ 
x

or

Hvordan opdeles en projektmappe for at adskille Excel-filer i Excel?

Det kan være nødvendigt at dele en stor projektmappe for at adskille Excel-filer ved at gemme hvert regneark i projektmappen som en individuel Excel-fil. For eksempel kan du opdele en projektmappe i flere individuelle Excel-filer og derefter levere hver fil til forskellige personer for at håndtere den. Ved at gøre det kan du få bestemte personer til at håndtere specifikke data og holde dine data sikre. Denne artikel introducerer måder at opdele en stor projektmappe på for at adskille Excel-filer baseret på hvert regneark.

  1. Del en projektmappe for at adskille Excel-filer med kopiering og indsættelse
  2. Del en projektmappe for at adskille Excel-filer med funktionen Flyt eller Kopi
  3. Del en projektmappe for at adskille Excel-filer med VBA-kode
  4. Opdel en projektmappe for nemt at adskille Excel / PDF / CSV / TXT-filer med Kutools til Excel

Del en projektmappe for at adskille Excel-filer med kopiering og indsættelse

Som normalt bruger Kopi kommando og pasta kommando kan gemme en projektmappe manuelt som en separat Excel-fil. Vælg først hele regnearket, som du vil gemme som en separat fil, opret en ny projektmappe, og indsæt den derefter i den nye projektmappe, gem den til sidst.

Dette er en brugervenlig måde, hvis du kun har brug for at opdele et par regneark som separate filer. Det skal dog være tidskrævende og kedeligt at opdele mange regneark med kopiering og indsættelse manuelt.


Del en projektmappe for at adskille Excel-filer med funktionen Flyt eller Kopi

Denne metode introducerer funktionen Flyt eller kopi for at flytte eller kopiere de valgte ark til en ny projektmappe og gemme som en separat projektmappe. Gør følgende:

1. Vælg arkene på fanebladet Ark, højreklik og vælg Flyt eller Kopier fra genvejsmenuen. Se skærmbillede:

Bemærk: Holding Ctrl nøgle, kan du vælge flere ikke-tilstødende ark ved at klikke på dem en efter en i fanebladet Ark; holder Flytte nøgle, kan du vælge flere tilstødende ark ved at klikke på det første og det sidste i fanebladet Ark.

2. I dialogboksen Flyt eller Kopi skal du vælge (ny bog) fra At reservere rulleliste, tjek Opret en kopi valgmulighed, og klik på knappen OK knap. Se skærmbillede:

3. Nu er alle valgte ark kopieret til en ny projektmappe. Klik på filet > Gem for at gemme den nye projektmappe.

Del hurtigt en projektmappe for at adskille Excel / PDF / TXT / CSV-filer i Excel

Normalt kan vi opdele en projektmappe til individuelle Excel-filer med Flyt eller Kopier funktion i Excel. Men Kutools til Excel Opdelt arbejdsbog hjælpeprogram kan hjælpe dig med let at opdele en projektmappe og gemme hvert regneark som en separat PDF / TEXT / CSV-fil eller projektmappe i Excel. Komplet funktion Gratis prøveperiode 30 dage!
annonce split projektmappe excel

Kutools til Excel - Indeholder mere end 300 praktiske værktøjer til Excel. Fuld funktion gratis prøveperiode 30 dage, intet kreditkort krævet! Hent den nu

Del en projektmappe for at adskille Excel-filer med VBA-kode

Følgende VBA-kode kan hjælpe dig med hurtigt at opdele flere regneark i den aktuelle projektmappe for at adskille Excel-filer. Gør som følger:

1. Opret en ny mappe til den projektmappe, som du vil opdele, fordi de delte Excel-filer forbliver i samme mappe som denne hovedarbejdsmappe.

2. Hold nede ALT + F11 nøgler i Excel, og det åbner Microsoft Visual Basic til applikationer vindue.

3. Klik indsatte > Moduler, og indsæt følgende kode i modulvinduet.

VBA: Del en projektmappe i flere projektmapper, og gem den i samme mappe

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. Tryk på F5 nøgle til at køre denne kode. Og projektmappen er opdelt for at adskille Excel-filer i samme mappe med den originale projektmappe. Se skærmbillede:

Bemærk: Hvis et af arkene har samme navn med projektmappen, kan denne VBA ikke fungere.

Opdel en projektmappe for nemt at adskille Excel / PDF / CSV / TXT-filer med Kutools til Excel

Hvis du har Kutools til Excel installeret, dens Opdelt arbejdsbog værktøj kan opdele flere regneark som separate Excel-filer bekvemt og hurtigt med kun et par klik.

Kutools til Excel - Indeholder mere end 300 praktiske værktøjer til Excel. Fuld funktion gratis prøveperiode 30 dage, intet kreditkort krævet! Hent den nu

1. Efter installation Kutools til Excelklik Kutools Plus > Opdelt arbejdsbog , se skærmbillede:

2. I Opdelt arbejdsbog i dialogboksen, skal du udføre følgende handlinger:
(1) Alle regnearknavne er markeret som standard. Hvis du ikke vil opdele nogle af regnearkene, kan du fjerne markeringen af ​​dem.
(2) Kontroller Gem en type mulighed;
(3) Fra Filtype drop down, vælg en filtype, du vil opdele og gemme.
(4) Klik derefter på Dele .

Bemærk: Hvis du vil undgå at opdele de skjulte eller tomme regneark, kan du kontrollere Spring skjulte regneark over or Spring blanke regneark over boks.

3. I dialogboksen Gennemse efter mappe skal du angive en destinationsmappe for at gemme de separate separate filer og klikke på OK .

Nu gemmes de afkrydsede regneark som nye adskilte projektmapper. Hver nye projektmappe er navngivet med det originale regnearknavn. Se skærmbillede:

Kutools til Excel's Opdelt arbejdsbog værktøj gør det let at opdele aktiv projektmappe i individuelle Excel-filer (en fil indeholder et regneark), CSV-filer, TXT-filer eller PDF-filer efter behov. Du kan indstille til at springe alle de tomme og skjulte regneark over. Få en gratis prøveperiode!


Demo: Opdel eller gem hvert regneark i en projektmappe som separate excel / txt / csv / pdf-filer


Kutools til Excel indeholder mere end 300 praktiske værktøjer til Excel, gratis at prøve uden begrænsning på 30 dage. Download og gratis prøveversion nu!

Relaterede artikler:


De bedste Office-produktivitetsværktøjer

Kutools til Excel løser de fleste af dine problemer og øger din produktivitet med 80%

  • Genbruge: Indsæt hurtigt komplekse formler, diagrammer og alt, hvad du har brugt før; Krypter celler med adgangskode Opret postliste og send e-mails ...
  • Super formel bar (let redigere flere linjer med tekst og formel); Læsning Layout (let at læse og redigere et stort antal celler); Indsæt til filtreret rækkevidde...
  • Flet celler / rækker / kolonner uden at miste data; Split celler indhold; Kombiner duplikerede rækker / kolonner... Forhindre duplikerede celler; Sammenlign områder...
  • Vælg Duplicate eller Unique Rækker; Vælg tomme rækker (alle celler er tomme); Super Find og Fuzzy Find i mange arbejdsbøger; Tilfældig valg ...
  • Præcis kopi Flere celler uden at ændre formelreference; Auto Opret referencer til flere ark; Indsæt kugler, Afkrydsningsfelter og mere ...
  • Uddrag tekst, Tilføj tekst, Fjern efter position, Fjern mellemrum; Opret og udskriv personsøgningssubtotaler; Konverter mellem celler indhold og kommentarer...
  • Superfilter (gem og anvend filterskemaer på andre ark); Avanceret sortering efter måned / uge / dag, hyppighed og mere; Specielt filter af fed, kursiv ...
  • Kombiner arbejdsbøger og arbejdsark; Fletabeller baseret på nøglekolonner; Opdel data i flere ark; Batch Konverter xls, xlsx og PDF...
  • Mere end 300 kraftfulde funktioner. Understøtter Office / Excel 2007-2019 og 365. Understøtter alle sprog. Nem implementering i din virksomhed eller organisation. Fuld funktioner 30-dages gratis prøveperiode. 60-dages pengene tilbage garanti.
kte-fane 201905

Fanen Office bringer en grænseflade til et kontor med Office, og gør dit arbejde meget lettere

  • Aktiver redigering og læsning af faner i Word, Excel, PowerPoint, Publisher, Access, Visio og Project.
  • Åbn og opret flere dokumenter i nye faner i det samme vindue snarere end i nye vinduer.
  • Øger din produktivitet med 50% og reducerer hundredvis af museklik for dig hver dag!
officetab bund
Say something here...
symbols left.
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.
    Adrian Rees · 5 years ago
    Thanks, vba code worked perfectly! Saved me a lot of time splitting a spreadsheet with 25 tabs into different files.

    Only thing i picked up is in the 6th last line i changed the file format to new Excel "xlsx"

    " & xWs.Name & ".xls" becomes >>>>> " & xWs.Name & ".xlsx"
  • To post as a guest, your comment is unpublished.
    Herb · 5 years ago
    Thanks for the VBA code!!. It worked perfectly. Saved me much work!!
  • To post as a guest, your comment is unpublished.
    NG · 5 years ago
    Hi there,

    Thank you so much for the code, however may how you would add to the code if you wanted the following to happen?
    1. What if you wanted Sheet 1 to be copied as well for each additional sheets?

    For example, the newly created
    1. "Sheet 2" would have "Sheet 1 and Sheet 2"
    2. "Sheet 3" would have "Sheet 1 and Sheet 3"

    and so on and so forth.

    Appreciate if you could provide any help with this. Thank you.
  • To post as a guest, your comment is unpublished.
    zirokl · 5 years ago
    Thank you so much for this! It is amazing, works perfectly.

    But is it possible to modify it so it saves each worksheet to the same directory(without creating a new folder) based on original file name or particular Cell Value? Just by adding (1), (2) ets at the end of each exported file.

    Greatly appreciate your help.
  • To post as a guest, your comment is unpublished.
    NG · 5 years ago
    you saved my day with this code!!
  • To post as a guest, your comment is unpublished.
    Ovi · 5 years ago
    Is there a way I can apply the split to many excel files at once? The files have the same 2 sheets (as format and name).
    Thank you!
  • To post as a guest, your comment is unpublished.
    Brian · 5 years ago
    Hello, Thank you; super helpful. I was curious how to edit the save directory. When I run this code, it saves one directory up from the directory the original file is saved. Can someone please tell me how to make it save to the source file's directory?
  • To post as a guest, your comment is unpublished.
    Sam · 5 years ago
    I am having data for all the 365 days for 14 year in one excel sheet.

    Now we want to separate the data year wise (Ex. one excel file for year 2002 in that 12 Sheet ie for 12 month and another excel file for year 2003 in that 12 Sheet ie for 12 month).

    We are able to separate month wise data (by using Excel Kutools - Split Data) and it will create excel sheet for each month (ie it will create totally 24 sheets) and then we should Split to Workbook and it will be 24 excel file and again we should combine 12 file for each year. This is taking lot of time.

    Now our problem is while splitting the date we want Excel to create Year wise Excel files and in each excel file 12 sheets for 12 months. Can we do this in same time.

    Please tell us because we are having lots of data and it is taking lot of time.
  • To post as a guest, your comment is unpublished.
    Caroline · 6 years ago
    Hi - I have a work sheet with a lot of data that I need to split into separate sheets so I can attach and email to various people. Do you know how I would go about this? I have a TOTAL cell for each batch of data that I need to split, not all the batches contain the same number of lines. Any help you can give would be greatly appreciated. Thank you.
  • To post as a guest, your comment is unpublished.
    John Boyd · 6 years ago
    Good stuff! Thank you!
  • To post as a guest, your comment is unpublished.
    Maggie · 6 years ago
    I receive a Run time error '1004'. Is this due to me using a pivot table? Is there a way around this?
    • To post as a guest, your comment is unpublished.
      Shiva-India · 6 years ago
      Dear Maggie,

      I also get Run time error '1004'. But again I tried and succeed. The error because of I hide one sheet. After unhide that sheet, I got. Check once if it is same case.
      • To post as a guest, your comment is unpublished.
        KY · 2 years ago
        THANK YOU! Finally I got this to work.
  • To post as a guest, your comment is unpublished.
    Fayyadh · 6 years ago
    Its not work
    Stuck at xWs.Copy
    what i suppose to do?

    Thanks :-)
    • To post as a guest, your comment is unpublished.
      Karen · 5 years ago
      I'm having this same problem now. It worked fine in previous months, but for some reason is not now. Did you figure out how to fix?
  • To post as a guest, your comment is unpublished.
    Andrew · 6 years ago
    I have to say I [b]LOVE[/b] Kutools for Excel, it saves me so much time everyday!

    One questions on the above process, is there an option to not open every sheet once it's created and just save it into the designated folder?
  • To post as a guest, your comment is unpublished.
    s · 6 years ago
    Superb Code. Actually searching for this.
  • To post as a guest, your comment is unpublished.
    Krishna · 6 years ago
    It's really Useful. Thanks for sharing.
  • To post as a guest, your comment is unpublished.
    Yasir Arfat · 6 years ago
    Can you please tell me that how can i send mass emails to with personalized attachment in outlook..

    Regrads,
    Yasir
    • To post as a guest, your comment is unpublished.
      Sanjay · 6 years ago
      Hi Yasir,

      You can't send personalized attachment in mail merge with outlook.

      Yes, few external tools are there but these are very costly also not reliable.

      You can do this with Mozilla Thunderbird.

      Just need to download mail merge add on & you can do this.
  • To post as a guest, your comment is unpublished.
    nayeem khan · 6 years ago
    Dear Sir,

    Can your please send me details how coding to split s
    • To post as a guest, your comment is unpublished.
      Sanjay · 6 years ago
      You can follow the below coding.

      VBA Coding for split sheets
      Alt+f11
      Insert-module-f5


      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 & ".xls"
      Application.ActiveWorkbook.Close False
      Next
      Application.DisplayAlerts = True
      Application.ScreenUpdating = True
      End Sub
      • To post as a guest, your comment is unpublished.
        Lisa · 4 years ago
        Can I save your module script to the PERSONAL Macro Workbook for future use?
      • To post as a guest, your comment is unpublished.
        Rony · 4 years ago
        Dear Mr Sanjay,

        I tried with the codes it works only for few sheets but the file I am trying to split having almost 466 sheets. May I know the codes for it? Thank you.
      • To post as a guest, your comment is unpublished.
        denise · 6 years ago
        Worksheets were product of pivot table report, new files created by the code still retain connection to data source and can be changed.
        can code be done to break pivot table connection, or add a copy all of each sheet & past as values to rid the connection?
  • To post as a guest, your comment is unpublished.
    Sanjay · 6 years ago
    This code & the KUtools both shows an error after converting 222 files & after 222 files both stop working.
  • To post as a guest, your comment is unpublished.
    Suzi · 6 years ago
    Its Showing error in the line 8....
    xws cpy is error
    • To post as a guest, your comment is unpublished.
      Marie · 5 years ago
      It worked for me after I removed the Option Explicit, so the variable needed to be defined.
      However, I would like to know what this variable should be defined as, as I appreciate the Option Explicit option :)
  • To post as a guest, your comment is unpublished.
    Lara · 6 years ago
    Works great, thanks. I have created this as an add-in and installed it. How do I now run it whenever I want to (without having to copy and paste each time)? Is there a button I push or a function name I use?
  • To post as a guest, your comment is unpublished.
    revati · 7 years ago
    Used this and it worked. Just what I needed. Thanks
  • To post as a guest, your comment is unpublished.
    ADO · 7 years ago
    Great help!!! extremely easy and useful :-)
  • To post as a guest, your comment is unpublished.
    Jennifer · 7 years ago
    Very useful! Thanks for sharing
  • To post as a guest, your comment is unpublished.
    Dodger · 7 years ago
    VBA instructions were "spot on"! ... even for a novice!
    Worked great!
  • To post as a guest, your comment is unpublished.
    Frankie · 7 years ago
    Thank you! Saved me a lot of manual effort!
  • To post as a guest, your comment is unpublished.
    joy · 7 years ago
    Thank you method 1 was extremely useful
  • To post as a guest, your comment is unpublished.
    Starscor · 7 years ago
    Useful script, but i'm looking for further steps, to self-pick a critera from a excel and auto-populate into multiple files
  • To post as a guest, your comment is unpublished.
    RAVISHANKAR · 7 years ago
    Awesome script.It's worked for me like anything. Thanks.
  • To post as a guest, your comment is unpublished.
    Shatrughan singh · 7 years ago
    Really superb facility.., amazing...wow..
  • To post as a guest, your comment is unpublished.
    KP · 7 years ago
    I am having an issue adding code to save the split files as ReadOnly. Why does this not work or how do I do this?


    Sub Splitbook()
    MyPath = ThisWorkbook.Path
    For Each sht In ThisWorkbook.Sheets
    sht.Copy
    ActiveSheet.Cells.Copy
    ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues
    ActiveSheet.Cells.PasteSpecial Paste:=xlPasteFormats
    ActiveWorkbook.SaveAs _
    Filename:=MyPath & "\" & sht.Name & ".xls",
    Password:="",
    WriteResPassword:="",
    ReadOnlyRecommended:=False,
    ActiveWorkbook.Close savechanges:=False
    Next sht
    End Sub
  • To post as a guest, your comment is unpublished.
    terp · 7 years ago
    Awesome code - saved the day for me. Thank you for posting.
  • To post as a guest, your comment is unpublished.
    Chandrasekar T · 7 years ago
    Column A IP Address
    Column B IP Name
    Column C IP Pinging Rate True (That is command Pinging Status)
    Column D IP Pinging Rate False (That is command Pinging Status)
    This ip and ip name list to excel format & status is same excel format(that is true or false Status)
  • To post as a guest, your comment is unpublished.
    Daniel · 7 years ago
    The code worked like a charm!
    Yes, not all formatting were kept.
    No problem, though. For me, all the merged cells in a table became un-merged. It was a simple thing to select the original table, format painter, highlight the copied table, and voila, good as new.

    Thanks!
  • To post as a guest, your comment is unpublished.
    Rebecca · 7 years ago
    When I "Split a workbook to separate Excel files with VBA code" it works a dream but does not keep formatting such as merged cells and cell alignment in the new spreadsheets.
    Is there something I can add so the formatting is kept the same as the originals?
  • To post as a guest, your comment is unpublished.
    Dhanasekar Sakthivel · 7 years ago
    I have data's in 4 different sheet. Sheet1 is named as "A", Sheet2 is named as "B", Sheet3 is named as "C" and Sheet4 is named as "D".
    Need to split the data into different workbooks and each sheet must have only 25 rows. The file name should be named as Sheet1 A.xls, Sheet2 A.xls if the data is copied from Sheet A. Once the Sheet is A become empty, it must copy the data from Sheet B and must continue the Sheet number from last e.g Sheet3 B.xls.