Çalışma sayfalarını veya çalışma kitaplarını bir çalışma sayfasında nasıl birleştirir veya birleştiririm?

Günlük çalışmamızda, Excel'de Kopyala ve Yapıştır komutunu kullanırsanız, verileri analiz etmek için yüzlerce sayfayı veya çalışma kitabını tek bir sayfada birleştiren bir sorunla karşılaşabiliriz. İşte bu eğitimde, bu işi hızlı bir şekilde çözmek için bazı ipuçları vereceğim.

HIZLI NAVİGASYON

Etkin çalışma kitabının tüm sayfalarını VBA ile tek bir sayfaya birleştirme
Düzinelerce sayfayı veya çalışma kitabını tek bir çalışma sayfasına / çalışma kitabına tıklama ile birleştirme
İki tabloyu tek bir tabloyu birleştirmek ve tıklama ile bir sütuna göre güncellemek
Örnek dosyayı indirin

Etkin çalışma kitabının tüm sayfalarını VBA ile tek bir sayfaya birleştirme

Bu bölümde, siz çalıştırırken aktif çalışma kitabının tüm sayfalarını toplamak için yeni bir sayfa oluşturacak bir VBA kodu sağlıyorum.

1. Tüm sayfalarını birleştirmek istediğiniz çalışma kitabını etkinleştirin ve ardından + açmak için anahtarlar Uygulamalar için Microsoft Visual Basic pencere.

2. Açılan pencerede, Ekle > modül yeni bir Modül komut dosyası oluşturmak için.

3. Aşağıdaki kodu kopyalayın ve koda yapıştırın.

Sub Combine()
'UpdatebyExtendoffice
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Combined"
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub

4. Basın F5 tuşuna tıklarsanız, sayfalardaki tüm veriler, tüm sayfaların önüne yerleştirilen Birleştirilmiş adlı yeni bir sayfada birleştirilir.

Ailenizle Eşlik Etmek İçin Maaş Artışı Yapmak ve Çok Zamanınız mı İstiyorsunuz?

Office Tab Şu Anda Çalışan Microsoft Office'te Verimliliğinizi% 50 Artırıyor

İnanılmaz, iki veya daha fazla belgede çalışmak, bir belgede çalışmaktan daha kolay ve hızlıdır.

İyi bilinen tarayıcılarla karşılaştırıldığında, Office Tab'taki sekmeli araç daha güçlü ve daha verimlidir.

Her gün yüzlerce fare tıklamasını ve klavye yazmayı sizin için azaltın, şimdi fare eline veda edin.

Genellikle birden çok belgede çalışıyorsanız, Office Tab sizin için harika bir zaman kazandıracaktır.

30 günlük ücretsiz deneme, Kredi kartına gerek yok.

Detaylı İnceleŞimdi Bedava İndirin

Düzinelerce sayfayı veya çalışma kitabını tek bir çalışma sayfasına / çalışma kitabına tıklama ile birleştirme

VBA ile yalnızca etkin çalışma kitabındaki sayfaları birleştirebilirsiniz, ancak sayfaları çalışma kitapları arasında bir sayfaya veya çalışma kitabına nasıl birleştirebilirsiniz?

Çalışma kitaplarındaki sayfaları tek bir sayfada birleştirin
doc birleştir 18

Çalışma kitaplarındaki sayfaları tek bir çalışma kitabında birleştirin
doc birleştir 3

Bu işi çözmek ve tabaka kombinasyonu ile ilgili diğer gereksinimleri karşılamak için, Birleştirmek işlevi dört kombinasyon senaryosu ile geliştirilmiştir:

Birden çok sayfayı veya çalışma kitabını tek bir sayfada birleştirin
Birden çok sayfayı veya çalışma kitabını tek bir çalışma kitabında birleştirin
Aynı ad sayfalarını tek bir sayfada birleştirin
Sayfalar veya çalışma kitaplarındaki değerleri tek bir sayfada birleştirin

Örnek olarak ikinci seçeneği alır:

Birden çok sayfayı veya çalışma kitabını tek bir çalışma kitabında birleştirin

Sonra ücretsiz kurulum Kutools for Excel, lütfen aşağıdaki işlemleri yapın:

