Skip to main content

Conas iliomad leabhar oibre a chur le chéile i máistirleabhar oibre amháin in Excel?

An raibh tú riamh i bhfostú nuair a chaithfidh tú iliomad leabhar oibre a chur le chéile i máistirleabhar oibre in Excel? Is é an rud is uafásaí ná go bhfuil iliomad bileoga oibre sna leabhair oibre a chaithfidh tú a chur le chéile. Agus conas na bileoga oibre sonraithe de iliomad leabhar oibre a chomhcheangal in aon leabhar oibre amháin? Taispeánann an rang teagaisc seo roinnt modhanna úsáideacha chun cabhrú leat na fadhbanna a réiteach céim ar chéim.


Comhcheangail iliomad leabhar oibre Isteach i leabhar oibre amháin le feidhm Bog nó Cóipeáil

Mura bhfuil ach cúpla leabhar oibre le chéile, is féidir leat an t-ordú Bog nó Cóipeáil a úsáid chun bileoga oibre a bhogadh nó a chóipeáil de láimh ón mbunleabhar oibre go dtí an máistirleabhar oibre.

1. Oscail na leabhair oibre a chumascfaidh tú le máistirleabhar oibre.

2. Roghnaigh na bileoga oibre sa bhunleabhar oibre a bhogfaidh tú nó a chóipeáilfidh tú chuig an máistirleabhar oibre.

Nótaí:

1). Is féidir leat iliomad bileoga oibre neamh-in aice láimhe a roghnú leis an Ctrl eochair agus cliceáil na cluaisíní bileog ceann ar cheann.

2). Chun iliomad bileoga oibre cóngaracha a roghnú, cliceáil ar an gcéad chluaisín le do thoil, coinnigh an Shift eochair, agus ansin cliceáil ar an táb deireanach bileog chun iad uile a roghnú.

3). Is féidir leat cliceáil ar dheis ar aon chluaisín bileog, cliceáil ar Roghnaigh Gach Bileog ón roghchlár comhthéacs chun gach bileog oibre sa leabhar oibre a roghnú ag an am céanna.

3. Tar éis duit na bileoga oibre riachtanacha a roghnú, cliceáil ar dheis ar an táb bileog, agus ansin cliceáil Bog nó Cóipeáil ón roghchlár comhthéacs. Féach an pictiúr:

4. Ansin an Bog nó Cóipeáil pops dialóg suas, sa A chur in áirithe anuas, roghnaigh an máistirleabhar oibre a bhogfaidh tú nó cóipeálfaidh tú bileoga oibre ann. Roghnaigh bogadh chun deiridh sa Roimh an mbileog bosca, seiceáil an Cruthaigh cóip bosca, agus ar deireadh cliceáil ar an OK cnaipe.

Ansin is féidir leat bileoga oibre a fheiceáil in dhá leabhar oibre le chéile i gceann amháin. Déan na céimeanna thuas arís le do thoil chun bileoga oibre a aistriú ó leabhair oibre eile go dtí an máistirleabhar oibre.


Comhcheangail iliomad leabhar oibre nó bileoga sonraithe leabhar oibre le máistirleabhar oibre le VBA

Má tá iliomad leabhar oibre ann is gá a chumasc i gceann amháin, is féidir leat na cóid VBA seo a leanas a chur i bhfeidhm chun é a bhaint amach go tapa. Déan mar a leanas le do thoil.

1. Cuir gach leabhar oibre is mian leat a chur le chéile i gceann amháin faoin eolaire céanna.

2. Seoladh comhad Excel (beidh an leabhar oibre seo mar an máistirleabhar oibre).

3. Brúigh an Eile + F11 eochracha a oscailt Microsoft Visual Basic d’iarratais fuinneog. Sa Microsoft Visual Basic d’iarratais fuinneog, cliceáil Ionsáigh > Modúil, ansin cóipeáil thíos cód VBA isteach i bhfuinneog an Mhodúil.

Cód VBA 1: Comhcheangail iliomad leabhar oibre Excel i gceann amháin

Sub GetSheets()
'Updated by Extendoffice 2019/2/20
Path = "C:\Users\dt\Desktop\dt kte\"
Filename = Dir(Path & "*.xlsx")
  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ótaí:

1. Coinneoidh an cód VBA thuas ainmneacha bileoga na mbunleabhar oibre tar éis iad a chumasc.

