Ide: Lòt lang yo se Google-tradui. Ou ka vizite a English vèsyon nan lyen sa a.
Ouvri yon sesyon
x
or
x
x
enskri
x

or

Ki jan yo konbine manyèl miltip nan yon sèl workbook mèt nan Excel?

Èske w te janm te kole lè ou gen konbine manyèl miltip nan yon workbook mèt nan Excel? Bagay ki pi terib se ke liv sa yo ou bezwen konbine genyen plizyè fich. Ak ki jan yo konbine sèlman fich yo espesifye nan manyèl miltip nan yon liv travay pratik? Tutorial sa a montre plizyè metòd itil yo ede ou rezoud etap sa yo pwoblèm pa etap.


Fasil konbine manyèl miltip nan yon sèl nan Excel:

Jounal Konbine manyèl sèvis piblik nan Kutools pou Excel ka ede w fasil konbine miltip dosye Excel nan yon sèl. Anplis, li pèmèt ou sèlman konbine fich yo espesifye nan sèten dosye Excel nan yon workbook mèt fasil. Download karakteristik la plen 60-jou gratis santye kounye a!

Konbine manyèl miltip nan yon liv travay pratik ak deplase oswa Kopi fonksyon

Si gen jis yon koup liv gid bezwen yo dwe konbine, ou ka itilize deplase oswa kopi lòd la manyèlman deplase oswa kopye fey ki soti nan workbook orijinal la nan liv travay la mèt.

NAN. Louvri liv sa yo ki ou pral rantre nan yon liv travayè mèt.

NAN. Chwazi fich yo nan liv travay orijinal la ke ou ap deplase oswa kopye nan liv travay la mèt.

nòt:

1). Ou ka chwazi miltip fo ki pa adjasan avèk kenbe la Ctrl kle epi klike onglè yo fèy youn pa youn.

2). Pou chwazi plizyè fichye adjasan, tanpri klike sou premye tab la, kenbe la Ka glise kle, ak Lè sa a, klike sou dènye tab la fèy papye yo chwazi yo tout.

3). Ou ka klike sou dwa sou nenpòt ki tab fèy, klike sou Chwazi tout dra nan meni kontèks la yo chwazi tout fich nan workbook a an menm tan.

NAN. Apre ou chwazi feyèt ki nesesè yo, klike sou tab tab la, epi klike sou li a Deplase oswa Kopi soti nan meni an kontèks. Al gade nan Ekran:

4. Lè sa a, la Deplase oswa Kopi dyalòg Pops leve, nan la Nan liv gout-desann, chwazi liv travay la mèt ou ap deplase oswa kopye fo nan. Chwazi deplase nan fen nan la Anvan fèy bwat, tcheke a Kreye yon kopi bwat, epi finalman klike sou la OK bouton.

Lè sa a, ou ka wè fich nan de liv yo konbine nan yon sèl. Tanpri repete etap sa yo pi wo a pou avanse pou pi worksheets soti nan liv manyèl nan livrètr la mèt.

Konbine manyèl miltip oswa fèy papye espesifye nan liv gid pou yon workbook mèt ak VBA

Si gen manyèl miltip bezwen fusion nan yon sèl, ou ka aplike kòd sa yo VBA sa yo byen vit reyalize li. Tanpri fè jan sa a.

NAN. Mete tout manyèl ke ou vle konbine nan yon sèl anba menm anyè a.

NAN. Lanse yon dosye Excel (liv travay pratik sa a pral livrèr mèt la).

3. Peze la Alt + F11 kle yo louvri a Microsoft Visual Basic pou aplikasyon yo fenèt. Nan Microsoft Visual Basic pou aplikasyon yo fenèt, klike Antre > Modil, Lè sa a, kopi anba a VBA kòd nan fenèt la modil.

VBA kòd 1: Rantre plizyè manyèl Excel nan yon sèl

Sub GetSheets()
'Updated by Extendoffice 2019/2/20
Path = "C:\Users\dt\Desktop\dt kte\"
Filename = Dir(Path & "*.xls")
  Do While Filename <> ""
  Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
     For Each Sheet In ActiveWorkbook.Sheets
     Sheet.Copy After:=ThisWorkbook.Sheets(1)
  Next Sheet
     Workbooks(Filename).Close
     Filename = Dir()
  Loop
