Code vba copy dữ liệu nhiều sheet (1 người xem)

[hungvinhhao]

Sau khi biết trong excel có ứng dụng VBA en thấy tính năng của nó thật ưu việt. Nhưng do trình độ có hạn nên em thường xuyên phải cầu cứu các bậc tiền bối. Em có mấy vấn đề nhờ các anh chị trong diễn đàn giúp đỡ như sau:
1- Cho em xin code copy dữ liệu ở các sheet ( từ sheet 1 đến sheet 31) sang sheet "MAIN" với điều kiện dữ liệu ở Sheet1 được copy từ dòng thứ 3 trở xuống ( dòng chứ tiêu đề) còn từ SHEET thứ 2 đến sheet31 thì dữ liệu được copy từ dòng thứ 4 trở xuống. Và khi em thay đổi dữ liệu ở các sheet kể cả chèn thêm dòng thì lệnh copy tự động thực hiện để cập vào sheet "MAIN". Em cũng tham khảo code copy dữ liệu từ nhiều sheet thành 1 sheet trong diễn đàn nhưng nó lại copy luôn cả sheet em không muốn copy chẳng hạn như Sheet "TONGHOP". Rất mong các anh chị giúp đỡ em.
2 - Code VBA thay thế hàm sumif() trong vùng H4:W37 trong sheet "TONGHOP"
Đây là file đính kèm của em

 

[Let'GâuGâu]

Sau khi biết trong excel có ứng dụng VBA en thấy tính năng của nó thật ưu việt. Nhưng do trình độ có hạn nên em thường xuyên phải cầu cứu các bậc tiền bối. Em có mấy vấn đề nhờ các anh chị trong diễn đàn giúp đỡ như sau:
1- Cho em xin code copy dữ liệu ở các sheet ( từ sheet 1 đến sheet 31) sang sheet "MAIN" với điều kiện dữ liệu ở Sheet1 được copy từ dòng thứ 3 trở xuống ( dòng chứ tiêu đề) còn từ SHEET thứ 2 đến sheet31 thì dữ liệu được copy từ dòng thứ 4 trở xuống. Và khi em thay đổi dữ liệu ở các sheet kể cả chèn thêm dòng thì lệnh copy tự động thực hiện để cập vào sheet "MAIN". Em cũng tham khảo code copy dữ liệu từ nhiều sheet thành 1 sheet trong diễn đàn nhưng nó lại copy luôn cả sheet em không muốn copy chẳng hạn như Sheet "TONGHOP". Rất mong các anh chị giúp đỡ em.
2 - Code VBA thay thế hàm sumif() trong vùng H4:W37 trong sheet "TONGHOP"
Đây là file đính kèm của em

Việc copy qua sheet "main" là để sumif cho sheet "th" đó hả?, nếu chỉ làm việc đó thôi thì không cần tổng hợp
còn đây là là theo yêu cầu của bạn,bạn chép nó vào sheet34 nha, khi nào bạn chọn sheet main (active) thì code sẻ chạy

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Dim ws As Worksheet

With Sheet34
    .[a3:x20000].ClearContents
    .[a3:x3].Value = Sheet1.[a3:x3].Value
End With
For Each ws In Worksheets
    With ws
        If ws.Index < 32 Then Sheet34.[A20000].End(3).Offset(1).Resize(.[A2000].End(3).Row - 3, 24).Value = .[A4].Resize(.[A2000].End(3).Row - 3, 24).Value
    End With
Next
Sheet34.[A3].CurrentRegion.Borders.Value = 1
Application.ScreenUpdating = True
End Sub

còn đây là code cho sumif

Sub SUMIF()
Dim MAIN, TH As Variant, KQ(), I, J, K As Long
MAIN = Sheet34.[A4].Resize(Sheet34.[A20000].End(3).Row - 4, 24).Value
TH = Sheet6.[A4].Resize(Sheet6.[A20000].End(3).Row - 4, 23).Value
ReDim KQ(1 To UBound(TH), 1 To 23)
For I = 1 To UBound(TH)
    For J = 1 To UBound(MAIN)
        For K = 8 To 23
            If MAIN(J, 2) = TH(I, 2) Then KQ(I, K) = KQ(I, K) + MAIN(J, K + 1)
         Next K
    Next J
Next I
       
With Sheet6
    .[H4:W20000].ClearContents
    .[H4].Resize(I, 16) = KQ
End With
Erase MAIN, TH
End Sub

 

[hungvinhhao]

Mục đích của em copy là còn để dùng báo cáo Pivottable nữa. Vì ngoài báo có theo cửa hàng em còn phải thống kê tổng hợp theo nhân viên đứng ở cửa hàng nữa. Hôm trước anh Quanghai1969 cho em đoạn code để tổng hợp báo cÁO CHO CÁC CỬA HÀNG TẠI sheet TONGHOP nhưng khi em muốn báo cáo theo nhân viên thì không biết thay đổi code đó như thế nào. Let'GâuGâu có thể giúp em được không? em gửi code và file đính kèm nhé

 

[hungvinhhao]

Đây là file đính kem

Đây là bài hôm trước em đăng lên diễn đàn.
Em có 1 file báo cáo như file đính kèm. Mỗi mỗi ngày tương ứng với 1 sheet. Các sheet có cấu trúc giống nhau. Sheet "TONGHOP" là tổng hợp dữ liệu của các sheet để báo cáo tháng. Em không biết về VBA lắm mà tổng hợp thủ công thì file chạy chậm. Em nhờ các tiền bối giúp em viết code VBA để:
1 - Hàng Ngày em vào dữ liệu ở các sheet (Sheet 1 .......sheet 31) thì dữ liệu tự cập nhật vào sheet " TONGHOP". Vùng dữ liệu i3:U5000 là tổng Sum (sheet1:sheet 31). với điều kiện đúng mã cửa hàng và mặt hàng.
2 - Khi chèn thêm cột, hoặc dòng ở bất kỳ sheet nào (sheet1,....sheet31) thì ở Sheet "TONGHOP" cũng tự động được chèn thêm đúng như nội dung được thêm vào ở sheet đó và dữ liệu cũng được cập nhật như bước 1.
Rất mong các bậc tiền bối giúp đỡ. Em cảm ơn nhiều.
Ghi chú: Em cũng đã làm theo theo cách dùng code copy dữ liệu các sheet thành 1 sheet nhưng nó lại copy luôn dư liệu của cả sheet "TONGHOP". Mà hơn nữa khi dùng hàm SUMIF tại sheet TONGHOP để cập nhật dữ liệu thì file chạy rất chậm.
Nếu không viết được code theo yêu cầu ở trên thì các tiền bối cho em xin code để mình copy dữ liệu từ sheet 1 đến sheet 31 sang 1 sheet mới. Không copy dữ liệu của sheet TONGHOP. Và code VBA để tổng hợp dữ liệu từ Sheet mới sang Sheet TONGHOP để em không phải dùng hàm SUMIF. Hàm SUMIF chạy rất chậm. Em dùng excel 2003.
Và đây là code anh quanghai1969 cho em chạy rất ok.
Sub tong_hop()
Dim Source(), Result(1 To 10000, 1 To 21)
Dim Dic As Object, I As Long, j As Long, k As Long, n As Byte
Set Dic = CreateObject("scripting.dictionary")
For n = 1 To 31
With Sheets("sheet" & n)
Source = .Range(.[A3], .[A65536].End(3)).Resize(, 21).Value
End With
For I = 1 To UBound(Source)
If Source(I, 2) <> "" Then
If Not Dic.exists(Source(I, 2)) Then
k = k + 1
Dic.Add Source(I, 2), k
Result(k, 1) = k
For j = 2 To 21
Result(k, j) = Source(I, j)
Next
Else
c = Dic.Item(Source(I, 2))
For j = 9 To 21
Result(c, j) = Result(c, j) + Source(I, j)
Next
End If
End If
Next
Next
For j = 8 To 21
Result(k + 1, j) = "=SUM(R3C:R[-1]C)"
Next
Sheets("TONGHOP").[A3].Resize(k + 1, 21) = Result
End Sub

Tất nhiên ở sheet TONGHOP của em cũ có thêm cot Hoten NV
Em nhờ viết code mới hoặc sửa code trên để nó tổng hợp dữ liệu ở sheet THNV được không ạ. Em cảm ơn nhiều!
À! em nhờ xe lại code hàm sumif() nhé, tuy có chạy nhưng kết quả hiển thị không đúng. trong file đính kèm của em cũng thể hiện kết quả.

 

[hungvinhhao]

Đây là file có sheet TONGHOP đã chạy được code của anh quanghai1969 theo yêu cầu

Nhờ các tiền bối em và giúp em nhé!

 

[hungvinhhao]

Việc copy qua sheet "main" là để sumif cho sheet "th" đó hả?, nếu chỉ làm việc đó thôi thì không cần tổng hợp
còn đây là là theo yêu cầu của bạn,bạn chép nó vào sheet34 nha, khi nào bạn chọn sheet main (active) thì code sẻ chạy

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Dim ws As Worksheet

With Sheet34
    .[a3:x20000].ClearContents
    .[a3:x3].Value = Sheet1.[a3:x3].Value
End With
For Each ws In Worksheets
    With ws
        If ws.Index < 32 Then Sheet34.[A20000].End(3).Offset(1).Resize(.[A2000].End(3).Row - 3, 24).Value = .[A4].Resize(.[A2000].End(3).Row - 3, 24).Value
    End With
Next
Sheet34.[A3].CurrentRegion.Borders.Value = 1
Application.ScreenUpdating = True
End Sub

còn đây là code cho sumif

Sub SUMIF()
Dim MAIN, TH As Variant, KQ(), I, J, K As Long
MAIN = Sheet34.[A4].Resize(Sheet34.[A20000].End(3).Row - 4, 24).Value
TH = Sheet6.[A4].Resize(Sheet6.[A20000].End(3).Row - 4, 23).Value
ReDim KQ(1 To UBound(TH), 1 To 23)
For I = 1 To UBound(TH)
    For J = 1 To UBound(MAIN)
        For K = 8 To 23
            If MAIN(J, 2) = TH(I, 2) Then KQ(I, K) = KQ(I, K) + MAIN(J, K + 1)
         Next K
    Next J
Next I
       
With Sheet6
    .[H4:W20000].ClearContents
    .[H4].Resize(I, 16) = KQ
End With
Erase MAIN, TH
End Sub

If ws.Index < 32 Thì 32 này là chỉ số thứ tự của sheet trong VBA khi em ấn alt+f11 hay sheet do em đặt tên. Nếu là số thứ tự sheet trong VBAPROJECT thì tại sao SHEET6(TONGHOP) lại không được copy nhỉ. Tiền bối giải thích giúp em nhé!

 

[Let'GâuGâu]

If ws.Index < 32 Thì 32 này là chỉ số thứ tự của sheet trong VBA khi em ấn alt+f11 hay sheet do em đặt tên. Nếu là số thứ tự sheet trong VBAPROJECT thì tại sao SHEET6(TONGHOP) lại không được copy nhỉ. Tiền bối giải thích giúp em nhé!

về cách nhận diện sheet thì có 3 cách
1- dựa vào tên:

sheet.name

2- dựa vào thứ tự từ trái qua phải

sheet.index

3- cái này tôi không biết tên là gì, trong code người ta goi là codename (tức là các số sheet1,sheet2, mà bạn thấy ở trong cửa sổ vba)

 sheet.codename

để rỏ hơn về nó bạn thử một code nhỏ như sau:

for each ws in worksheets
msgbox "ten " & ws.name
msgbox "stt " & ws.index
msgbox "tencode " & ws.codename 
next

 

[hungvinhhao]

OK! Em đã hiểu vấn đề. Em cảm ơn bác nhiều nhiều!

 

[hungvinhhao]

Private Sub Worksheet_Activate()Application.ScreenUpdating = False
Dim ws As Worksheet

With Sheet34
.[a3:x20000].ClearContents
.[a3:x3].Value = Sheet1.[a3:x3].Value
End With
For Each ws In Worksheets
With ws
If ws.Index < 32 Then Sheet34.[A20000].End(3).Offset(1).Resize(.[A2000].End(3).Row - 3, 24).Value = .[A4].Resize(.[A2000].End(3).Row - 3, 24).Value
End With
Next
Sheet34.[A3].CurrentRegion.Borders.Value = 1
Application.ScreenUpdating = True
End Sub


Em muốn code trên copy sang sheet "main" bătt đầu từ dòng thứ 300 trở xuống thì phải sửa code như thế nào bác nhỉ. Hàm Offset trong vba không không hiểu bác ạ.

 

[tuanda85]

Chào các ac trên diễn đàn,

Mình cũng có nhu cầu tổng hợp số liệu các ngày trong tháng từ các file vào 1 file Tổng hợp, nhưng mình không muốn copy hết mà chỉ muốn copy số liệu ở dòng Tổng cộng dưới cùng ở các file vào File Tổng hợp.

mình cần nữa là ví dụ file chi tiết ngày 09/11/2016 thì khi copy số liệu dòng tổng cộng vào file Tổng hợp thì dữ liệu đó phải nhảy vào dòng ngày Thứ 4 trên file TỔng hợp, Tương tự nếu copy file chi tiết ngày 10/11 thì nhảy vào dòng ngày thứ 5.

File mình cần làm đính kèm email này, rất mon được các anh/ chị trên diễn đàn giúp đỡ ^^

 

[tuanda85]

Chào các ac trên diễn đàn,

Mình cũng có nhu cầu tổng hợp số liệu các ngày trong tháng từ các file vào 1 file Tổng hợp, nhưng mình không muốn copy hết mà chỉ muốn copy số liệu ở dòng Tổng cộng dưới cùng ở các file vào File Tổng hợp.

mình cần nữa là ví dụ file chi tiết ngày 09/11/2016 thì khi copy số liệu dòng tổng cộng vào file Tổng hợp thì dữ liệu đó phải nhảy vào dòng ngày Thứ 4 trên file TỔng hợp, Tương tự nếu copy file chi tiết ngày 10/11 thì nhảy vào dòng ngày thứ 5.

File mình cần làm đính kèm email này, rất mon được các anh/ chị trên diễn đàn giúp đỡ ^^