1. Excel'i etkinleştirin, Kutools Artı > Birleştirmek, birleştirmek istediğiniz çalışma kitaplarının kapatılması gerektiğini size hatırlatmak için bir iletişim kutusu açılır. Tıklayın OK Devam etmek.
doc birleştir 4
doc birleştir 5

2. içinde Çalışma sayfalarını birleştirin 1. adım iletişim, kontrol et Çalışma kitaplarından birden çok çalışma sayfasını tek bir çalışma kitabında birleştirin seçeneği. Click Sonraki Sihirbazın sonraki adımına gitmek için.
doc birleştir 6

3. tık Arkadaşlarınızdan > fileto or Dosya birleştirmek istediğiniz çalışma kitaplarını eklemek için Çalışma kitabı listesi bölmesini seçerseniz, içindeki adları kontrol ederek hangi çalışma sayfasının birleştirileceğini belirtebilirsiniz. Çalışma sayfası listesi bölme. Tıklayın Sonraki sihirbazın son adımına gitmek için.
doc birleştir 7

4. Bu adımda, ihtiyaç duyduğunuz ayarları belirtin. Sonra tıklayın Bitiş.
doc birleştir 8

5. Birleştirilmiş çalışma kitabını yerleştirmek için bir klasör seçmeniz için bir pencere açılır ve ardından İndirim.
doc birleştir 9

Şimdi çalışma kitapları tek bir çalışma kitabında birleştirildi. Ve tüm sayfaların önünde, her sayfa için sayfalar ve bağlantılar hakkında bazı bilgileri listeleyen Kutools for Excel adlı bir ana sayfa da oluşturulur.
doc birleştir 10

Demo: Sayfaları / çalışma kitaplarını tek bir sayfaya veya çalışma kitabına birleştirin

Kutools for Excel: Excel'deki karmaşık görevleri birkaç tıklamayla basitleştiren 200 + kullanışlı kullanışlı araçlar.

Şimdi Fare Eline ve Servikal Spondiloza Veda Edin

Kutools for Excel'in 300 gelişmiş aracı,% 80 Excel görevini saniyeler içinde çözerek sizi binlerce fare tıklamasından kurtarır.

1500 çalışma senaryosuyla kolayca başa çıkın, çözüm aramak için zaman kaybetmenize gerek yok, hayatınızın tadını çıkarmak için çok zamanınız olsun.

Elbette siz de dahil olmak üzere her gün 80'den fazla son derece etkili insan için% 110000 üretkenliği artırın.

Artık acı veren formüller ve VBA tarafından eziyet edilmeyecek, beyninize bir dinlenme ve neşeli bir çalışma ortamı verin.

Tam özelliklere sahip 30 günlük ücretsiz deneme, sebepsiz 30 günlük para iadesi.

Daha İyi Bir Vücut Daha İyi Bir Yaşam Yaratır.

İki tabloyu tek bir tabloyu birleştirmek ve tıklama ile bir sütuna göre güncellemek

İki tabloyu tek bir tabloda birleştirmek ve aşağıda gösterilen ekran görüntüsü gibi bir sütuna göre verileri güncellemek istiyorsanız, deneyebilirsiniz. Tablolar Birleştirme yarar Kutools for Excel.
doc birleştir 11

Kutools for Excel: karmaşık görevleri Excel'de birkaç tıklamayla basitleştirmek için 200'den fazla kullanışlı Excel eklentisi

Sonra ücretsiz kurulum Kutools for Excel, lütfen aşağıdaki işlemleri yapın:

1. tık Kutools Artı > Tablolar Birleştirme etkinleştirmek Tablolar Birleştirme sihirbazı.
doc birleştir 12

Kutools for Excel

2. Sihirbazın 1. adımında, ana tabloyu ve arama tablosu aralıklarını ayrı ayrı seçmeniz gerekir. Sonra tıklayın Sonraki.
doc birleştir 13

3. Ana tablodaki verileri temel alarak güncellemek istediğiniz anahtar sütununu işaretleyin. Tıklayın Sonraki.
doc birleştir 14

4. Ardından, arama tablosuna göre verileri güncellemek istediğiniz ana tablodaki sütunları kontrol edin. Tıklayın Sonraki.
doc birleştir 15

5. Sihirbazın 4. adımında, arama tablosundan ana tabloya eklemek istediğiniz sütunları işaretleyin. Tıklayın Sonraki.
doc birleştir 16