End Sub
	

nòt:

NAN. Kòd VBA ki anwo a ap kenbe non fèy papye orijinal yo apre fizyon.

NAN. Si ou vle yo fè distenksyon ant ki worksheets nan liv travayè a mèt soti nan kote apre melanje, tanpri aplike anba a kòd VBA 2.

NAN. Si ou jis vle konbine espesifik fichye nan manyèl yo nan yon liv travayè mèt, anba a kòd VBA 3 ka ede.

Nan kòd VBA, "C: Itilizatè yo DT168”Se chemen katab la. Nan kòd la VBA 3, "Sheet1, Sheet3"se fich yo espesifye nan liv yo ou pral konbine nan yon livrètr mèt. Ou ka chanje yo ki baze sou bezwen ou yo.

VBA kòd 2: Rantre nan Liv Atik yon sèl (pral chak Fèy travay yo te rele ak prefiks nan non dosye orijinal li):

Sub MergeWorkbooks()
'Updated by Extendoffice 2019/2/20
Dim xStrPath As String
Dim xStrFName As String
Dim xWS As Worksheet
Dim xMWS As Worksheet
Dim xTWB As Workbook
Dim xStrAWBName As String
On Error Resume Next
xStrPath = "C:\Users\DT168\Desktop\KTE\"
xStrFName = Dir(xStrPath & "*.xlsx")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set xTWB = ThisWorkbook
Do While Len(xStrFName) > 0
    Workbooks.Open Filename:=xStrPath & xStrFName, ReadOnly:=True
    xStrAWBName = ActiveWorkbook.Name
    For Each xWS In ActiveWorkbook.Sheets
    xWS.Copy After:=xTWB.Sheets(xTWB.Sheets.Count)
    Set xMWS = xTWB.Sheets(xTWB.Sheets.Count)
    xMWS.Name = xStrAWBName & "(" & xMWS.Name & ")"
    Next xWS
    Workbooks(xStrAWBName).Close
    xStrFName = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

VBA kòd 3: Rantre nan fich espesifik nan liv travay nan yon workbook mèt.

Sub MergeSheets2()
'Updated by Extendoffice 2019/2/20
Dim xStrPath As String
Dim xStrFName As String
Dim xWS As Worksheet
Dim xMWS As Worksheet
Dim xTWB As Workbook
Dim xStrAWBName As String
Dim xI As Integer
On Error Resume Next

xStrPath = " C:\Users\DT168\Desktop\KTE\"
xStrName = "Sheet1,Sheet3"

xArr = Split(xStrName, ",")

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set xTWB = ThisWorkbook
xStrFName = Dir(xStrPath & "*.xlsx")
Do While Len(xStrFName) > 0
Workbooks.Open Filename:=xStrPath & xStrFName, ReadOnly:=True
xStrAWBName = ActiveWorkbook.Name
For Each xWS In ActiveWorkbook.Sheets
For xI = 0 To UBound(xArr)
If xWS.Name = xArr(xI) Then
xWS.Copy After:=xTWB.Sheets(xTWB.Sheets.count)
Set xMWS = xTWB.Sheets(xTWB.Sheets.count)
xMWS.Name = xStrAWBName & "(" & xArr(xI) & ")"
Exit For
End If
Next xI
Next xWS
Workbooks(xStrAWBName).Close
xStrFName = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

4. Peze la F5 kle kouri kòd la. Lè sa a, tout worksheets oswa espesifye worksheets nan liv yo nan folder nan sèten yo konbine nan yon workbook mèt nan yon fwa.

Fasil konbine manyèl miltip oswa fèy papye espesifye nan liv gid pou yon liv travay pratik

Kutools pou Excel

Li se yon Excel sou la men ajoute-an ki ranmase plis pase 300 karakteristik avanse senplifye travay ou nan operasyon Excel. Avèk li, ou ka fasilman:

  • Konbine Livrezon miltip;
  • Konte / sòm selil pa koulè;
  • Split cellules;
  • Pakèt retire espas;
  • Plis karakteristik ...

