Giúp đỡ về macro fill-down (1 người xem)

[skullrock]

File tổng hợp của mình do rất nhiều dòng dữ liệu và công thức, nên ở mỗi nhóm mình chỉ giữ lại hàng đầu tiên đã có đủ công thức, khi cần chỉ cần bung copy cho các hàng phía dưới, mục đích để lưu file gọn nhẹ, chỉ bung full khi cần tới.
Mình đã tạo được 1 button, với code sau:

Sub Button26814_Click()'
' Button26814_Click Macro
' Macro recorded 12/3/2012 by pctuan
'


'
    Range("C7:M7").Select
    Selection.AutoFill Destination:=Range("C7:M25")
    Range("C27:M27").Select
    Selection.AutoFill Destination:=Range("C27:M45")
    Calculate
End Sub

Vấn đề xảy ra là khi chèn thêm hàng bên file dữ liệu (2 file đi kèm file tổng hợp), thì code trong button không tự động thay đổi theo đc. Chỉnh sửa lại là vấn đề khó khăn vì file của mình gồm gần cả nghìn nhóm như thế, khi có một hàng chèn vào thì toàn bộ các nhóm phía sau bị nhảy loạn lên hết.
Xin chỉ giáo mình cách để giữ sao cho code trong button hoạt động tốt khi có thêm dữ liệu vào mà chỉ cần chỉnh sửa ngay nhóm dữ liệu đó.
Có một cách khác thủ công hơn là sử dụng macro tạo phím tắt cho việc copy từng nhóm dữ liệu

Sub Macro4()'
' Macro4 Macro
' Macro recorded 12/3/2012 by pctuan
'
' Keyboard Shortcut: Ctrl+Shift+A
'
    ActiveCell.Range("A1:K1").Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:K19")
    Calculate
End Sub

Với cách này thì chỉ dùm mình cách để copy với số lượng dòng trong từng nhóm dữ liệu khác nhau mà vẫn chạy tốt. Vd trong code trên thì chỉ áp dụng đc cho nhóm có 19 dòng dữ liệu, khi nhóm có ít hơn hoặc nhiều hơn thì vẫn tự động chạy đủ.
Ở cả 2 cách còn một vấn đề nữa là khi số lượng dòng trong một nhóm cáp hơi lớn, khi double click để copy xuống bị báo lỗi "Selection is too large". Giúp mình xử lý luôn vấn đề này.
Mình trình bày hơi rối, mong các bác hiểu ý và giúp đỡ được mình.

 

[Quang_Hải]

File tổng hợp của mình do rất nhiều dòng dữ liệu và công thức, nên ở mỗi nhóm mình chỉ giữ lại hàng đầu tiên đã có đủ công thức, khi cần chỉ cần bung copy cho các hàng phía dưới, mục đích để lưu file gọn nhẹ, chỉ bung full khi cần tới.
Mình đã tạo được 1 button, với code sau:

Sub Button26814_Click()'
' Button26814_Click Macro
' Macro recorded 12/3/2012 by pctuan
'


'
    Range("C7:M7").Select
    Selection.AutoFill Destination:=Range("C7:M25")
    Range("C27:M27").Select
    Selection.AutoFill Destination:=Range("C27:M45")
    Calculate
End Sub

Với cách này thì chỉ dùm mình cách để copy với số lượng dòng trong từng nhóm dữ liệu khác nhau mà vẫn chạy tốt. Vd trong code trên thì chỉ áp dụng đc cho nhóm có 19 dòng dữ liệu, khi nhóm có ít hơn hoặc nhiều hơn thì vẫn tự động chạy đủ.
Ở cả 2 cách còn một vấn đề nữa là khi số lượng dòng trong một nhóm cáp hơi lớn, khi double click để copy xuống bị báo lỗi "Selection is too large". Giúp mình xử lý luôn vấn đề này.
Mình trình bày hơi rối, mong các bác hiểu ý và giúp đỡ được mình.

Không biết là đối với người khác thì có thấy hơi rối hay không chứ mình thấy không phải hơi rối mà là quá rối.
Thử cái này coi sao

Sub copy_test()
[C7:M7].copy Range("C7:M" & [B7].End(4).Row)
[C27:M27].copy Range("C27:M" & [B27].End(4).Row)
End Sub

 

[skullrock]

Không biết là đối với người khác thì có thấy hơi rối hay không chứ mình thấy không phải hơi rối mà là quá rối.
Thử cái này coi sao

Sub copy_test()
[C7:M7].copy Range("C7:M" & [B7].End(4).Row)
[C27:M27].copy Range("C27:M" & [B27].End(4).Row)
End Sub

Code này giải quyết được copy với số dòng bất kỳ, nhưng khi chèn thêm hay xóa 1 dòng ở nhóm trên thì địa chỉ C27:M27 không còn đúng nữa mà nó phải là C26:M26 hay C28:M28. Do bảng dữ liệu này có thể thường xuyên phải cập nhật với nội dung rất nhiều nên không thể thay đổi code bằng tay được. Có cách nào định địa chỉ tương đối ở đây không, và còn vấn đề là khi số dòng cần copy lớn code sẽ báo bug.
Cám ơn bạn đã nhiệt tình giúp đỡ dù quá rối, rối chỗ nào thì mình làm rõ lại liền.

 

[Quang_Hải]

Code này giải quyết được copy với số dòng bất kỳ, nhưng khi chèn thêm hay xóa 1 dòng ở nhóm trên thì địa chỉ C27:M27 không còn đúng nữa mà nó phải là C26:M26 hay C28:M28. Do bảng dữ liệu này có thể thường xuyên phải cập nhật với nội dung rất nhiều nên không thể thay đổi code bằng tay được. Có cách nào định địa chỉ tương đối ở đây không, và còn vấn đề là khi số dòng cần copy lớn code sẽ báo bug.
Cám ơn bạn đã nhiệt tình giúp đỡ dù quá rối, rối chỗ nào thì mình làm rõ lại liền.

Thử vầy coi, cũng không hiểu hết tình huống của bạn

Sub copy_test()
[C7:M7].copy Range("C7:M" & [C7].End(4).Offset(-1).Row)
[C27:M27].copy Range("C27:M" & [B65536].End(3).Row)
End Sub

 

[ptm0412]

Với code sau đây thì bao nhiêu nhóm, nhóm bao nhiêu dòng đều chạy tuốt. Chỉ cần đánh số thứ tự đúng và mỗi nhóm có cách nhau 1 hoặc vài dòng.

Sub FillFormula()
Dim EndR As Long, BeginR As Long
    EndR = Sheet5.[A65000].End(xlUp).Row
    BeginR = Sheet5.Cells(EndR, 1).End(xlUp).Row
Do
    Sheet5.Cells(BeginR, 3).Resize(1, 11).AutoFill _
    Destination:=Sheet5.Cells(BeginR, 3).Resize(EndR - BeginR + 1, 11)
    EndR = Sheet5.Cells(BeginR, 1).End(xlUp).Row
    BeginR = Sheet5.Cells(EndR, 1).End(xlUp).Row
    If BeginR < 6 Then Exit Do
Loop
End Sub

 

[skullrock]

Hay quá bro ơi, 1 đoạn code ngắn giải quyết đúng vấn đề của mình rồi, cám ơn mọi người giúp mình giải quyết được công việc này.

 

[skullrock]

ah mà vẫn còn bị giới hạn số dòng copy trong 1 nhóm, trong file gốc mình có nhóm gần 100 dòng, khi chạy code báo lỗi bug "Selection is too large" như khi double click để copy thông thường cũng bị báo như thế. Có cách nào khắc phục được thêm chỗ này ko.

 

[ptm0412]

Vậy thì sửa .AutoFill thành .Copy là xong

    Sheet5.Cells(BeginR, 3).Resize(1, 11).Copy _
    Destination:=Sheet5.Cells(BeginR + 1, 3).Resize(EndR - BeginR, 11)

 

[skullrock]

chạy ngon lành rồi, cám ơn bro nhìu

 

[skullrock]

ptm0412 giúp mình lần nữa với, file dữ liệu mình có 125 cột, 10000 dòng. Với số lượng dòng ít thì chạy ok, nhưng với file lớn vậy thì chạy vẫn bị báo lỗi "Selection is too large".
Cho mình cái giải pháp nào để xử lý nó giúp, hay là phải dùng cách chia nhỏ ra để copy.
Thanks !!!

 

[ptm0412]

Bạn thử code này:

Sub CopyFormula()
Dim EndR As Long, BeginR As Long
    EndR = Sheet5.[A65000].End(xlUp).Row
    BeginR = Sheet5.Cells(EndR, 1).End(xlUp).Row
Do
    Sheet5.Cells(BeginR, 3).Resize(1, 11).Copy _
    Destination:=Sheet5.Cells(BeginR + 1, 3).Resize(EndR - BeginR, 11) 
    
    'thêm 2 dòng'
    Sheet5.Cells(BeginR + 1, 3).Resize(EndR - BeginR, 11).Value = _
    Sheet5.Cells(BeginR + 1, 3).Resize(EndR - BeginR, 11).Value
 
    EndR = Sheet5.Cells(BeginR, 1).End(xlUp).Row
    BeginR = Sheet5.Cells(EndR, 1).End(xlUp).Row
    If BeginR < 6 Then Exit Do
Loop
End Sub

(nhớ đổi 11 thành 125 gì đó)
Nếu vẫn không được thì chia nhỏ ra

 

[skullrock]

hic, không hiểu gì hết, hôm qua lấy code về chạy đc, hôm nay mở file lên ko chạy được, dữ liệu >25 dòng là lại bị "Selection is too large". Vây chắc là excel bị gì rồi phải không bạn, double click không đuợc, chọn vùng copy cũng không.

 

[ptm0412]

Vậy chia nhỏ thành cụm 20 dòng, OK?

 

[skullrock]

chia sao chỉ dùm với anh, trong 1 nhóm không tách ra thành từng cụm đuợc không, chỉ sửa code thôi

 

[skullrock]

P/S: thử clear format cả row cần copy thì có thể double click copy phạm vi 125 cột, 100 dòng.

 

[ptm0412]

Chia cụm tất nhiên là chia bằng code:
- Vẫn chia nhóm
- Nếu nhóm nhỏ hơn 50 dòng, copy 1 lần
- Nếu nhóm nhiều hơn 50 dòng, chia từng cụm 50 dòng.

Xử lý từng cụm một.

Tất nhiên cũng còn cách dùng mảng nhanh hơn và tốt hơn thay vì copy hay Fill, nhưng đây là cách căn bản dễ hiểu.

Sub CopyFormula()
Dim EndR As Long, BeginR As Long
    EndR = Sheet5.[A65000].End(xlUp).Row
    BeginR = Sheet5.Cells(EndR, 1).End(xlUp).Row
Do
    If Sheet5.Cells(EndR, 1) = 1 Then BeginR = EndR: GoTo Skip1
    GrCount = Int((EndR - BeginR) / 50)
    RestR = (EndR - BeginR) Mod 50
If GrCount > 0 Then
    For i = 1 To GrCount
         Sheet5.Cells(BeginR, 3).Resize(1, 125).Copy _
         Destination:=Sheet5.Cells(BeginR + 1 + (i - 1) * 50, 3).Resize(50, 125)
    Next
End If
If RestR > 0 Then
    Sheet5.Cells(BeginR, 3).Resize(1, 125).Copy _
    Destination:=Sheet5.Cells(BeginR + 1 + GrCount * 50, 3).Resize(RestR, 125)
End If
         Sheet5.Cells(BeginR + 1, 3).Resize(EndR - BeginR, 125).Value = _
         Sheet5.Cells(BeginR + 1, 3).Resize(EndR - BeginR, 125).Value
Skip1:
    EndR = Sheet5.Cells(BeginR, 1).End(xlUp).Row
    BeginR = Sheet5.Cells(EndR, 1).End(xlUp).Row
    If BeginR < 6 Then Exit Do


Loop
End Sub

 

[skullrock]

đoạn code trên là copy / paste value hả bạn, paste thường thôi chỉnh lại chỗ nào chỉ giúp mình, phải lấy công thức để chạy.

 

[ptm0412]

10.000 dòng x 125 cột = 1.250.000 ô chứa công thức.
Bạn muốn vừa làm vừa chơi chờ nó tính hay sao?

 

[skullrock]

đầu mỗi dòng trong 1 nhóm là 1 mã sản phẩm, dựa vào mã đó để vlookup ra các thông số khác. Nếu copy / paste value như trên thì chưa vlookup đã paste value như hàng đầu rồi. Kết quả trong 1 nhóm, hàng nào cũng giống như hàng nào mất rồi.
Toàn bộ công thức chỉ là link qua file khác để lấy giá trị thôi, nhưng nó vẫn phải là công thức mà.
Mình định sau khi chạy copy full hết thì dùng 1 macro paste value sau. Lúc đó save as lại thành 1 file nhẹ hơn để sử dụng cho mục đích nào đó. File gốc thì vẫn là file chưa copy.
Mình bik khi nó chạy copy sẽ mất thời gian, nhưng đành phải chịu như vậy mà.
Giúp mình nốt copy thường thôi nha.
Thanks

 

[ptm0412]

Đó là copy thường
paste xong lookup lookiếc rồi mới fix value
Không tin bạn xóa dòng code fix value này xem có phải là lookup hay không:

Sheet5.Cells(BeginR + 1, 3).Resize(EndR - BeginR, 125).Value = _
Sheet5.Cells(BeginR + 1, 3).Resize(EndR - BeginR, 125).Value