Code Insert Row và điền dữ liệu tự động từ 01 Sh cho các Sh từ T01 đến Tn, tùy ĐK! (1 người xem)

[Hong.Van]

Thầy cô & anh chị giúp em trường hợp sau:
1/ Em có Sheet "Ma", các sheet T01, T02,... T12 (tương ứng với các tháng 01, 02, ...12) và Sheet TNam (tương ứng năm)
2/ Em sẽ nhập các số liệu ở Sheet Ma, Cột E (Tháng) phát sinh tháng nào thì ghi tháng đó, VD: T03 là tăng của tháng 03
3/ Tại các Sheet T01 -> T12 và TNam, sẽ tự động chèn dòng và lấy số liệu tương ứng từ Sheet Ma qua
VD: a/Sheet T01 sẽ lấy số liệu tương ứng với cột E sheet Ma là T01
b/Sheet T02 sẽ lấy số liệu tương ứng với cột E sheet Ma là từ T01 đến T02. Tóm lại Tn sẽ lấy số liệu tương ứng với cột E sheet Ma là từ T01 đến Tn
c/ Sheet Tnam sẽ lấy số liệu tương ứng với cột E sheet Ma là từ T01 đến T12
4/ Khi chèn dòng & điền dữ liệu tự động ở các Sheet Tn, thì sẽ chèn từ cột A cho đến cột M (Vì từ cột N trở về sau em còn có các số liệu khác)
Em có minh họa trong File đính kèm.

Xin viết code giúp em trường hợp này. Cảm ơn

 

[SA_DQ]

Giải fáp là Copy từ kết quả AdvancedFilter hay hơn thêm dòng đó bạn!

Xem trong file nhe & chú í câu lệnh

Const SoTr As Byte = 5

 

[Hong.Van]

Em cảm ơn thầy đã giúp đỡ!
Em xin có một vài ý kiến sau
1/ Em hiểu câu dưới Số 5 là tổng Số Sheet chó chữ T đứng đầu, như vậy mỗi lần thêm Sheet thì em phải sửa lại thông số này. Do File này em chia sẽ trong phòng cho các anh chị cùng làm, nên em sợ các anh chị táy máy các đọan code một chút thì fiền. Thầy có cách nào ghi thông số này ngòai bảng tình để chỉnh sửa cho dễ.
Em thử đặt Name, nhưng nó báo lỗi

Xem trong file nhe & chú í câu lệnh

Const SoTr As Byte = 5

2/ Tại cột F của các Sheet T..., có ghi thêm các mã T01, T02,...Thầy xóa giùm em không cập nhật tự động cái này!
Em cảm ơn thầy cô & anh chị.

 

[ChanhTQ@]

Các ý kiến sau
1/ Em hiểu câu dưới Số 5 là tổng Số Sheet chó chữ T đứng đầu, như vậy mỗi lần thêm Sheet thì em phải sửa lại thông số này. Do File này em chia sẽ trong phòng cho các anh chị cùng làm, nên em sợ các anh chị táy máy các đọan code một chút thì fiền. Thầy có cách nào ghi thông số này ngòai bảng tình để chỉnh sửa cho dễ.
Em thử đặt Name, nhưng nó báo lỗi

2/ Tại cột F của các Sheet T..., có ghi thêm các mã T01, T02,...Thầy xóa giùm em không cập nhật tự động cái này!
Em cảm ơn thầy cô & anh chị.

(1) Bạn tới các ô [AD11:Ae11] của trang 'Ma' & xoá dữ liệu của chúng đi; & nhập con số 5 (số trang tính 'T..') vô [AE11]
Bạn sau đó có thể tô màu nền cho ô này & gán cho nó cái tên, như 'GPE'

Sửa lại dòng lệnh For đầu tiên thành:

For jJ = 1 To [AE11].Value

hay

For jJ = 1 To Range("GPE").value

Lúc đó dòng khai báo hằng số (=5) dư, bạn có thể huỷ đi hay vô hiệu hoá nó.

(2) Bạn sửa dòng lệnh dài nhứt thành:

    Rng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=[aG11].Resize(fF), CopyToRange:=Range("AA11:AC11")

thêm nữa là được

Chúc mĩ mãn!

 

[Hong.Van]

Em cảm ơn Thầy cô & anh chị
1/Em đã chuyển hằng số vế cell A1 Sh Ma, để dễ quản lý

(1) Bạn tới các ô [AD11:Ae11] của trang 'Ma' & xoá dữ liệu của chúng đi; & nhập con số 5 (số trang tính 'T..') vô [AE11]
Bạn sau đó có thể tô màu nền cho ô này & gán cho nó cái tên, như 'GPE'

Sửa lại dòng lệnh For đầu tiên thành:

For jJ = 1 To [AE11].Value

hay

For jJ = 1 To Range("GPE").value

Lúc đó dòng khai báo hằng số (=5)

2/ Vì em không muốn khi chèn dòng thì xóa dữ liệu ở các cột E đến cột M của các Sheet T...nên đã sửa

   Sh.[a9].Resize(Rws, 14).ClearContents

thành

   Sh.[a9].Resize(Rws, 4).ClearContents

Nhưng nó vẫn xóa dữ liệu ở cột E & F
3/ MẶT KHÁC KHI PHÁT SINH MÃ MỚI TẠI SHEET "MA", NẾU CHẠY CODE THÌ TẠI CÁC SHEET T... CÓ MÃ MỚI PHÁT SINH, NÓ CHỈ CHÈN DÒNG TỪ CỘT B ĐẾN CỘT F, CÒN TỪ CỘT G ĐẾT CỘT M THÌ KHÔNG CHÈN DÒNG TƯƠNG ỨNG, DO ĐÓ SỐ LIỆU CỦA CÁC DÒNG TƯƠNG ỨNG KHÁC SẼ BỊ ĐÔN LÊN! (VD, tại Sh Ma em thêm Dòng 27 (tô màu vàng) Phát sinh cho T04, Khi chạy code thì sẽ thấy nó chạy dòng ở Sh T04
Mong các thầy cô& anh chị giúp sửa Code cho em ở mục 2 & 3 nói trên.
Em cảm ơn.

 

[SA_DQ]

2/ Vì em không muốn khi chèn dòng thì xóa dữ liệu ở các cột E đến cột M của các Sheet T...nên đã sửa
hành

         Sh.[a9].Resize(Rws, 4).ClearContents

Nhưng nó vẫn xóa dữ liệu ở cột E & F



3/ MẶT KHÁC KHI PHÁT SINH MÃ MỚI TẠI SHEET "MA", NẾU CHẠY CODE THÌ TẠI CÁC SHEET T... CÓ MÃ MỚI PHÁT SINH, NÓ CHỈ CHÈN DÒNG TỪ CỘT B ĐẾN CỘT F, CÒN TỪ CỘT G ĐẾT CỘT M THÌ KHÔNG CHÈN DÒNG TƯƠNG ỨNG, DO ĐÓ SỐ LIỆU CỦA CÁC DÒNG TƯƠNG ỨNG KHÁC SẼ BỊ ĐÔN LÊN! (VD, tại Sh Ma em thêm Dòng 27 (tô màu vàng) Phát sinh cho T04, Khi chạy code thì sẽ thấy nó chạy dòng ở Sh T04

(2) Vì bạn chưa xóa dữ liệu tại ô [AD11] của 'Ma'

(3) Có lẻ fải chọn giải fáp khác rồi; Không thể chỉnh sửa được macro đâu!
Vì từ file đầu dữ liệu các cột sau 'E' của các tháng là trống trơn, nên mới chọn fương án dùng AdvancedFilter;
Còn giờ đã có dữ liệu nhập & cần giử nguyên hàng lối, nên đúng là chỉ có cách đối chiếu giữa 2 trang tính & thêm dòng thôi;
Có thể bạn fải chờ lâu đó.

(1) Con đường tơ lụa của dữ liệu của bạn là như thế nào nhỉ?

Vào đầu năm bạn có danh sách bên trang 'Ma'; cũng trong tháng đó bạn chép sang 'T01';
Trong th áng cũng đồng thời thêm dử liệu vô 'T01' cho người này, người kia;

Sang tháng sau, lại nhập thêm vô 'Ma' & chép sang 'T02'
. . . . .
Bạn mô tả rõ thêm công việc của bạn được không?

để tìm hướng mới í mà & hướng cũ ở trên là fá sản rồi!

 

[Hong.Van]

(2) Vì bạn chưa xóa dữ liệu tại ô [AD11] của 'Ma'

(3) Có lẻ fải chọn giải fáp khác rồi; Không thể chỉnh sửa được macro đâu!
Vì từ file đầu dữ liệu các cột sau 'E' của các tháng là trống trơn, nên mới chọn fương án dùng AdvancedFilter;
Còn giờ đã có dữ liệu nhập & cần giử nguyên hàng lối, nên đúng là chỉ có cách đối chiếu giữa 2 trang tính & thêm dòng thôi;
Có thể bạn fải chờ lâu đó.

Vậy mong các thầy cô Anh chị giúp em!
Mong tin, xin cảm ơn!

 

[Hong.Van]

(1) Con đường tơ lụa của dữ liệu của bạn là như thế nào nhỉ?

Vào đầu năm bạn có danh sách bên trang 'Ma'; cũng trong tháng đó bạn chép sang 'T01';
Trong th áng cũng đồng thời thêm dử liệu vô 'T01' cho người này, người kia;

Sang tháng sau, lại nhập thêm vô 'Ma' & chép sang 'T02'
. . . . .
Bạn mô tả rõ thêm công việc của bạn được không?

để tìm hướng mới í mà & hướng cũ ở trên là fá sản rồi!

Em nhập dữ liệu bằng tay như sau:
Danh sách đầu năm là có sẵn, do năm cũ chuyển qua. Em chép vào Sh Ma và ghi nó là T01, Tại T01 em chép các mã từ Sh Ma sang. Nếu tháng 01 có phát sinh mã mới thì em vẫn ghi là T01 ở hai nơi ( Sh Ma & T01). Sang tháng 02 em copy Sh T01 và sửa thành T02 (Vì có một số dữ liệu em phải theo dõi giữa các tháng cho liên tục). và tiếp tục quy trình.


Ngòai các điều kiện nói ở các bài trên thì em có đề nghị thêm như sau:
1/Nếu số liệu của năm trước chuyển qua thì ta thêm Sh T00 là của năm trước. như vậy sheet Tn sẽ lấy số liệu bên Sh Ma với điều kiện từ T00 đến Tn
2/ Riêng đặt hằng số tại cell A1 Sh Ma (mục đích là có bao nhiêu Sh T...) thì giúp em luôn đếm trong File có bao nhiêu Sh có chữ đầu tiên là "T". (Vì File nhiều người cùng sử dụng nên đễ quên thay đổi Hằng số này, Mặc khác khi em làm việc thì em đặt các Sh ẩn, chỉlàm việc với Sh hiện hành thôi)
3/ Khi chạy code thì chỉ chay code cho Sh hiện hành, không chạy các Sh T... khác, để em còn có thể kiểm sóat số liệu.
Em cảm ơn!

 

[Hong.Van]

(1)/ Con đường tơ lụa của dữ liệu của bạn là như thế nào nhỉ?Vào đầu năm bạn có danh sách bên trang 'Ma'; cũng trong tháng đó bạn chép sang 'T01';Trong tháng cũng đồng thời thêm dử liệu vô 'T01' cho người này, người kia;Sang tháng sau, lại nhập thêm vô 'Ma' & chép sang 'T02'. . . . . Bạn mô tả rõ thêm công việc của bạn được không?
để tìm hướng mới í mà & hướng cũ ở trên là fá sản rồi!

Em nhập dữ liệu bằng tay như sau:
Danh sách đầu năm là có sẵn, do năm cũ chuyển qua. Em chép vào Sh Ma và ghi nó là T01, Tại T01 em chép các mã từ Sh Ma sang. Nếu tháng 01 có phát sinh mã mới thì em vẫn ghi là T01 ở hai nơi ( Sh Ma & T01). Sang tháng 02 em copy Sh T01 và sửa thành T02 (Vì có một số dữ liệu em phải theo dõi giữa các tháng cho liên tục). và tiếp tục quy trình.Ngòai các điều kiện nói ở các bài trên thì em có đề nghị thêm như sau:
1/Nếu số liệu của năm trước chuyển qua thì ta thêm Sh T00 là của năm trước. như vậy sheet Tn sẽ lấy số liệu bên Sh Ma với điều kiện từ T00 đến Tn
2/ Riêng đặt hằng số tại cell A1 Sh Ma (mục đích là có bao nhiêu Sh T...) thì giúp em luôn đếm trong File có bao nhiêu Sh có chữ đầu tiên là "T". (Vì File nhiều người cùng sử dụng nên đễ quên thay đổi Hằng số này, Mặc khác khi em làm việc thì em đặt các Sh ẩn, chỉ làm việc với Sh hiện hành thôi)
3/ Khi chạy code thì chỉ chay code cho Sh hiện hành, không chạy các Sh T... khác, để em còn có thể kiểm sóat số liệu.
Em cảm ơn!

 

[ChanhTQ@]

Bạn kiểm theo macro này xem còn sai không nha:

Option Explicit
Sub InsertNewValueRow()
 Dim Sh As Worksheet, Rng As Range, sRng As Range, Cls As Range, Rg0 As Range
 Dim Rws As Long, jJ As Byte, Lop As Long
 Dim ShName As String
 
 Sheets("Ma").Select
 For Each Cls In Range([A12], [A65500].End(xlUp))
    If IsNumeric(Cls.Value) Then Lop = Cls.Value
    For jJ = 1 To [A1].Value
        ShName = "T" & Right("0" & CStr(jJ), 2)
        Set Sh = ThisWorkbook.Worksheets(ShName)
        Set Rng = Sh.Range(Sh.[b8], Sh.[B9999].End(xlUp))
        Set sRng = Rng.Find(Cls.Value, , xlFormulas, xlWhole)
        If sRng Is Nothing Then
            If Cls.Offset(, 4).Value <= ShName Then
                Set Rg0 = Rng.Find(Lop + 1)
                If Rg0 Is Nothing Then
                    Rws = Sh.[b9].End(xlDown).Row + 1
                    Sh.Rows(Rws & ":" & Rws).Insert Shift:=xlDown
                    Sh.Cells(Rws, "B").Resize(, 4).Value = Cls.Offset(, 4).Value
                Else
                    Sh.Rows(Rg0.Row & ":" & Rg0.Row).Insert Shift:=xlDown
                    Rg0.Offset(-1).Resize(, 4).Value = Cls.Resize(, 4).Value
                    'Exit For'
                End If
             End If
        End If
    Next jJ
 Next Cls
End Sub

 

[Hong.Van]

Em xin cảm ơn các thầy cô & anh chị giúp em hoàn chỉnh bài này!

Em muốn một ý như sau:
Khi InsertRow thì em muốn Insert từ cột A đến cột M (để không ảnh hưởng các cột phía sau), em nghỉ phải sửa ở đọan code này, nhưng thực hiện vẫn chưa được!

                If Rg0 Is Nothing Then
                    Rws = Sh.[b9].End(xlDown).Row + 1
                    Sh.Rows(Rws & ":" & Rws).Insert Shift:=xlDown
                    Sh.Cells(Rws, "B").Resize(, 4).Value = Cls.Offset(, 4).Value
                Else
                    Sh.Rows(Rg0.Row & ":" & Rg0.Row).Insert Shift:=xlDown
                    Rg0.Offset(-1).Resize(, 4).Value = Cls.Resize(, 4).Value

Nếu có thể, xin hướng dẫn em.

 

[SA_DQ]

2/ Riêng đặt hằng số tại cell A1 Sh Ma (mục đích là có bao nhiêu Sh T...) thì giúp em luôn đếm trong File có bao nhiêu Sh có chữ đầu tiên là "T". (Vì File nhiều người cùng sử dụng nên đễ quên thay đổi Hằng số này, Mặc khác khi em làm việc thì em đặt các Sh ẩn, chỉ làm việc với Sh hiện hành thôi)

Bạn xài thử macro sau:

Sub CountSheetsWithFirst_T()
 Dim Sh As Worksheet, NumSh As Byte
 For Each Sh In ThisWorkbook.Worksheets
    If Left(Sh.Name, 1) = "T" Then NumSh = 1 + NumSh
 Next Sh
 MsgBox NumSh
End Sub

3/ Khi chạy code thì chỉ chay code cho Sh hiện hành, không chạy các Sh T... khác, để em còn có thể kiểm sóat số liệu.

Chưa thể hiểu hết í của bạn!

Khi InsertRow thì em muốn Insert từ cột A đến cột M (để không ảnh hưởng các cột phía sau), em nghỉ phải sửa ở đọan code này, nhưng thực hiện vẫn chưa được!

Để không ảnh hưởng các cột phía sau 'M' khi 'Thêm' dòng, bạn có 2 cách lựa chọn 1:

(1) khi ta thêm dòng thứ i nào đó xong; Ta fải tiến hành copy toàn bộ dữ liệu của vùng từ ô trái nhỏ nhất (Ni+1) đến ô fải dưới nhất có dữ liệu đưa lên trên 1 hàng

(2) Ta copy toàn bộ dữ liệu từ vùng có ô trái nhỏ nhất là [Ai] cho đến ô cuối cùng có dữ liệu của cột 'M' đem xuống dưới 1 dòng.

/-(ãy lựa chọn, căn cứ vô dữ liệu thực của bạn.