GRATIS DOWNLOAD KOUNYE A!

Erezman, nan Konbine livrezon travayè nan Kutools pou Excel fè li pi fasil pou amalibre liv miltip nan yon sèl. Ann wè ki jan yo ka resevwa travay sa a fonksyon nan konbine manyèl miltip.

NAN. Kreye yon nouvo liv travayè epi klike sou Kutools Plis > Konbine. Lè sa a, yon dyalòg Pops fè ou sonje ke tout workbooks konbine ta dwe sove ak karakteristik nan pa ka aplike nan liv travay pwoteje, tanpri klike sou OK bouton.

2. Nan Konbine Worksheets sòsye, chwazi Konbine fichye miltip soti nan liv sou yon liv opsyon, ak Lè sa a klike sou la Next bouton. Al gade nan Ekran:

3. Nan Konbine Worksheets - Step 2 nan 3 bwat dyalòg, klike sou la Add > File or Dosye ajoute dosye yo Excel ou pral plonje nan yon sèl. Apre ajoute dosye yo Excel, klike sou la Fini bouton epi chwazi yon katab pou konsève liv travayè mèt la. Al gade nan D:

Koulye a, tout manyèl yo fizyone nan yon sèl.

Konpare ak de pi wo a metòd yo, Kutools pou Excel gen avantaj sa yo:

  • 1) Tout manyèl ak workheets yo ki nan lis nan ti bwat la dyalòg;
  • 2) Pou fich yo ou vle eskli nan fusion, jis dezaktive li;
  • 3) fich vid yo eskli otomatikman;
  • 4) ap pote non orijinal la kòm prefiks sou non fèy la apre fizyon;
  • Pou plis fonksyon nan karakteristik sa a, tanpri vizite isit la.

Sa a Konbine liv travay èd sèvis piblik? Se pou nou download epi eseye karakteristik la plen gratis jijman 60-jou!

Rekòmande zouti pwodiktivite pou Microsoft Office

Tab Office - Tabbed Navigasyon, Koreksyon, ak jere nan Excel / Pawòl / PowerPoint:

Biwo Tab pote koòdone nan onglè jan yo wè nan navigatè entènèt tankou Google Chrome, Internet Explorer nouvo vèsyon ak Firefox nan Microsoft Excel / Pawòl / PowerPoint. Li pral yon zouti ekonomize tan ak iranplaceble nan travay ou. Gade anba a Demo:

ot1

Kutools pou Excel - Konbine Plis Pase 300 Fonksyon avanse ak Zouti pou Microsoft Excel:

Kutools pou Excel se yon sou la men Excel ajoute-an ak plis pase XNX karakteristik avanse senplifye divès kalite travay konplike nan yon kèk klik nan Excel. Pa egzanp:

  • Konbine fich travay miltip oswa liv nan yon liv
  • Konte ak selil sòm ki baze sou background / font koulè
  • Sèl selil Split nan ranje miltip / kolòn pa espas / comma / delimiter
  • Batch Retire espas ki mennen nan selil yo
kte

