Opomba: Drugi jeziki spletnega mesta so prevedeni z Googlom. Nazaj na angleščino
Vpiši se  \/ 
x
or
x
Registracija  \/ 
x

or

Kako večkrat kopirati več listov v Excelu?

Včasih bomo morda morali v delovni zvezek narediti kopijo ali več kopij določenih delovnih listov. Tu je nekaj hitrih načinov, s pomočjo katerih lahko v Excelu večkrat kopirate en delovni ali več delovnih listov.


Z ukazom Premakni ali Kopiraj enkrat naredite eno kopijo več delovnih listov v aktivni delovni zvezek ali drug delovni zvezek

Z Premakni ali Kopiraj ukaz v Excelu lahko hkrati ustvarite eno kopijo delovnega lista, več določenih delovnih listov ali vse delovne liste v aktivni delovni zvezek ali drug delovni zvezek.

1. V določenem delovnem zvezku, kamor boste kopirali delovne liste, izberite več delovnih listov v vrstici z zavihki Sheet.
Opombe: Držati CTRL tipko, lahko izberete več nesosednjih zavihkov lista, tako da jih kliknete enega za drugim v vrstici zavihka List; držati SHIFT tipko, lahko izberete več sosednjih zavihkov lista s klikom na prvi zavihek lista in zadnjega na vrstici zavihka List.

2. Z desno miškino tipko kliknite izbrani zavihek lista na vrstici zavihka Sheet in izberite Premakni ali Kopiraj iz kontekstnega menija. Oglejte si posnetek zaslona:
doc kopiraj več listov 01

3. v Premakni ali Kopiraj v pogovornem oknu navedite naslednje nastavitve:
(1) Navedite ciljni delovni zvezek, v katerega boste kopirali delovne liste iz Rezervirati spustni seznam. Po potrebi lahko izberete aktivni delovni zvezek, drug odprt delovni zvezek ali nov delovni zvezek.
(2) Določite položaj kopiranih listov na vrstici z zavihki Sheet, ki jih lahko izberete po vseh obstoječih listih.
(3) Preverite Ustvari kopijo Če te možnosti ne izberete, se izbrani delovni listi premaknejo v ciljni delovni zvezek.
(4) Kliknite OK gumb.

Zdaj bo naredil samo eno kopijo izbranih delovnih listov v navedeni delovni zvezek. Če želite narediti več kopij teh delovnih listov, lahko postopek ponovite večkrat.

Samo nekaj klikov, da naredite več kopij več delovnih listov v aktivnem delovnem zvezku

Na splošno lahko z delovnim listom Premakni ali Kopiraj funkcija v Excelu. Vendar pa lahko ta funkcija naenkrat naredi samo eno kopijo. Tukaj s Kutools za Excel Kopirajte delovne liste Pripomoček lahko preprosto naredite toliko kopij številnih delovnih listov, kot jih potrebujete v aktivnem delovnem zvezku, samo z več kliki. Popolnoma brezplačna preizkusna različica 30-dnevna!
kopija oglasa več delovnih listov 01

Kutools za Excel - Vključuje več kot 300 priročnih orodij za Excel. 30-dnevna brezplačna preizkusna različica brez kreditne kartice! Get It Now

Naredite več kopij enega določenega delovnega lista v aktivni delovni zvezek s kodo VBA

Če želite narediti 10 kopij določenega delovnega lista, se Premakni ali Kopiraj ukaz bo zamuden in operacijo morate večkrat ponoviti. Toda z naslednjo kodo VBA lahko delovni list hitro kopirate 10-krat naenkrat.

1. Držite tipko ALT + F11 in odpre tipko Microsoft Visual Basic za aplikacije okno.

2. Kliknite Vstavi > Moduliin prilepite naslednjo kodo v okno modula.

VBA: Naredite več kopij določenih delovnih listov v aktivni delovni zvezek

Sub Copier ()
Dim x As Integer
x = InputBox("Enter number of times to copy Sheet1")
For numtimes = 1 To x
ActiveWorkbook.Sheets("Sheet1").Copy _
After:=ActiveWorkbook.Sheets("Sheet1")
Next
End Sub

Opombe: V zgornji kodi nadomestite "Sheet1"z imenom lista, ki ga želite kopirati.

3. Nato pritisnite F5 tipko za zagon kode, se prikaže pozivno polje, ki vas vpraša, koliko kopij listov želite.

4. Nato kliknite OK, je bil navedeni delovni list v aktivnem delovnem zvezku 100-krat kopiran.


S Kutools za Excel ustvarite več kopij več delovnih listov v aktivni delovni zvezek