6. Sihirbazın son adımında, ihtiyaç duyduğunuz şekilde ayar seçeneklerini belirtin. Sonra tıklayın Bitiş.
doc birleştir 17

Şimdi ana tablo verileri güncelledi ve arama tablosuna göre yeni veriler eklendi.

Demo: Sayfaları / çalışma kitaplarını tek bir sayfaya veya çalışma kitabına birleştirin

Kutools for Excel: Excel'deki karmaşık görevleri birkaç tıklamayla basitleştiren 200 + kullanışlı kullanışlı araçlar.

Örnek Dosyayı İndirin

Önerilen Üretkenlik Araçları

Office Sekmesi - Microsoft Office 2019-2003 ve Office 365'te Belgeleri Sekmeli Tarama, Düzenleme ve Yönetme

Excel 2019-2007'yi Hızlandırmak için Profesyonel Bir Eklenti, saat görevlerini saniyelerle küçültün

Bu eklenti düzinelerce profesyonel grubu içerir; 300'den fazla seçenek, Excel'deki günlük görevlerinizin çoğunu otomatikleştirecek ve üretkenliğinizi en az% 50 artıracaktır. Tek tıklama seçenekleri ve toplu dönüştürme grupları gibi.
Kutools for Excel ile şimdi kendinizi hızlandırma şansınız!

166 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.
AJIT DIXIT · 1 years ago
In combining shhets I want to add one extra column in combined sheet as ShhetName
Can you suggest ?
- Reply
- To post as a guest, your comment is unpublished.
  Sunny · 1 years ago
  
  The Combine function of Kutools for Excel can help you to insert sheet name in a new row of each combined range in the combined sheet by checking First row of each range (new row) in Insert worksheet information section in the last step of the Combine.
  
  Reply
To post as a guest, your comment is unpublished.
Eben · 1 years ago
Hi - this function works well except my file has more than 900 sheets, and when I change the maximum number of rows (Loc 16, "A65536") to a higher number, the macro doesn't work. Is there a way to increase the number of rows that can be shown on the combine sheet?
- Reply
To post as a guest, your comment is unpublished.
Charlotte · 1 years ago
The VBA code works fine but I want the data to be merged horizontally(side by side).
Hope you can help.
- Reply
To post as a guest, your comment is unpublished.
Daniel · 1 years ago
Hi,

Great Code and for the most part it works fantastically well.

Could I suggest creating a written tutorial for each line of code. I'd love to be able to play around with it to suit my Company's needs.

In the meantime however could you talk me through problems that would hinder this code from copying all data from each Sheet? I've noticed some sheets in my workbook aren't being copied at all.

To give you some context I'm dealing with roughly 330 sheets and they hold from 50 to 500 lines in each.

looking forward to hearing from you.
- Reply
- To post as a guest, your comment is unpublished.
  Sunny · 1 years ago
  
  Hi, Daniel, I do not understand your question as you describe it not clearly, but if you have the Combine function in Excel, it can solve most of your question about combining sheets.
  
  Reply
To post as a guest, your comment is unpublished.
HS · 1 years ago
Hi,

Is it possible to only copy a particular range of cells for example A:4:FU38 from each sheet?

Also in the Combined sheet where the data is copied to, can the tab names from where the data is copied included in column A to enable lookups or to pivot the data?

Finally, can i specify the list of worksheets from which to copy the data across or maybe i can specify something like copy all worksheets to the left of a particular worksheet in the file.

Thanks in advance for your help
- Reply
- To post as a guest, your comment is unpublished.
  Sunny · 1 years ago
  
  Hi, HS, you mean to combine same ranges from sheets into one sheet, it that right? If so, this article may help you: https://www.extendoffice.com/documents/excel/2754-excel-combine-ranges-from-different-worksheets-into-one.html#a3
  
  Reply