2. Más mian leat idirdhealú a dhéanamh ar na bileoga oibre sa mháistirleabhar oibre as ar tháinig tú, déan an cód VBA 2 thíos a chur i bhfeidhm.

3. Mura dteastaíonn uait ach bileoga oibre sonraithe de na leabhair oibre a chomhcheangal i máistirleabhar oibre, is féidir leis an gcód 3 VBA thíos cabhrú leat.

I gcóid VBA, “C: \ Úsáideoirí \ DT168 \ Deisce \ KTE \"Is é cosán an fhillteáin. I gcód VBA 3, "Bileog1, Bileog3"is é seo bileoga oibre sonraithe na leabhar oibre a chomhcheanglóidh tú le máistirleabhar oibre. Is féidir leat iad a athrú bunaithe ar do chuid riachtanas.

Cód VBA 2: Cumaisc Leabhair Oibre i gceann amháin (ainmneofar gach bileog oibre le réimír a hainm comhaid bunaidh):

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

Cód VBA 3: Bileoga oibre sonraithe de leabhair oibre a chumasc le máistirleabhar oibre:

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. Brúigh an F5 eochair chun an cód a rith. Ansin cuirtear gach bileog oibre nó bileog oibre shonraithe de na leabhair oibre san fhillteán áirithe le chéile le máistirleabhar oibre ag an am céanna.


Comhcheangail go héasca iliomad leabhar oibre nó bileoga sonraithe leabhar oibre le leabhar oibre amháin

Ar ámharaí an tsaoil, tá an Chomhcheangail leabhar oibre fóntais de Kutools le haghaidh Excel déanann sé i bhfad níos éasca iliomad leabhar oibre a chumasc i gceann amháin. Feicfimid conas an fheidhm seo a chur ag obair chun iliomad leabhar oibre a chur le chéile.

Roimh iarratas a dhéanamh Kutools le haghaidh Excel, Le do thoil é a íoslódáil agus a shuiteáil ar dtús.

1. Cruthaigh leabhar oibre nua agus cliceáil Kutools Plus > Chomhcheangail. Ansin pops dialóg le cur i gcuimhne duit gur chóir gach leabhar oibre comhcheangailte a shábháil agus nach féidir an ghné a chur i bhfeidhm ar leabhair oibre faoi chosaint, cliceáil le do thoil OK cnaipe.

2. Sa Comhcheangail Bileoga Oibre draoi, roghnaigh Comhcheangail iliomad bileoga oibre ó leabhair oibre in aon leabhar oibre amháin rogha, agus ansin cliceáil ar an Ar Aghaidh cnaipe. Féach an pictiúr:

3. Sa Comhcheangail Bileoga Oibre - Céim 2 de 3 bosca dialóige, cliceáil an Cuir > Comhad or Fillteán chun na comhaid Excel a chur leis, cumascfaidh tú isteach i gceann amháin iad. Tar éis duit na comhaid Excel a chur leis, cliceáil an Críochnaigh cnaipe agus roghnaigh fillteán chun an máistirleabhar oibre a shábháil. Féach an pictiúr:

Anois déantar gach leabhar oibre a chumasc i gceann amháin.

I gcomparáid leis an dá mhodh thuas, Kutools le haghaidh Excel tá na buntáistí seo a leanas aige:

  • 1) Tá gach leabhar oibre agus bileog oibre liostaithe sa bhosca dialóige;
  • 2) Maidir leis na bileoga oibre is mian leat a eisiamh ó chumasc, ní gá ach iad a dhísheiceáil;
  • 3) Déantar bileoga oibre bán a eisiamh go huathoibríoch;
  • 4) Cuirfear ainm bunaidh an chomhaid mar réimír le hainm na bileoige tar éis é a chumasc;
  • Le haghaidh tuilleadh feidhmeanna den ghné seo, tabhair cuairt anseo.

  Más mian leat triail saor in aisce (30 lá) a bheith agat ar an bhfóntas seo, cliceáil le do thoil chun é a íoslódáil, agus ansin téigh chun an oibríocht a chur i bhfeidhm de réir na gcéimeanna thuas.