Čeprav je v Excelu enostavno narediti kopijo več delovnih listov, če želite v trenutnem delovnem zvezku narediti več kopij več delovnih listov, boste morda morali uporabiti Premakni ali Kopiraj znova in znova ukazni element. Bi radi stvar naredili z enim klikom? Z Kopirajte delovne liste uporabnost dodatka tretje osebe Kutools za Excel, lahko z enim klikom v Excelu naredite več kopij več delovnih listov.

Kutools za Excel - Vključuje več kot 300 priročnih orodij za Excel. 30-dnevna brezplačna preizkusna različica brez kreditne kartice! Get It Now

1. Kliknite Kutools Plus > Kopirajte delovne liste. Oglejte si posnetek zaslona:

Opombe: Klik Kutools Plus > Delovni list > Kopirajte delovne liste bo dobil tudi to funkcijo.

2. Uporabi nastavitve v Kopirajte več delovnih listov pogovorno okno:
(1) Označite delovne liste, iz katerih želite kopirati Kopirajte izbrane delovne liste oddelek.
(2) Navedite Število izvodov.
(3) Določite položaj kopiranih listov, na primer pred ali po vseh delovnih listih, pred ali za trenutnim delovnim listom.
(4) Kliknite Ok gumb.

3. Pojavi se pozivno polje, ki vas opozori, da so bili preverjeni delovni listi kopirani večkrat, kot potrebujete, kliknite OK za izhod iz njega. Oglejte si posnetek zaslona:

Ta Kopirajte več delovnih listov značilnost Kutools za Excel bo naredil več kopij več določenih delovnih listov v aktivnem delovnem zvezku samo z več kliki. Brezplačno preskusite!


Naredite eno kopijo več delovnih listov iz več delovnih zvezkov v novega

Če imate nameščen Kutools za Excel, ga lahko tudi uporabite Združite delovne liste funkcija za kopiranje več delovnih listov iz več zaprtih delovnih zvezkov v nov delovni zvezek z več kliki samo v Excelu.

Kutools za Excel - Vključuje več kot 300 priročnih orodij za Excel. 30-dnevna brezplačna preizkusna različica brez kreditne kartice! Get It Now

1. Kliknite Kutools Plus > Združite za aktiviranje funkcije kombiniranja delovnih listov.

2. V čarovniku za kombiniranje delovnih listov - 1. korak od 3, preverite Več delovnih listov iz delovnih zvezkov združite v en delovni list in kliknite na Naslednji . Oglejte si posnetek zaslona:

3. V čarovniku za kombiniranje delovnih listov - korak 2 od 3, storite tako, kot je prikazano na sliki:
(1) Kliknite Dodaj > file or Mapa za dodajanje delovnih zvezkov boste kopirali delovne liste.
(2) V Seznam delovnih zvezkov v razdelku preverite delovni zvezek, katerega delovne liste boste kopirali;
(3) V Seznam delovnih listov v razdelku preverite delovne liste, ki jih boste kopirali;
(4) Ponovite zgoraj (2) in (3) za izbiro delovnih listov iz drugih delovnih zvezkov, ki jih boste kopirali.
(5) Kliknite Naslednji gumb.

4. V čarovniku Combine Worksheets - Step 3 of 3 nastavite nastavitve kopiranja, kot želite, in kliknite Konec gumb.

5. Zdaj v novem pogovornem oknu določite ciljno mapo, v katero želite shraniti novo delovno knjigo, novo delovno knjigo poimenujte v Ime datoteke in kliknite Shrani . Oglejte si posnetek zaslona:

Zdaj se bosta odprli še dve pogovorni okni in vas prosili, da odprete nov delovni zvezek in shranite kombinacijo scenarijev. Doslej je kopiral vse določene delovne liste iz več delovnih zvezkov hkrati.

S tem Kombiniraj (delovni listi) Pripomoček lahko preprosto kopirate in kombinirate delovne liste in delovne zvezke, kot je prikazano na spodnjem seznamu. Brezplačno preskusite!
(1) hitro združite več delovnih listov / obsegov od delovnih zvezkov v en delovni list;
(2) Hitro združite / združite vse istoimenske delovne liste v delovnih zvezkih v en delovni list;
(3) hitro združite / združite delovne liste ali delovne zvezke v en delovni zvezek;
(4) Hitro povzemite / izračunajte podatke iz več delovnih listov v en delovni list.


Najboljša orodja za pisarniško produktivnost