To post as a guest, your comment is unpublished.
Dennis · 2 years ago
This VBA code is an absolute lifesaver. Thank you!!
- Reply
To post as a guest, your comment is unpublished.
martinoreilly · 2 years ago
Hi. This code works well but I have the same problem as adj. The 1st row form each tab doesn't appear in the combined tab - except he first tab. Thanks
- Reply
- To post as a guest, your comment is unpublished.
  Pete · 1 years ago
  
  Just delete "Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select" from the code.
  
  If you want to merge all rows from all worksheets, just use the code:
  
  Sub Combine()
  'UpdatebyExtendoffice
  Dim J As Integer
  On Error Resume Next
  Sheets(1).Select
  Worksheets.Add
  Sheets(1).Name = "Combined"
  Sheets(2).Activate
  Range("A1").EntireRow.Select
  Selection.Copy Destination:=Sheets(1).Range("A1")
  For J = 2 To Sheets.Count
  Sheets(J).Activate
  Range("A1").Select
  Selection.CurrentRegion.Select
  Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
  Next
  End Sub
  
  Reply
- To post as a guest, your comment is unpublished.
  Sunny · 2 years ago
  
  Yes, the code only can keep first row of the first tab, because it is used to consolidate, if you want to keep all contents of each sheet, you can try Combine utility of Kutools for Excel, it is free fior 60 days
  
  Reply
To post as a guest, your comment is unpublished.
elok · 2 years ago
i had a problem when I try to combine all same name worksheet to one worksheet, it works but some value are missing. so what should i do?
- Reply
- To post as a guest, your comment is unpublished.
  Sunny · 2 years ago
  
  Hello, elok, did the problem appear while you applying Combine function? If so, please go to contact us support@extendoffice.com to describe the problem with more details, our support will handle the problem for you.
  
  Reply
To post as a guest, your comment is unpublished.
Andrew · 2 years ago
Thanks that macro saved my day. I had over 40 sheets of data to combine into one and although my computer bogged for a minute or two, it completed without issue. The only modification I made was I tweaked the macro to start with A2 instead of A1 since A1 was my column headers.
- Reply
- To post as a guest, your comment is unpublished.
  Sunny · 2 years ago
  
  Thanks for your support, Andrew, I am glad that the macro can help you.
  
  Reply
To post as a guest, your comment is unpublished.
thangaraj571@gmail.com · 2 years ago
hi I would like combine all the worksheets in single sheet where i need data of column A of all worksheets and I need the worksheet names in column B
- Reply
- To post as a guest, your comment is unpublished.
  Sunny · 2 years ago
  
  Sorry, Thangaraj R, I do not undertsand your problem clearly, could you upload a screenshot to describe your quetion with more detials?
  
  Reply
To post as a guest, your comment is unpublished.
Gio · 2 years ago
Hi all,
I need to combine only sheets with identical heading, ship other sheets.

thanks
- Reply
- To post as a guest, your comment is unpublished.
  Sunny · 2 years ago
  
  Sorry, I do not understand your problem clearly, do you mean to combine sheets with same column header? If so, this article may help you.
  
  https://www.extendoffice.com/documents/excel/3252-excel-merge-excel-files-same-header.html
  
  Reply
To post as a guest, your comment is unpublished.
arul.annalect@gmail.com · 3 years ago
Hi all,

I have used the below mentioned code, In my data some blank Rows is there for each tab. Data is not coping after the blank rows. How to combine the all data after the blank rows. Please advise.

Sub Combine()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Combined"
Sheets(2).Activate
Range("A16").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A17").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub

Thanks
Arul
- Reply
- To post as a guest, your comment is unpublished.
  Sunny · 2 years ago
  
  You can try below code, it will merge all data and skip blank rows.
  
  Sub CombineForm()
  Dim xI, xMax As Integer
  Dim xCWS, xWS As Worksheet
  Dim xObjRRange, xObjCRange, xObjSR As Range
  On Error Resume Next
  Sheets(1).Select
  Set xCWS = Worksheets.Add
  xCWS.Name = "Combined"
  Sheets(2).Activate
  Range("A1").EntireRow.Select
  Selection.Copy Destination:=xCWS.Range("A1")
  For xI = 2 To Sheets.Count
  Set xWS = Sheets(xI)
  xWS.Activate
  Set xObjRRange = xWS.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
  Set xObjCRange = xWS.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
  Set xObjSR = Range(Cells(1, 1), Cells(xObjRRange.Row, xObjCRange.Column))
  xObjSR.Select
  Selection.Copy Destination:=xCWS.Range("A65536").End(xlUp)(2)
  Next
  xMax = xCWS.Range("a65536").End(3).Row
  xCWS.Range("a1:a" & xMax).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  End Sub
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  arul.annalect@gmail.com · 2 years ago
  
  Thank you, Sunny! This coding is working fine..
  
  But i want to combine all the sheets from row no: 16. Could you help us to update the coding.
  
  Thanks
  Arul
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Sunny · 2 years ago
  
  The below code will combine sheets from the second row, you can change the number 2 (xStart = 2) to other number for you need.
  
  Sub CombineForm()
  Dim xStart, xFNum, xIntCFMax, xIntRRg, xIntCRg As Integer
  Dim xCFWS, xWS As Worksheet
  Dim xObjSR As Range
  Dim xStrName As String
  On Error Resume Next
  Application.ScreenUpdating = False
  xStart = 2
  xStrName = "CombinedForm"
  Set xWS = Sheets(xStrName)
  If xWS Is Nothing Then
  xWS.Delete
  End If
  Set xCFWS = Worksheets.Add
  xCFWS.Name = xStrName
  
  For xFNum = 1 To Sheets.Count
  Set xWS = Sheets(xFNum)
  If xWS.Name <> xStrName Then
  xWS.Activate
  xIntRRg = xWS.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  xIntCRg = xWS.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
  If xStart <= xIntRRg Then
  Set xObjSR = Range(Cells(xStart, 1), Cells(xIntRRg, xIntCRg))
  xObjSR.Select
  Selection.Copy Destination:=xCFWS.Range("A65536").End(xlUp)(2)
  End If
  End If
  Next
  Application.ScreenUpdating = True
  End Sub
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  arul · 2 years ago
  
  Thank you, Sunny!
  
  I want to combine the sheet from row no:16. Could you help me to update the coding.
  
  Thanks
  Arul
  
  Reply
To post as a guest, your comment is unpublished.
neno · 3 years ago
My headings start on row 26, the first 25 rows are occupied with the parameters I used to generate my reports, it would take a long time to delete these since there are many reports generated. Using the VBA code above, how could I configure this code to exclude the first 25 rows, capture the headings on row 26 and combine all the data under the headings (row 27 onwards) for each worksheet?
- Reply
- To post as a guest, your comment is unpublished.
  Bruno G. · 3 years ago
  
  Change all the "A1" in the code to "A26". Save your data before trying this change, just in case!
  
  Reply
To post as a guest, your comment is unpublished.
Tina · 3 years ago
Hi.. The VBA code works fine. Thank you! But can you please help on how I can edit the code so that only 2 sheets will be combined? I have 5 sheets in my workbook but I only need to combine 2 of them.
- Reply
- To post as a guest, your comment is unpublished.
  Nigah · 3 years ago
  
  Just save as the current workbook with a new name and delete other three worksheets which you don't want to combine, now you have only your desired two sheets for VBA code execution.
  
  Reply
To post as a guest, your comment is unpublished.
deepak · 3 years ago
hello everyone

I want to merged more workbook data in one so give me suggest
- Reply
To post as a guest, your comment is unpublished.
· 3 years ago
Hi everyone,

First of all I have to tell that I have no experience with Macro (VBA Codes). However what I need is related to this. Maybe you guys could help me with it.

I have a workbook and in this workbook there are 10 worksheets. The first 9 Sheets have the same order of the coloumns of titles and in these columns there are names, dates, percentages of Project Status, comments to Projects etc.. As I said the columns have the same order just the name of the worksheets (for different Teams in the Organisation) are different.

In Addition to this I have to merge all the worksheets and have them in another sheet which is called "Übersicht" (Overview). However there is a different column in the sheet and it's between "Nr." and "Thema" columns (which are in A1 and A2 in all the 9 Sheets) and this different column called "Kategorie" (in A2 in Übersicht-Overwiev sheet). As this column is between These the order is like this "Nr. (A1), Kategorie (A2) and Thema (A3).....".So this category column (Kategorie) should be empty except this all the Information should be merged into this sheet. And also when there is a Change or update in any worksheet, the Information in "Übersicht" (Overview) sheet needs to update by itself. How can I do this?

P.S.: Every sheet has different filled rows, some 30, some 13, some 5 etc. And the Teams which are responsible for the Sheets can add or delete some rows (in each row there is different Information for different Projects). This also means the number of rows can increase or decrease.

I hope I explained it well. Thanks a lot in advance!

I wish you merry Christmas and a happy new year!

oduff
- Reply
To post as a guest, your comment is unpublished.
hk · 3 years ago
The VBA code works fine but I want the data to be merged horizontally(side by side).
Hope you can help.
- Reply
To post as a guest, your comment is unpublished.
Jeanette · 3 years ago
THANK YOU for the VB Code. Amazing. Worked perfectly. I had 72 worksheets to combine into one. It took a second and it was done. YOU ROCK.
- Reply
To post as a guest, your comment is unpublished.
Yohani · 3 years ago
Excellent code! Worked very well!
- Reply
To post as a guest, your comment is unpublished.
Jonas · 3 years ago
Hello!
I need your support. I have three sheets (A, B, C) into a worksheet. the sheet contain information on individuals.
Sheet A: contains all individuals considered as ID with information (age, education, etc...)
Sheets B et C: contain some individuals (ID) with information (Organisation, income, etc...)
So I want to combine B and C to A to get all information from A,B, C together.

Jonas
- Reply
To post as a guest, your comment is unpublished.
Jennifer · 3 years ago
Thank you so very much! The code worked perfectly! I appreciate the instruction so very much!!!
- Reply
To post as a guest, your comment is unpublished.
Gary · 3 years ago
Hello, this worked fine at first, but now I see that not all rows from all worksheets are appended into the combined sheet. Please help!
- Reply
To post as a guest, your comment is unpublished.
Suzana · 3 years ago
Oh dear! Thank you so MUCH. Worked PERFECTLY with the Code. Had trouble and was thinking how to resolve this and then...just in a second! Thank you a lot. God bless you!
- Reply
To post as a guest, your comment is unpublished.
adj · 3 years ago
This code works for the most part - however, the first line of each sheet (except for the first sheet) is not extracted into the combined sheet. Which piece of the code should be modified to ensure extraction of the first line?
- Reply
To post as a guest, your comment is unpublished.
mario · 4 years ago
You rock guys!
- Reply
To post as a guest, your comment is unpublished.
s · 4 years ago
You guys are the best!!! You totally saved me and I look like a super star
- Reply
To post as a guest, your comment is unpublished.
Daniel · 4 years ago
THANK YOU SO MUCH!!!!!
- Reply
To post as a guest, your comment is unpublished.
Mohd · 4 years ago
How to merge multiple workbooks with multiple worksheets into one workbook?
- Reply
To post as a guest, your comment is unpublished.
Mohd · 4 years ago
[quote name="John"], how to merge multiple workbooks with multiple worksheets into one workbook? appriciate if you could reply.
- Reply
To post as a guest, your comment is unpublished.
John · 4 years ago
This is amazing. Thank you so much!
- Reply
To post as a guest, your comment is unpublished.
Jens · 4 years ago
When installed, Excel 365 gives a memory error and nothing works.
I had to uninstall kutools to be able to work again :-(
- Reply
To post as a guest, your comment is unpublished.
Rich Thorpe · 4 years ago
Thanks for the help on combining worksheets. Saved me hours of mind numbing work!
- Reply
To post as a guest, your comment is unpublished.
Ryan · 4 years ago
how do i get the macro to insert column "a" and paste the name of the workbook the data came from in the added column
- Reply
To post as a guest, your comment is unpublished.
astu · 4 years ago
it works so well for my case.
million thanks for sharing!!!!
- Reply
To post as a guest, your comment is unpublished.
Raymundo · 4 years ago
It works, Thaaaanks!
- Reply
To post as a guest, your comment is unpublished.
Charul · 4 years ago
can you please explain me the meaning of [b]Selection.Copy Destination:=Sheets(1).Range(" A65536").End(xlUp)(2)[/b] line?What is A65536 used for?
- Reply
To post as a guest, your comment is unpublished.
Yogesh · 4 years ago
How to overwrite the updated data over existing data
- Reply
To post as a guest, your comment is unpublished.
Diane · 4 years ago
Attempted VBA Code. Have 5 worksheets in 2 workbook that need to be combined. All same structure. VBA code will only combine 2 of the worksheets rather than all 5. What do I change in the VBA code to make combine all 5 worksheets? Data in worksheets is all under the 65K .xls row limit and I am using Excel 2016.
- Reply
To post as a guest, your comment is unpublished.
Elvedin · 4 years ago
I receive spreadsheets where table header is in starts in row 3. So I need to go to each worksheet to delete columns 1 and 2 for the macro to work. Can I adjust the code, so it would start in A3 instead of A1
- Reply
To post as a guest, your comment is unpublished.
boopathi raj · 4 years ago
Very much Thanks ... ... :lol:
- Reply
To post as a guest, your comment is unpublished.
CRG · 4 years ago
[quote name="Nat"]When using the VBA providied above, is there a way for the combined sheet to automatically update when the sheets that is combined are added to it, so it is always running?

Thanks![/quote]
Would like to know the answer to this. Thanks for the great Macro!
- Reply
To post as a guest, your comment is unpublished.
angry_banana · 4 years ago
Hi! Can you help me with this problem.
I have an excel doc with 12 sheets. In each sheet is a table. With a differend cells format (width & height).
I have to copy all sheets in one and save each cells format. Thanks!
- Reply
To post as a guest, your comment is unpublished.
Kayla · 4 years ago
Your macro worked BUT it added each sheet multiple times. i have no idea why...I had 4 sheets with invoice lists (1 septembers, 2 octobers Ect.)
and for some reason it added my september back on 2 more times and added eachother one underneath 3x each....
I did not modify or delete anything from the macro just simply copied pasted and ran it...
- Reply
To post as a guest, your comment is unpublished.
GeneA · 4 years ago
For those people who want to update data they already merged you can use this code:

Sub Combine()
Dim J As Integer
On Error Resume Next
'Delete the sheet "Combined" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("Combined").Delete
On Error GoTo 0
Application.DisplayAlerts = True

Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Combined"
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub

----------------------------------

Hope that helps!!!!
- Reply
To post as a guest, your comment is unpublished.
Kabir Humayun · 4 years ago
How can i collect specific cells data from around 400 sheet... where all sheet are same format.

for ex. I want to get data from A3, A4, A29, B30, C34 and store on a new sheet.

thanks in advanced.
- Reply
To post as a guest, your comment is unpublished.
Kabir Humayun · 4 years ago
1st thanks for the code,
I modified the code following way,
Sub Combine()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Combined"
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(0, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(3, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("B65536").End(xlUp)(2)
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(115, 5).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("E65536").End(xlUp)(2)
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(116, 5).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("F65536").End(xlUp)(2)

Next
End Sub

When i tried with this code only around 5/10 sheet, it works fine, but when i tried this code for around 400 sheets then got 2 wrong value and after 255 row no data come for only one column.

Another columns data are okey and came.

any idea, basically i need to call data (from some specific cell) from around 400 sheets any idea ?
- Reply
To post as a guest, your comment is unpublished.
Chandra Mouli · 4 years ago
Excellent Job ....buddy u have refine my 1 hour ....
THANKS
- Reply
To post as a guest, your comment is unpublished.
Martin · 4 years ago
When I try to use this VBA, it caps out at 79,160 lines (including header). Any suggestions on how to tweak the code or could it be the data in the spreadsheets.
- Reply
To post as a guest, your comment is unpublished.
Lloyd · 4 years ago
I am using the VBA below with great effect with all the sheets in workbook but now I am trying to exclude one sheet name " OAL Index" and just cant seemed to get it right. It keeps selecting all the sheets

Any points to the right direction would be appreciated.

Sub Combine()
Dim J As Integer

On Error Resume Next
Sheets(1).Select
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "Combined"

' copy headings
Sheets(2).Activate
Range("A2").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A2")

' work through sheets

For J = 2 To Sheets.Count ' from sheet 2 to last sheet
If Sheets(J).Name "OAL Index" Then Sheets(J).Select Replace:=False
Sheets(J).Activate ' make the sheet active
Sheets(J).Select
Range("A1").Select
Selection.CurrentRegion.Select ' select all cells in this sheets

' select all lines except title
Selection.Offset(2, 0).Resize(Selection.Rows.Count - 1).Select

' copy cells selected in the new sheet on last line
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub

Thanks All
- Reply
To post as a guest, your comment is unpublished.
Jennifer · 4 years ago
This worked great, saved me a lot of time, but it only merged up to column J and I need it to go up to column T. Can someone please help??? This is for my job!!
Please help me not look dumb in front of my boss, thanks
- Reply

Hesabınıza Giriş

Parola Sıfırlama