Say something here...
symbols left.
You are guest ( Sign Up? )
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.
    Henrik · 5 years ago
    Hey

    Great tip. Did [i]almost[/i] all I wanted.

    In the combinde woorkbook, I would have lovede for the worksheet name to contain the name of the original woorkbook, so I know which woorkbook the data comes from.

    The data I'm combining, is from different archives. I have to search for an entry, but don't know which archive it's in.

    So by combining all the data in one file will make it possible for me to search all archives at once.

    But I still need to know, which archive the entry is in.

    Henrik
    • To post as a guest, your comment is unpublished.
      Code · 4 years ago
      For the code to incorporate the file name just do this.

      Sub GetSheets()
      Dim temp As String
      Path = "C:\Users\....\Desktop\Excel combine\"
      Filename = Dir(Path & "*.xlsx")
      Do While Filename ""
      Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
      temp = ActiveWorkbook.Name
      ActiveSheet.Name = temp
      ActiveWorkbook.Sheets(temp).Copy After:=ThisWorkbook.Sheets(1)
      Workbooks(Filename).Close
      Filename = Dir()
      Loop
      End Sub

      Note: this is to only copy the first sheet, it can be tweeked to do all sheets
      • To post as a guest, your comment is unpublished.
        Adriano Marcato · 3 years ago
        It's a great solution indeed. thank you.
        One Problem though, when I execute it like this excel will ask if I want to save alterations before closing (Since the name was changed), and I don't want to do it for every file (around 32 per execution).
        Would there be a way to solve this?
      • To post as a guest, your comment is unpublished.
        LAW · 4 years ago
        How do you incorporate more sheets and how do you specify a different master file to paste all the sheets.
  • To post as a guest, your comment is unpublished.
    Amir · 5 years ago
    Hi!
    Thanks a lot of this file.............
    :roll:
    Best Regard
  • To post as a guest, your comment is unpublished.
    Susie · 5 years ago
    I get a 'runtime error 1004', Copy Method of Worksheet class failed on the line that reads: Sheet.Copy After:=ThisWorkbook.Sheets(1). I'm using Excel 2010.

    Can you assist? Thanks, - Susie
    • To post as a guest, your comment is unpublished.
      Chris F · 2 years ago
      Go to view and unhide "PERSONAL" - it seems to have trouble executing the whole code while the master is hidden.
      You can make the macro native to that workbook, but you'd have to recreate the whole thing every time you wanted to use it
    • To post as a guest, your comment is unpublished.
      Chris F · 2 years ago
      [quote name="Susie"]I get a 'runtime error 1004', Copy Method of Worksheet class failed on the line that reads: Sheet.Copy After:=ThisWorkbook.Sheets(1). I'm using Excel 2010.

      Can you assist? Thanks, - Susie[/quote]
      Had the same problem, it works when I go to view and unhide "PERSONAL", it seems to have trouble accessing this macro with the master hidden.
    • To post as a guest, your comment is unpublished.
      samuel Birch · 2 years ago
      Hey Susie,

      Ive been working on this problem for a while now myself, getting the same error.

      Check to see if the module was created under PERSONAL instead of your active workbook.

      Once I created the module under the correct tree, the below code worked just fine.

      Sub GetSheets_xls()
      Dim Sheet As Worksheet
      Path = "C:\Users\yournamehere\Desktop\Testingfolder\"
      Filename = Dir(Path & "*.xls")
      Do While Filename ""
      Workbooks.Open Filename:=Path & Filename, ReadOnly:=False
      Set Sheet = ActiveWorkbook.Sheets(1)
      Sheet.Copy After:=ThisWorkbook.Sheets(1)
      'Next Sheet
      Workbooks(Filename).Close
      Filename = Dir()
      Loop
      End Sub




      Hope this helps!
      • To post as a guest, your comment is unpublished.
        DS · 1 years ago
        Thanks a lot. Your code worked well.
  • To post as a guest, your comment is unpublished.
    A. Karthi · 5 years ago
    Hi Plz help me out to this below condition.

    I have different workbook which has more than 5 worksheets each in different path. I need to consolidate all worksheets from different workbook into single workbook. Can anyone help me to resolve with macro.TIA!
    • To post as a guest, your comment is unpublished.
      E nic · 2 years ago
      Our office has duplication of data, (i.e.-name, address, city, amount, date signed) from several excel originals and trying to combine the data is going to be a work in progress. How can that be done to eliminate double work and double information entries?
    • To post as a guest, your comment is unpublished.
      Taslima · 2 years ago
      KUTOOLS Awsome solution.

      I need one more help when I create master workbook then cell color of work sheet is changed from original worksheet.

      How can I keep it like original worksheet.
    • To post as a guest, your comment is unpublished.
      Jay Chivo · 5 years ago
      [quote name="A. Karthi"]Hi Plz help me out to this below condition.

      I have different workbook which has more than 5 worksheets each in different path. I need to consolidate all worksheets from different workbook into single workbook. Can anyone help me to resolve with macro.TIA![/quote]

      Please go to download and install the Kutools for Excel, you can quickly get it done. But if you want to use a VBA, it may be too complicated.

      For more information about how to get it done, please visit:http://www.extendoffice.com/product/kutools-for-excel/excel-combine-worksheets-into-one.html