Kutools za Excel rešuje večino vaših težav in poveča produktivnost za 80%

  • Ponovna uporaba: Hitro vstavite zapletene formule, grafikoni in vse, kar ste že uporabljali; Šifriraj celice z geslom; Ustvari poštni seznam in pošiljanje e-pošte ...
  • Vrstica Super Formula (enostavno urejanje več vrstic besedila in formule); Bralna postavitev (enostavno branje in urejanje velikega števila celic); Prilepite v filtrirani obseg...
  • Združi celice / vrstice / stolpce brez izgube podatkov; Vsebina razdeljenih celic; Združi podvojene vrstice / stolpce... prepreči podvojene celice; Primerjaj obsege...
  • Izberite Duplicate ali Unique Vrstice; Izberite prazne vrstice (vse celice so prazne); Super Find in Fuzzy Find v mnogih delovnih zvezkih; Naključna izbira ...
  • Natančna kopija Več celic brez spreminjanja sklica formule; Samodejno ustvarjanje referenc na več listov; Vstavi oznake, Potrditvena polja in še več ...
  • Izvleček besedila, Dodaj besedilo, Odstrani po položaju, Odstrani presledek; Ustvari in natisni vmesne seštevke strani Pretvarjanje med vsebino celic in komentarji...
  • Super filter (shranite in uporabite sheme filtrov za druge liste); Napredno razvrščanje glede na mesec / teden / dan, pogostost in drugo; Poseben filter s krepko, ležeče ...
  • Združite delovne zvezke in delovne liste; Spoji tabele na podlagi ključnih stolpcev; Razdelite podatke na več listov; Paketna pretvorba xls, xlsx in PDF...
  • Več kot 300 zmogljivih funkcij. Podpira Office / Excel 2007-2019 in 365. Podpira vse jezike. Preprosta namestitev v vašem podjetju ali organizaciji. Vse funkcije 30-dnevnega brezplačnega preskusa. 60-dnevno jamstvo za vračilo denarja.
zavihek kte 201905

Kartica Office prinaša vmesnik z zavihki v Office in poenostavi vaše delo

  • Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Poveča vašo produktivnost za 50% in vsak dan zmanjša na stotine klikov z miško!
dno pisarniške mize
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.
    Tyler Dempsey · 3 years ago
    I am new to this I am just trying to make copies of the same sheet this is the code I am using.

    Sub Copier()
    Dim s As String
    Dim numtimes As Integer
    Dim numCopies As Integer
    numCopies = InputBox("How many copies do you need?")
    s = InputBox("Enter the name of the Worksheet you want to copy")
    For numtimes = 1 To numCopies
    ActiveWorkbook.Sheets(s).Copy _
    After:=ActiveWorkbook.Sheets(Worksheets.Cou nt)
    Next
    End Sub

    when i use the code it gives me the error of "Syntax Error" then this text turns red ActiveWorkbook.Sheets(s).Copy _
    After:=ActiveWorkbook.Sheets(Worksheets.Cou nt) and the is text turns yellow Sub Copier() what can i do to fix it.
    • To post as a guest, your comment is unpublished.
      Jorge · 1 years ago
      Working Code

      Sub Copier()
      Dim s As String
      Dim numtimes As Integer
      Dim numCopies As Integer
      numCopies = InputBox("How many copies do you need?")
      s = InputBox("Enter the name of the Worksheet you want to copy")
      For numtimes = 1 To numCopies
      ActiveWorkbook.Sheets(s).Copy After:=ActiveWorkbook.Sheets(Worksheets.Count)
      Next
      End Sub

      copy the entire line from ActiveWorkbook.Sheets...... that was the problem, and some spaces

      Have a great day
    • To post as a guest, your comment is unpublished.
      Schuyler · 3 years ago
      [quote name="Tyler Dempsey"]I am new to this I am just trying to make copies of the same sheet this is the code I am using.

      Sub Copier()
      Dim s As String
      Dim numtimes As Integer
      Dim numCopies As Integer
      numCopies = InputBox("How many copies do you need?")
      s = InputBox("Enter the name of the Worksheet you want to copy")
      For numtimes = 1 To numCopies
      ActiveWorkbook.Sheets(s).Copy _
      After:=ActiveWorkbook.Sheets(Worksheets.Cou nt)
      Next
      End Sub

      when i use the code it gives me the error of "Syntax Error" then this text turns red ActiveWorkbook.Sheets(s).Copy _
      After:=ActiveWorkbook.Sheets(Worksheets.Cou nt) and the is text turns yellow Sub Copier() what can i do to fix it.[/quote]


      Here is the exact code you want to use:


      Sub Copier()
      Dim s As String
      Dim numtimes As Integer
      Dim numCopies As Integer
      numCopies = InputBox("How many copies do you need?")
      s = InputBox("Enter the name of the Worksheet you want to copy")
      For numtimes = 1 To numCopies
      ActiveWorkbook.Sheets(s).Copy _
      After:=ActiveWorkbook.Sheets(Worksheets.Count)
      Next
      End Sub
      • To post as a guest, your comment is unpublished.
        Belva · 1 months ago
        Thank you, the last one saved my life :)
    • To post as a guest, your comment is unpublished.
      Schuyler · 3 years ago
      [quote name="Tyler Dempsey"]I am new to this I am just trying to make copies of the same sheet this is the code I am using.

      Sub Copier()
      Dim s As String
      Dim numtimes As Integer
      Dim numCopies As Integer
      numCopies = InputBox("How many copies do you need?")
      s = InputBox("Enter the name of the Worksheet you want to copy")
      For numtimes = 1 To numCopies
      ActiveWorkbook.Sheets(s).Copy _
      After:=ActiveWorkbook.Sheets(Worksheets.Cou nt)
      Next
      End Sub

      when i use the code it gives me the error of "Syntax Error" then this text turns red ActiveWorkbook.Sheets(s).Copy _
      After:=ActiveWorkbook.Sheets(Worksheets.Cou nt) and the is text turns yellow Sub Copier() what can i do to fix it.[/quote]


      Double check your code and make sure you don't have a space at ActiveWorkbook. Sheets(s).Copy _ or at Sheets(Worksheets.Cou nt)
  • To post as a guest, your comment is unpublished.
    Simon · 4 years ago
    Fantastic VBA code - really helped
  • To post as a guest, your comment is unpublished.
    Barry · 4 years ago
    When I first tried it, I got the error message because I didn't change the name Sheet1. After I realized what caused the error, I researched a little further, as I did not want to be manually entering the sheet name into the macro. And when I need this feature, it is almost always for the current sheet.

    I added the line a = activesheet.name
    And revised the line after:=activeworkbook.sheets("sheet1") to activeworkbook.sheets(a).copy _

    That worked very well, but I did notice the numbering was reversed ... that didn't bother me as I was going to manually rename the new sheets anyway.

    When I saw Schuyler's post, I further revised the line activeworkbook.sheets(a).copy _ to after:=activeworkbook.sheets(worksheets.count)

    I am now satisfied with the result. My finished macro:

    Sub copies()
    Dim x As Integer
    x = InputBox("Enter number of times to copy Sheet1")
    For numtimes = 1 To x
    ActiveWorkbook.Sheets("Sheet1").Copy _
    After:=ActiveWorkbook.Sheets(Worksheets.Count)
    Next
    End Sub

    ---
    All the best, Barry
    • To post as a guest, your comment is unpublished.
      MJ · 2 years ago
      i got syntax error on "After:=ActiveWorkbook.Sheets(Worksheets.Count)


      but i dunno whats wrong... Can u help me please?
    • To post as a guest, your comment is unpublished.
      Kate · 3 years ago
      Thank you so much Barry. Your finished macro is the only thing that worked for me.
  • To post as a guest, your comment is unpublished.
    stalag 17 · 4 years ago
    where i will insert this above code in vba should i create common button then inside ?? regards.
    • To post as a guest, your comment is unpublished.
      Adi · 3 years ago
      Follow the below steps:
      1. Copy the above code
      2. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.
      2. Click Insert > Module, and paste the following code in the Module Window.
      4.Then press F5 key to run the code
      5.A prompt box appears to ask you the number of sheet copies you want.
  • To post as a guest, your comment is unpublished.
    Susan · 5 years ago
    I get an error that 'numtimes' is not defined...?
  • To post as a guest, your comment is unpublished.
    Debbi · 5 years ago
    I got the same error as Theou and no one seems to have addressed it. My tabs are already named PO 51, PO 52, etc. and I replaced Sheet1 with PO 51 to copy that and got the subscript error out of range (9)

    I followed Schuyler's code to get the right order, but I still get the error and it's always due to these two lines:

    ActiveWorkbook.Sheets(s).Copy _
    Before:=ActiveWorkbook.Sheets(Worksheets.Count)
    • To post as a guest, your comment is unpublished.
      Schuyler · 5 years ago
      [quote name="Debbi"]I got the same error as Theou and no one seems to have addressed it. My tabs are already named PO 51, PO 52, etc. and I replaced Sheet1 with PO 51 to copy that and got the subscript error out of range (9)

      I followed Schuyler's code to get the right order, but I still get the error and it's always due to these two lines:

      ActiveWorkbook.Sheets(s).Copy _
      Before:=ActiveWorkbook.Sheets(Worksheets.Count)[/quote]

      Can you include your complete code to make it easier to debug? A "subscript error out of range" usually means that the code references something that doesn't exist. I find this in my own code when I've got a typo or something of that nature.
  • To post as a guest, your comment is unpublished.
    Roy · 5 years ago
    how to copy with same column widths
    • To post as a guest, your comment is unpublished.
      Schuyler · 5 years ago
      Same column widths as the original Worksheet or do you want all of the column widths to be the same?
  • To post as a guest, your comment is unpublished.
    sadaqat · 5 years ago
    yes it works thanks I have successfully make multiple copies of a single worksheet in same workbook by using vba code thnx a lot.
  • To post as a guest, your comment is unpublished.
    Gopal Krishan · 5 years ago
    Search for word "After" and change it to "Before". This will create copies in right order "Before" Sheet1. The only thing to rememeber is that numbering will always start from (2) as the original sheet will always be counted as 1st copy. Also you can replace "Sheet1" with name of the tab you are trying to copy.
  • To post as a guest, your comment is unpublished.
    juan · 6 years ago
    [quote name="MichaelTadashi"]Using the VBA code, the naming of the duplicated worksheets is in reverse order. Let's say I make 10 copies of Sheet1, I'll end up with Sheet1, Sheet1(10), Sheet1(9), Sheet1(8),.......is it possible to have them in normal order?[/quote]

    Anyone was able to answer this question? I need to create 72 copies, but it would be needed to have them in order (1 throught 72, intead of 72 through 1)

    Thanks!
    • To post as a guest, your comment is unpublished.
      Schuyler · 5 years ago
      [quote name="juan"][quote name="MichaelTadashi"]Using the VBA code, the naming of the duplicated worksheets is in reverse order. Let's say I make 10 copies of Sheet1, I'll end up with Sheet1, Sheet1(10), Sheet1(9), Sheet1(8),.......is it possible to have them in normal order?[/quote]

      Anyone was able to answer this question? I need to create 72 copies, but it would be needed to have them in order (1 throught 72, intead of 72 through 1)

      Thanks![/quote]

      If you want the sheet copies to be in sequential order instead of backwards, change the following line...

      [i]After:=ActiveWorkbook.Sheets[/i][u]("Sheet1")[/u]

      to this...

      [i]After:=ActiveWorkbook.Sheets[/i][u](Worksheets.Count)[/u]

      My completed code looked like the following which uses 2 InputBox prompts to allow for a dynamic copy count and worksheet name..


      [i]Sub Copier()
      Dim s As String
      Dim numtimes As Integer
      Dim numCopies As Integer
      numCopies = InputBox("How many copies do you need?")
      s = InputBox("Enter the name of the Worksheet you want to copy")
      For numtimes = 1 To numCopies
      ActiveWorkbook.Sheets(s).Copy _
      After:=ActiveWorkbook.Sheets(Worksheets.Count)
      Next
      End Sub[/i]
      • To post as a guest, your comment is unpublished.
        Karsten · 4 years ago
        How do i get the coppies continuous numbered. If the sheet i want to copy is named I002, and i want the next to be named I003,I004,I005 an so on.
  • To post as a guest, your comment is unpublished.
    Lim · 6 years ago
    Wow, thanks for the VBA code. It really helps a lot!
  • To post as a guest, your comment is unpublished.
    Dee · 6 years ago
    thanks, the vba code was huge
  • To post as a guest, your comment is unpublished.
    Theou Aegis · 6 years ago
    I tried the VBA code and got subscript out of range error (9). I replaced the sheet name with anything I could think of that was in the workbook already. Any idea what I did wrong?

    Also, is there a way to make it name each sheet incrementally? This would tie in with Michael's question, I'm sure. I'm guessing the answer to his question would be you'd need to set the After target to "sheet"+x somehow and that in turn would apply to my question if there was a way to specify what to name the new sheet. I could always just change my boss' "master" to "Aug 0" or whatever month it is, but it'd be easier for her to understand if the script did the naming automatically.
  • To post as a guest, your comment is unpublished.
    MichaelTadashi · 7 years ago
    Using the VBA code, the naming of the duplicated worksheets is in reverse order. Let's say I make 10 copies of Sheet1, I'll end up with Sheet1, Sheet1(10), Sheet1(9), Sheet1(8),.......is it possible to have them in normal order?