Tổng hợp dữ liệu từ nhiều sheet khác nhau (1 người xem)

[lohado]

Em có 1 bảng quản lý các mặt hàng trong kho. Vì nó quá nhiều sheet (Khoảng 500 sheet)nên e rất khó trong việc sử dùng hàm Vlookup đê tổng hợp dữ liệu lại.
Nhờ các thầy cô, anh chị, các bạn và các cao thủ viết giúp e 1 đoạn code VBA để e giải quyết được công việc này. file ví dụ đính kèm.
Em xin chân thành cảm ơn !

 

[SA_DQ]

Bạn chạy macro này để kiểm chứng kết quả

Option Explicit
Sub gpeTH()
 Dim Sh As Worksheet
 
 Sheets("TongHop").Select
 [b6].CurrentRegion.Offset(1, 1).ClearContents
 For Each Sh In ThisWorkbook.Worksheets
    If Sh.Name <> "TongHop" Then
        With [B65500].End(xlUp).Offset(1)
            .Value = Sh.Name
            .Offset(, 1).Value = Sh.Columns("H:H").SpecialCells(xlCellTypeFormulas, 3).Value
            .Offset(, 2).Value = Sh.Columns("I:I").SpecialCells(xlCellTypeFormulas, 3).Value
        End With
    End If
 Next Sh
End Sub

Theo mình để trang 'TongHop' thêm diêm dúa, ta tiến hành các công đoạn bổ trợ sau:

Tại [A] ta áp công thức: =IF(B6="","",1)
Tại ô dưới liền kề ta áp CT: =IF(B7="","",A6+1)

Sau đó bạn dùng fương thức FillDown đến dòng 600;

Tuy nhiên mình nghĩ rằng CSDL của bạn chưa thật hợp lý cho lắm!

Dù gì chăng nữa cũng xin chúc bạn thành công.

 

[lohado]

Option Explicit
Sub gpeTH()
 Dim Sh As Worksheet
 
 Sheets("TongHop").Select
 [b6].CurrentRegion.Offset(1, 1).ClearContents
 For Each Sh In ThisWorkbook.Worksheets
    If Sh.Name <> "TongHop" Then
        With [B65500].End(xlUp).Offset(1)
            .Value = Sh.Name
            .Offset(, 1).Value = Sh.Columns("H:H").SpecialCells(xlCellTypeFormulas, 3).Value
            .Offset(, 2).Value = Sh.Columns("I:I").SpecialCells(xlCellTypeFormulas, 3).Value
        End With
    End If
 Next Sh
End Sub

Theo mình để trang 'TongHop' thêm diêm dúa, ta tiến hành các công đoạn bổ trợ sau:

Tại [A] ta áp công thức: =IF(B6="","",1)
Tại ô dưới liền kề ta áp CT: =IF(B7="","",A6+1)

Sau đó bạn dùng fương thức FillDown đến dòng 600;

Tuy nhiên mình nghĩ rằng CSDL của bạn chưa thật hợp lý cho lắm!

Dù gì chăng nữa cũng xin chúc bạn thành công.

Bạn ơi 2 dòng kia mình ngốc quá không hiểu . Nếu B6= "trống" thì "trống" hoặc bằng 1. Nếu B7 bằng "Trống" thì "Trống" hoặc bằng A6+1 .Tại [A] tức là tại cột A hả ??.
Thứ nữa là mình mà áp dụng vào bảng quản lý kho thật thì phải sửa chỗ nào nhỉ . Ví dụ trên sheet " TongHop" Tên hàng của mình ở cột D, "Tổng số lượng nhập" là cột I và "Tổng số lượng xuất" là ở cột K. Còn các sheet phụ kia " Tổng số lượng nhập" ở cột O, "Tổng số lượng xuất" ở côt P

 

[lohado]

Cứ ngồi vừa ngủ gật vừa nhấn F8. Mãi Cũng vỡ ra tí vấn đề. Mình đã hiểu xơ xơ và biết thay vào bảng quản lý của mình rồi.
Code này sẽ xóa hết dữ liệu và điền lại từ đầu, nhưng như vậy thì nó lại vô tình xóa hết các cột "Thông tin" khác của mình ở sheet "TongHop".
Vấn đề nữa là nếu phát sinh 1 số "sheet lạ" mà trong đó cột H và I không có cái bảng tổng kia là error ngay.
Nhờ HYen17 và các cao thủ sửa lại hộ mình cho nó hoàn thiện hơn.
Mình gửi file đính kèm. "Thông tin""khác của mình ở đây là cột "khách hàng". "Sheet lạ" là sheet "MucLuc".

Một số vấn đề khác nếu rảnh thì giải thích giúp mình như :
+ Mỗi lần mình save thì nó hiện cái bảng này :

Xin chỉ giáo cách tắt nó.

Đoạn code này nghĩa nó như thế nào :

[COLOR=#007700][FONT=monospace][I].[/I][/FONT][/COLOR][COLOR=#0000BB][FONT=monospace][I]Offset[/I][/FONT][/COLOR][COLOR=#007700][FONT=monospace][I](, [/I][/FONT][/COLOR][COLOR=#0000BB][FONT=monospace][I]1[/I][/FONT][/COLOR][COLOR=#007700][FONT=monospace][I]).[/I][/FONT][/COLOR][COLOR=#0000BB][FONT=monospace][I]Value [/I][/FONT][/COLOR][COLOR=#007700][FONT=monospace][I]= [/I][/FONT][/COLOR][COLOR=#0000BB][FONT=monospace][I]Sh[/I][/FONT][/COLOR][COLOR=#007700][FONT=monospace][I].[/I][/FONT][/COLOR][COLOR=#0000BB][FONT=monospace][I]Columns[/I][/FONT][/COLOR][COLOR=#007700][FONT=monospace][I]([/I][/FONT][/COLOR][COLOR=#DD0000][FONT=monospace][I]"H:H"[/I][/FONT][/COLOR][COLOR=#007700][FONT=monospace][I]).[/I][/FONT][/COLOR][COLOR=#0000BB][FONT=monospace][I]SpecialCells[/I][/FONT][/COLOR][COLOR=#007700][FONT=monospace][I]([/I][/FONT][/COLOR][COLOR=#0000BB][FONT=monospace][I]xlCellTypeFormulas[/I][/FONT][/COLOR][COLOR=#007700][FONT=monospace][I], [/I][/FONT][/COLOR][B][COLOR=#0000BB][FONT=monospace][I]3[/I][/FONT][/COLOR][/B][COLOR=#007700][FONT=monospace][I]).[/I][/FONT][/COLOR][COLOR=#0000BB][FONT=monospace][I]Value
      [/I][/FONT][/COLOR][COLOR=#007700][FONT=monospace][I].[/I][/FONT][/COLOR][COLOR=#0000BB][FONT=monospace][I]Offset[/I][/FONT][/COLOR][COLOR=#007700][FONT=monospace][I](, [/I][/FONT][/COLOR][COLOR=#0000BB][FONT=monospace][I]2[/I][/FONT][/COLOR][COLOR=#007700][FONT=monospace][I]).[/I][/FONT][/COLOR][COLOR=#0000BB][FONT=monospace][I]Value [/I][/FONT][/COLOR][COLOR=#007700][FONT=monospace][I]= [/I][/FONT][/COLOR][COLOR=#0000BB][FONT=monospace][I]Sh[/I][/FONT][/COLOR][COLOR=#007700][FONT=monospace][I].[/I][/FONT][/COLOR][COLOR=#0000BB][FONT=monospace][I]Columns[/I][/FONT][/COLOR][COLOR=#007700][FONT=monospace][I]([/I][/FONT][/COLOR][COLOR=#DD0000][FONT=monospace][I]"I:I"[/I][/FONT][/COLOR][COLOR=#007700][FONT=monospace][I]).[/I][/FONT][/COLOR][COLOR=#0000BB][FONT=monospace][I]SpecialCells[/I][/FONT][/COLOR][COLOR=#007700][FONT=monospace][I]([/I][/FONT][/COLOR][COLOR=#0000BB][FONT=monospace][I]xlCellTypeFormulas[/I][/FONT][/COLOR][COLOR=#007700][FONT=monospace][I], [/I][/FONT][/COLOR][B][COLOR=#0000BB][FONT=monospace][I]3[/I][/FONT][/COLOR][/B][COLOR=#007700][FONT=monospace][I]).[/I][/FONT][/COLOR][COLOR=#0000BB][FONT=monospace][I]Value[/I][/FONT][/COLOR]

Nhất là 2 cái số 3 ở cuối

 

[lohado]

UP!
Các cao thủ giải đáp giúp e với !

 

[Ngoai Thanh]

UP!
Các cao thủ giải đáp giúp e với !

Thêm tý tẹo kiểu "thấp thủ" vậy .

 

[lohado]

Thêm tý tẹo kiểu "thấp thủ" vậy .

Với e a vẫn là cao thủ ).Từ lần sau có sheet lạ là thêm vào đoạn này phải không anh

Option ExplicitSub gpeTH() Dim Sh As Worksheet  Sheets("TongHop").Select [c6].CurrentRegion.Offset(1, 1).ClearContents For Each Sh In ThisWorkbook.Worksheets[COLOR=#ff0000][B]    If Sh.Name  "TongHop" Then[/B][/COLOR][COLOR=#ff0000][B]    If Sh.Name  "Muc luc" Then[/B][/COLOR][COLOR=#ff0000][B]    If Sh.Name  "Lo xa" Then[/B][/COLOR]        With [c65500].End(xlUp).Offset(1)            .Value = Sh.Name            .Offset(, 1).Value = Sh.Columns("H:H").SpecialCells(xlCellTypeFormulas, 3).Value            .Offset(, 2).Value = Sh.Columns("I:I").SpecialCells(xlCellTypeFormulas, 3).Value        End With    End If    End If    End If Next ShEnd Sub

còn vấn đề xóa hết cột thông tin khác thì sử lý thế nào ạ ??? Mấy khách hàng , Mỹ , Nga, Trung Quốc, Triều Tiên toàn khách hàng tiềm năng của e cả )Có cách nào để nó dùng giá trị Bom, TenLua, MayBay, XeTang quét ra tên sheet rồi lọc dữ liệu không ạ. E nghĩ như thế thì chắc nó sẽ không xóa khách hàng của e và các sheet lạ sẽ không phải add vào đoạn code kia . >>>>Ngu ý của e @@

 

[lohado]

Up!
Các Thấp thủ, Trung thủ, Cao thủ giúp e với

 

[Ngoai Thanh]

Up!
Các Thấp thủ, Trung thủ, Cao thủ giúp e với

Thứ nhất bạn là thành viên mới nên tôi nhắc bạn kể cả các thày trong diễn đàn không ai nhận mình là cao thủ đâu. Chính vì mấy cụm từ ấy mà kết quả là không người giúp . thứ 2 là bạn cũng phải tự vận động, nếu bạn hiểu thì đưa dữ liệu giả định, sau tự mình thay đổi. còn không thì đưa dữ liệu thực, mọi người sẽ giúp , đằng này bạn đưa những bom đạn rồi kêu váng trời thế là sao !

 

[lohado]

Thứ nhất bạn là thành viên mới nên tôi nhắc bạn kể cả các thày trong diễn đàn không ai nhận mình là cao thủ đâu. Chính vì mấy cụm từ ấy mà kết quả là không người giúp . thứ 2 là bạn cũng phải tự vận động, nếu bạn hiểu thì đưa dữ liệu giả định, sau tự mình thay đổi. còn không thì đưa dữ liệu thực, mọi người sẽ giúp , đằng này bạn đưa những bom đạn rồi kêu váng trời thế là sao !

Đúng là mình mới tham gia diễn đàn nên cũng chưa biết lệ làng ở diễn đàn mình. Có gì không phải mong các bạn bỏ quá cho.
File ví dụ của mình đưa lên gần như giống hệt file dữ liệu thực, chỉ khác nhau vị trí cell tí thôi. Mình thì mình không biết VBA, cứ nghĩ đơn giản là nhìn vào code kiểu gì chả có chỗ cell sửa được. Nhưng thực sự là lần này có khác lần trước 1 tí nên gặp khó khăn. Ai lên đây nhờ giúp đỡ thì đều chắc có 1 tình trạng chung là cần rất gấp để giải quyết công việc.
Mình xin lỗi khi post bài kiểu hối giục này .
Trong các câu văn lời nói của mình tuy hơi lủng cũng nhưng không hề vô lễ hay mang tính chất ra lệnh giống như kiểu 1 số bạn khác nên các bạn đừng ghét mình nhé.
Mình biết mình còn nhiều thiếu sót, ví như mình mới nhận ra cái topic này cũng post sai chỗ ,có lẽ nó phải post ở khu vực "Lập Trình Với Excell". Như ở diễn đàn khác là có khi bị xóa ngay rồi đấy. Rất may là mod nhà mình dễ tính, thương người
Điều cuối cùng là mình thành thật xin lỗi. Mong các bạn giúp đỡ mình nốt lần này .Lần sau mình sẽ chú ý hơn ( Mình xin chân thành cảm ơn !

 

[Ngoai Thanh]

Đúng là mình mới tham gia diễn đàn nên cũng chưa biết lệ làng ở diễn đàn mình. Có gì không phải mong các bạn bỏ quá cho.
File ví dụ của mình đưa lên gần như giống hệt file dữ liệu thực, chỉ khác nhau vị trí cell tí thôi. Mình thì mình không biết VBA, cứ nghĩ đơn giản là nhìn vào code kiểu gì chả có chỗ cell sửa được. Nhưng thực sự là lần này có khác lần trước 1 tí nên gặp khó khăn. Ai lên đây nhờ giúp đỡ thì đều chắc có 1 tình trạng chung là cần rất gấp để giải quyết công việc.
Mình xin lỗi khi post bài kiểu hối giục này .
Trong các câu văn lời nói của mình tuy hơi lủng cũng nhưng không hề vô lễ hay mang tính chất ra lệnh giống như kiểu 1 số bạn khác nên các bạn đừng ghét mình nhé.
Mình biết mình còn nhiều thiếu sót, ví như mình mới nhận ra cái topic này cũng post sai chỗ ,có lẽ nó phải post ở khu vực "Lập Trình Với Excell". Như ở diễn đàn khác là có khi bị xóa ngay rồi đấy. Rất may là mod nhà mình dễ tính, thương người
Điều cuối cùng là mình thành thật xin lỗi. Mong các bạn giúp đỡ mình nốt lần này .Lần sau mình sẽ chú ý hơn ( Mình xin chân thành cảm ơn !

Không ai ghét bạn cả, nhưng sợ mất thời gian bởi làm xong bạn lại thêm cái này, thêm cái kia thì ..."đến tết tây" . Các sheet bạn hỏi trên là nó "chừa" ra không tổng hợp . Còn bạn muốn thêm bao nhiêu sheet dữ liệu thì phải thử thêm vào xem code có chạy không đã chứ ? miễn là các sheet thêm vào phải có cấu trúc đúng như sheet hiện có để tổng hợp . Để chắc ăn thày Hyến 17 đã giúp bạn thì bạn đặt vấn đề để thày giúp tiếp vì kiến thức mình hơn bạn mỗi một " hạt ngô ". thôi mà . Còn cái "vụ" xóa dữ liệu là sao ? Nếu đúng sheet bạn thêm vào bị xóa thì do cấu trúc không đúng các sheet khác, bạn kiểm tra lại xem .

 

[lohado]

Không ai ghét bạn cả, nhưng sợ mất thời gian bởi làm xong bạn lại thêm cái này, thêm cái kia thì ..."đến tết tây" . Các sheet bạn hỏi trên là nó "chừa" ra không tổng hợp . Còn bạn muốn thêm bao nhiêu sheet dữ liệu thì phải thử thêm vào xem code có chạy không đã chứ ? miễn là các sheet thêm vào phải có cấu trúc đúng như sheet hiện có để tổng hợp . Để chắc ăn thày Hyến 17 đã giúp bạn thì bạn đặt vấn đề để thày giúp tiếp vì kiến thức mình hơn bạn mỗi một " hạt ngô ". thôi mà . Còn cái "vụ" xóa dữ liệu là sao ? Nếu đúng sheet bạn thêm vào bị xóa thì do cấu trúc không đúng các sheet khác, bạn kiểm tra lại xem .

Tại mình không hiểu sâu nên khi chạy code gặp vấn đề phát sinh thì mới hỏi. Ví như code nó xóa toàn bộ dữ liệu trong bảng tổng hợp trước khi tìm kiếm và điền kết quả >>> cái này thì lúc chạy mới biết nên mới hỏi tiếp.
Chỉ 1 lý do là kiến thức kém.
Bạn ơi! xử lý chỗ đó được không??. Giúp mình với !

 

[Ngoai Thanh]

Tại mình không hiểu sâu nên khi chạy code gặp vấn đề phát sinh thì mới hỏi. Ví như code nó xóa toàn bộ dữ liệu trong bảng tổng hợp trước khi tìm kiếm và điền kết quả >>> cái này thì lúc chạy mới biết nên mới hỏi tiếp.
Chỉ 1 lý do là kiến thức kém.
Bạn ơi! xử lý chỗ đó được không??. Giúp mình với !

Sao thế nhỉ ? Tất cả các sheet nếu có nhập xuất thì tồn thay đổi, không có nhập xuất thì dữ liệu không đổi . Nếu thay đổi thì phải xóa dữ liệu cũ và điền dữ liệu mới chứ . Hay bạn muốn tổng hợp tồn theo thời gian ? tức là hôm qua tồn thế nào ? hôm nay tồn thế nào ?....

 

[lohado]

ý mình mà nếu không có sự xáo trộn vị trí các sheet. vd như lúc nào nó cũng là MucLuc,TongHop,TenLua,XeTang,Bom,Maybay >>> Thì thứ tự các loại hàng trong cột "Tên hàng" ở Sheet tổng hợp lúc nào cũng theo thứ tự không đổi. Khi đó tại cột khách hàng của mình sẽ chỉ phải thêm 1 lần các khách hàng mà không sợ bị xáo trộn thứ tự. Code này nó xóa hết nên mỗi lần làm mình lại phải coppy lại hơi bất tiện

 

[Ngoai Thanh]

ý mình mà nếu không có sự xáo trộn vị trí các sheet. vd như lúc nào nó cũng là MucLuc,TongHop,TenLua,XeTang,Bom,Maybay >>> Thì thứ tự các loại hàng trong cột "Tên hàng" ở Sheet tổng hợp lúc nào cũng theo thứ tự không đổi. Khi đó tại cột khách hàng của mình sẽ chỉ phải thêm 1 lần các khách hàng mà không sợ bị xáo trộn thứ tự. Code này nó xóa hết nên mỗi lần làm mình lại phải coppy lại hơi bất tiện

làm gì có chuyện ấy, bạn xem file đính kèm . Nếu thêm sheet khách hàng bạn bấm nút tự nó tổng hợp .

 

[lohado]

làm gì có chuyện ấy, bạn xem file đính kèm . Nếu thêm sheet khách hàng bạn bấm nút tự nó tổng hợp .

Đây là hình ảnh khi mở file bạn dính kèm ra lần đầu nhé


Giò cột "Khách Hang" mình thêm vào A,B,C,D chẳng hạn. Nhấn nút Button 1. A,B,C,D biến mất.
-----------------------------------------------------
Giờ thêm sheet khách hàng hả bạn. Đợi mình thử tí

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

Bạn ơi thêm như thế nào hả bạn. mình thêm toàn báo error

 

[Ngoai Thanh]

Đây là hình ảnh khi mở file bạn dính kèm ra lần đầu nhé
View attachment 106677

Giò cột "Khách Hang" mình thêm vào A,B,C,D chẳng hạn. Nhấn nút Button 1. A,B,C,D biến mất.
-----------------------------------------------------
Giờ thêm sheet khách hàng hả bạn. Đợi mình thử tí

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

Bạn ơi thêm như thế nào hả bạn. mình thêm toàn báo error

Bạn bấm phải chuột vào tap sheet (chẳng hạn sheet Bom) -->Move or copy --> tích vao nút create a copy --> kéo thanh trượt xuống cuối tìm (move to end) --> ok, sau đó đổi tên sheet, cập nhật dữ liệu mới . thay đổi tên mã hàng (B4) . nhớ không được thay đổi định dạng, và các lệnh insert cột, các dòng trên tiêu đề dẫn tới địa chỉ sai lạc. Nói chung không nên thay đổi gì khác ngoài dữ liệu , nếu không nó báo lỗi và xóa đi đó . À mà bạn tự thêm dữ liệu vào cuối bản tổng hợp A, B, c ....nó xóa đi quá chứ lại . bởi đó không phải dữ liệu tổng hờp từ các sheet , nó như ai đó bắt bạn nuôi con người khác bạn có chịu không ? chắc la còn to hơn nữa !

 

[lohado]

Bạn bấm phải chuột vào tap sheet (chẳng hạn sheet Bom) -->Move or copy --> tích vao nút create a copy --> kéo thanh trượt xuống cuối tìm (move to end) --> ok, sau đó đổi tên sheet, cập nhật dữ liệu mới . thay đổi tên mã hàng (B4) . nhớ không được thay đổi định dạng, và các lệnh insert cột, các dòng trên tiêu đề dẫn tới địa chỉ sai lạc. Nói chung không nên thay đổi gì khác ngoài dữ liệu , nếu không nó báo lỗi và xóa đi đó . À mà bạn tự thêm dữ liệu vào cuối bản tổng hợp A, B, c ....nó xóa đi quá chứ lại . bởi đó không phải dữ liệu tổng hờp từ các sheet , nó như ai đó bắt bạn nuôi con người khác bạn có chịu không ? chắc la còn to hơn nữa !

Hì. Mình hiểu rồi. Cảm ơn bạn đã trả lời tận tình

À nhân tiện bạn còn online cho mình hỏi nốt. Mình kiếm được cái code tạo menu sheet này. Bạn xem nhanh xem có cách nào đưa nó vào 1 cái nút mà khi nào bấm nó mới cập nhật menu sheet không, chứ như code này cứ click chuột vào là nó lại chạy code từ đầu. file quản lý kho của mình có nhiều sheets lắm, mỗi lần thế toàn đơ máy 1 lúc.
Links
http://goccay.vn/showthread.php?4257-Tao-muc-luc-cac-sheet-trong-Excel

 

[Ngoai Thanh]

Hì. Mình hiểu rồi. Cảm ơn bạn đã trả lời tận tình

À nhân tiện bạn còn online cho mình hỏi nốt. Mình kiếm được cái code tạo menu sheet này. Bạn xem nhanh xem có cách nào đưa nó vào 1 cái nút mà khi nào bấm nó mới cập nhật menu sheet không, chứ như code này cứ click chuột vào là nó lại chạy code từ đầu. file quản lý kho của mình có nhiều sheets lắm, mỗi lần thế toàn đơ máy 1 lúc.
Links
http://goccay.vn/showthread.php?4257-Tao-muc-luc-cac-sheet-trong-Excel

làm nốt cho bạn cho xong, oải quá rồi .

 

[lohado]

làm nốt cho bạn cho xong, oải quá rồi .

ok. Vậy là đã giải quyết hết các vẫn đề
Cảm ơn bạn Ngoai Thanh nhiều lắm !
Chúc bạn ngủ ngon nhé !!!

 

[tranvanhung2009]

Nhờ các bạn viết giúp code để tự động tổng hợp số liệu các tháng từ tháng 1 đến tháng 12 của các sheet sang sheet tổng hợp

 

[tranvanhung2009]

Bổ sung tiêu đề cho 2 cell B9 & B10. Xóa dữ liệu bạn đã làm mẫu ở 3 cột C,D,E. Và chạy code sau

Option Explicit
Sub Copy()
Dim Ws As Worksheet
Application.ScreenUpdating = False
For Each Ws In Worksheets
    If Ws.Name <> "Tong hop" Then
        Ws.Range("A79:BN79").Copy
        Sheets("Tong hop").Range("A9").End(2).Offset(, 1).PasteSpecial xlPasteValues, , , True
    End If
Next Ws
Application.ScreenUpdating = True
End Sub

Copy bị sai tháng anh ạ. Có cách nào để các tháng nó tăng dần từ 1 đến 12 không anh