Kutools le haghaidh Excel - Cabhraíonn sé leat an obair a chríochnú i gcónaí roimh an am, Bíodh níos mó ama agat chun taitneamh a bhaint as an saol
An minic a bhíonn tú ag imirt teacht suas leis an obair, easpa ama le caitheamh ort féin agus ag do mhuintir?  Kutools le haghaidh Excel cabhrú leat chun déileáil leis 80% Excel puzzles agus feabhas a chur ar éifeachtacht oibre 80%, tabhair níos mó ama duit aire a thabhairt do theaghlach agus taitneamh a bhaint as an saol.
300 uirlis chun cinn le haghaidh 1500 cás oibre, déan do phost an oiread sin níos éasca ná riamh.
Ní gá foirmlí agus cóid VBA a chur de ghlanmheabhair a thuilleadh, tabhair sosa d’inchinn as seo amach.
Is féidir oibríochtaí casta agus arís agus arís eile a phróiseáil aon-uaire i soicindí.
Laghdaigh na mílte oibríochtaí méarchláir & luch gach lá, slán a fhágáil le galair cheirde anois.
Bí i do shaineolaí Excel i gceann 3 nóiméad, cabhrú leat aitheantas tapa a fháil agus ardú céime pá a fháil.
110,000 duine an-éifeachtach agus 300+ rogha cuideachtaí a bhfuil cáil dhomhanda orthu.
Déan do $ 39.0 ar fiú níos mó ná $ 4000.0 oiliúint daoine eile.
Triail saor in aisce gné iomlán 30 lá. Ráthaíocht Airgead Ar Ais 60 Lá gan chúis.

Comments (146)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I have one workbook with 100+ sheets, I want to move all 100+ sheets into another workbook in a single sheet.
This comment was minimized by the moderator on the site
I had to read throught the comments to find suggestions that worked for my application of the VBA CODE 2, but I managed to get it to work doing the following things:
1. make sure to change "C:\Users\DT168\Desktop\KTE\" to your own directory to wherever you have your files are located. don't forget the extra "\" at the end!2. my spreadsheets were extension ".xls", so I deleted the extra "x" in this line, like so: xStrFName = Dir(xStrPath & "*.xlsx")3. I placed all my spreadsheets in a single folder, and only those files were the contents of that folder, the target macro enabled spreadsheet where this vba was running from was saved outside of this folder (hopefully this makes sense).
one thing that I didn't want to mess with though is I only needed to merge the 1st tab of each spreadsheet, but I didn't want to mess with the code so if each workbook you want to merge into one has multiple tabs, this code will grab all of the tabs on each workbook and place them in your target spreadsheet, I had to manually delete all the tabs I didn't want.
if the author of this vba could reply to me, how do you change the code to just copy the 1st tab as opposed to all the tabs?
thank you!
This comment was minimized by the moderator on the site
hi I want a change. If the the sheet name is same then the data should be appended in the same name sheet rather than adding a sheet. for example if i have 10 files with jan, feb, mar same sheetnames. then result should be 1 file having jan, feb, mar only 3 sheets with the data of all 10 files. thanks
This comment was minimized by the moderator on the site
Good morning,

Basically I have to copy the values of another file example c: \ test.xlsx (sheet name "date"):

I have to copy the values from A2: T20


And I have to paste in another Extract.xlsx file on the “Extracts” folder on A2.


PLEASE NOTE: You must run vba when opening the file.
This comment was minimized by the moderator on the site
Hello! I need to merge multiple files into one, that are password protected. All source files use the same password. What changes are needed to the first VBA script to merge the files without having to enter the password each time?
This comment was minimized by the moderator on the site
Hello any one can help me, I want to combine sheet 2 only from 5 different sheet, can you script for me.
This comment was minimized by the moderator on the site
you can use Array function to copy the multiple sheets to combine in one file
Sheets(Array("Sheet1","Sheet2")).copy
This comment was minimized by the moderator on the site
hai sir i want know code for copying multiple sheets in one excel to multiple excels
This comment was minimized by the moderator on the site
Sheets(Array("Sheet1","Sheet2")).copy
This comment was minimized by the moderator on the site
hai sir i want know code for copying multiple sheets in one excel to multiple excels
This comment was minimized by the moderator on the site
how to use VBA code 1 to amend and make it runs to combine all the .xlsx files into one excel file and each excel spreedsheet tab in the combined excel file should be named as original file name.thanks
This comment was minimized by the moderator on the site
What part of VBA code 3 specifies the name of the worksheet to be copied?
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations