VBA copy công thức khi thêm dòng.

Duong gia · 8/11/07

Các Bác ơi giúp với, trong bảng tính Excel khi Insert dòng có cách nào VBA copy công thức từ trên xuống không? Thanks in advance.

tedaynui · 8/11/07

Duong gia đã viết:
Các Bác ơi giúp với, trong bảng tính Excel khi Insert dòng có cách nào VBA copy công thức từ trên xuống không? Thanks in advance.

Ví dụ khi bạn chèn ở dòng 4, bạn tham khảo code sau

Mã:

With Rows("4:4").Select
    Selection.Insert Shift:=xlDown
    Selection.FillDown
End With

TDN

Duong gia · 9/11/07

Tedaynui bạn nói rõ hơn được không, thanks

kietbui · 10/11/07

Nếu chỉ insert 1 dòng ngay ô chọn rồi copy công thức ( và format ) dòng trên đó xuống bạn có thể sử dụng code này xem sao

Mã:

Sub insert_row()
'
' insert_row Macro
' Macro recorded 12-11-2007 by TDE
'
'
Selection.EntireRow.Insert
ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

tôi có sử dụng trường hợp này để tự động làm " nở" vùng lưu dư liệu có chứa công thức.
thí dụ : tôi có sheet1 làm form phiếu xuất, sheet2 là lưu dữ liệu phiếu xuất có 1 vùng tên là dulieu_xuat.nếu vùng này lớn thì các link làm máy chậm, nếu vùng này nhỏ thí đến 1 lúc nào đó sẽ đầy.cho nên trước khi cho chạy macro in phiếu xuất nó sẽ kiểm tra nếu vùng dulieu_xuat còn dưới 50 dòng thì nó chèn vô 500 dòng. Sau đó nó tìm đến dòng có chứa công thức ngay trên vùng 500 dòng mới insert copy dòng này vào vùng mới chèn ( trong ví dụ vùng dulieu_xuat là (A:L), "o1_vstt_xuat","bottom_vstt_xuat" là ô đầu tiên và ô cuối cùng của vùng dữ liệu có chứa công thức

Mã:

Sub resize_xuat()
'
' resize_xuat Macro
' Macro recorded 03-11-2007 by TDE
'
    Range("A1").Select
    Application.Goto Reference:="bottom_vstt_xuat"
    ActiveCell.Range("A1:A500").Select
    Selection.EntireRow.Insert
    Application.Goto Reference:="o1_vstt_xuat"
    Selection.End(xlDown).Select
    ActiveCell.Range("A1:l1").Select
    Selection.Copy
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A1").Select
End Sub

tedaynui · 10/11/07

Duong gia đã viết:
Tedaynui bạn nói rõ hơn được không, thanks

Bạn thắc mắc chỗ nào ?

phamduylong · 11/11/07

NHG đã viết:
Tedaynui nói rất đúng trong trường hợp chèn vào dòng thứ 4, nhưng mình muốn hỏi nếu chèn vào một dòng bất kỳ thì nó tự động Fill công thức ở dòng trên xuống thì làm thế nào

Muốn chèn một dòng và fill công thức xuống, thì Excel phải biết hành động chèn dòng, và hành động này chỉ xảy ra trong 1 sheet này. Nhưng làm điều này thì tôi chưa làm được.
Để sheet có thể nhận biết chúng ta muốn chèn dòng, trong InsertRow tôi dùng hành động doubleclick gán cho nó. Khi DoubleClick, Excel sẽ kiểm tra các ô ở dòng trên, nếu ô nào là công thức thì copy xuống.

Mã:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
r = Target.Row   'xác định dòng chèn
If r = 1 Then Exit Sub  'nếu chèn dòng 1 bỏ qua
cc = Target.SpecialCells(xlCellTypeLastCell).Column 'xác định cột dữ liệu cuối cùng
Target.EntireRow.Insert
For c = 1 To cc
  If Cells(r - 1, c).HasFormula = True Then   'kiểm tra ô có công thức
    Cells(r, c).FormulaR1C1 = Cells(r - 1, c).FormulaR1C1
  End If
Next
End Sub

tedaynui · 11/11/07

NHG đã viết:
Tedaynui nói rất đúng trong trường hợp chèn vào dòng thứ 4, nhưng mình muốn hỏi nếu chèn vào một dòng bất kỳ thì nó tự động Fill công thức ở dòng trên xuống thì làm thế nào

Nếu chèn dòng bất kỳ và chỉ chép ô công thức thì bạn làm theo cách của thầy Long (Kiểm tra ô nào là công thức mới copy)

Nếu chèn dòng bất kỳ tại ô hiện hành và Fill cả dòng xuống thì bạn làm như sau

Mã:

With ActiveCell.EntireRow.Select
    Selection.Insert Shift:=xlDown
    Selection.FillDown
End With

TDN

huuthanhxd · 29/8/14

phamduylong đã viết:
Muốn chèn một dòng và fill công thức xuống, thì Excel phải biết hành động chèn dòng, và hành động này chỉ xảy ra trong 1 sheet này. Nhưng làm điều này thì tôi chưa làm được.
Để sheet có thể nhận biết chúng ta muốn chèn dòng, trong InsertRow tôi dùng hành động doubleclick gán cho nó. Khi DoubleClick, Excel sẽ kiểm tra các ô ở dòng trên, nếu ô nào là công thức thì copy xuống.

Mã:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Cancel = True r = Target.Row 'xác định dòng chèn If r = 1 Then Exit Sub 'nếu chèn dòng 1 bỏ qua cc = Target.SpecialCells(xlCellTypeLastCell).Column 'xác định cột dữ liệu cuối cùng Target.EntireRow.Insert For c = 1 To cc If Cells(r - 1, c).HasFormula = True Then 'kiểm tra ô có công thức Cells(r, c).FormulaR1C1 = Cells(r - 1, c).FormulaR1C1 End If Next End Sub

Cho hỏi nếu muốn dùng cách này mà khi click đúp chuột thì nó chèn bên dưới chứ không phải bên trên dòng hiện thời thì làm thế nào bác ơi?

Miccpro · 29/10/15

phamduylong đã viết:
Muốn chèn một dòng và fill công thức xuống, thì Excel phải biết hành động chèn dòng, và hành động này chỉ xảy ra trong 1 sheet này. Nhưng làm điều này thì tôi chưa làm được.
Để sheet có thể nhận biết chúng ta muốn chèn dòng, trong InsertRow tôi dùng hành động doubleclick gán cho nó. Khi DoubleClick, Excel sẽ kiểm tra các ô ở dòng trên, nếu ô nào là công thức thì copy xuống.

Mã:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Cancel = True r = Target.Row 'xác định dòng chèn If r = 1 Then Exit Sub 'nếu chèn dòng 1 bỏ qua cc = Target.SpecialCells(xlCellTypeLastCell).Column 'xác định cột dữ liệu cuối cùng Target.EntireRow.Insert For c = 1 To cc If Cells(r - 1, c).HasFormula = True Then 'kiểm tra ô có công thức Cells(r, c).FormulaR1C1 = Cells(r - 1, c).FormulaR1C1 End If Next End Sub

Cái này có thể thay thế DoubleClick chuột trái bằng CommandButton được không ạ. DoubleClick chuột trái thấy bất tiện quá $:=\+$ $:=\+$ $:=\+$

giaiphap · 29/10/15

Miccpro đã viết:
Cái này có thể thay thế DoubleClick chuột trái bằng CommandButton được không ạ. DoubleClick chuột trái thấy bất tiện quá $:=\+$ $:=\+$ $:=\+$

Được, bạn thay Target thành Selection

Miccpro · 30/10/15

giaiphap đã viết:
Được, bạn thay Target thành Selection

Phiền bác thay dum em cái ạ, em thay vào nó bát nháo luôn. Híc, gà quá +-+-+-+

phulien1902 · 30/10/15

Miccpro đã viết:
Phiền bác thay dum em cái ạ, em thay vào nó bát nháo luôn. Híc, gà quá

Tôi thấy bình thường mà, còn bạn thay thế nào, có cần gửi File để tham khảo không bạn?

Miccpro · 30/10/15

phulien1902 đã viết:
Tôi thấy bình thường mà, còn bạn thay thế nào, có cần gửi File để tham khảo không bạn?

Bác gửi giúp em cái được không ạ.

phulien1902 · 30/10/15

Miccpro đã viết:
Bác gửi giúp em cái được không ạ.

Bạn tham khảo File dưới đây

Miccpro · 30/10/15

phulien1902 đã viết:
Bạn tham khảo File dưới đây

Nhờ bác thêm em cái như hôm qua, nhập số dòng cần chèn được không ạ. Em cảm ơn bác nhiều nhiều lắm }}}}}

P/s: Vì lính mới hoàn toàn nên nhiều khi hỏi toàn câu ngớ ngẩn. Mong các bác thông cảm và chỉ bảo ạ

JungSangAh · 26/6/18

kietbui đã viết:
Nếu chỉ insert 1 dòng ngay ô chọn rồi copy công thức ( và format ) dòng trên đó xuống bạn có thể sử dụng code này xem sao

Mã:

Sub insert_row() ' ' insert_row Macro ' Macro recorded 12-11-2007 by TDE ' ' Selection.EntireRow.Insert ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End Sub

tôi có sử dụng trường hợp này để tự động làm " nở" vùng lưu dư liệu có chứa công thức.
thí dụ : tôi có sheet1 làm form phiếu xuất, sheet2 là lưu dữ liệu phiếu xuất có 1 vùng tên là dulieu_xuat.nếu vùng này lớn thì các link làm máy chậm, nếu vùng này nhỏ thí đến 1 lúc nào đó sẽ đầy.cho nên trước khi cho chạy macro in phiếu xuất nó sẽ kiểm tra nếu vùng dulieu_xuat còn dưới 50 dòng thì nó chèn vô 500 dòng. Sau đó nó tìm đến dòng có chứa công thức ngay trên vùng 500 dòng mới insert copy dòng này vào vùng mới chèn ( trong ví dụ vùng dulieu_xuat là (A:L), "o1_vstt_xuat","bottom_vstt_xuat" là ô đầu tiên và ô cuối cùng của vùng dữ liệu có chứa công thức

Mã:

Sub resize_xuat() ' ' resize_xuat Macro ' Macro recorded 03-11-2007 by TDE ' Range("A1").Select Application.Goto Reference:="bottom_vstt_xuat" ActiveCell.Range("A1:A500").Select Selection.EntireRow.Insert Application.Goto Reference:="o1_vstt_xuat" Selection.End(xlDown).Select ActiveCell.Range("A1:l1").Select Selection.Copy Range(Selection, Selection.End(xlDown)).Select ActiveSheet.Paste Application.CutCopyMode = False Range("A1").Select End Sub

Anh làm ơn cho em hỏi chút ạ. Làm sao để khai báo cho excel hiểu bottom_vstt_xuat và o1_vstt_xuat vậy ạ. Em chưa hiểu rõ về VBA mong anh chỉ giúp ạ

Bài đã được tự động gộp: 26/6/18

kietbui đã viết:
Nếu chỉ insert 1 dòng ngay ô chọn rồi copy công thức ( và format ) dòng trên đó xuống bạn có thể sử dụng code này xem sao

Mã:

Sub insert_row() ' ' insert_row Macro ' Macro recorded 12-11-2007 by TDE ' ' Selection.EntireRow.Insert ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End Sub

tôi có sử dụng trường hợp này để tự động làm " nở" vùng lưu dư liệu có chứa công thức.
thí dụ : tôi có sheet1 làm form phiếu xuất, sheet2 là lưu dữ liệu phiếu xuất có 1 vùng tên là dulieu_xuat.nếu vùng này lớn thì các link làm máy chậm, nếu vùng này nhỏ thí đến 1 lúc nào đó sẽ đầy.cho nên trước khi cho chạy macro in phiếu xuất nó sẽ kiểm tra nếu vùng dulieu_xuat còn dưới 50 dòng thì nó chèn vô 500 dòng. Sau đó nó tìm đến dòng có chứa công thức ngay trên vùng 500 dòng mới insert copy dòng này vào vùng mới chèn ( trong ví dụ vùng dulieu_xuat là (A:L), "o1_vstt_xuat","bottom_vstt_xuat" là ô đầu tiên và ô cuối cùng của vùng dữ liệu có chứa công thức

Mã:

Sub resize_xuat() ' ' resize_xuat Macro ' Macro recorded 03-11-2007 by TDE ' Range("A1").Select Application.Goto Reference:="bottom_vstt_xuat" ActiveCell.Range("A1:A500").Select Selection.EntireRow.Insert Application.Goto Reference:="o1_vstt_xuat" Selection.End(xlDown).Select ActiveCell.Range("A1:l1").Select Selection.Copy Range(Selection, Selection.End(xlDown)).Select ActiveSheet.Paste Application.CutCopyMode = False Range("A1").Select End Sub

Anh cho em hỏi. Trường hợp ở code Insert row, em muốn thêm một số dòng cụ thể và vẫn copy dữ liệu và công thức như ở dòng đó thì viết code như nào vậy ạ? Em cảm ơn!

trantiepkk · 12/4/20

Miccpro đã viết:
Cái này có thể thay thế DoubleClick chuột trái bằng CommandButton được không ạ. DoubleClick chuột trái thấy bất tiện quá $:=\+$ $:=\+$ $:=\+$

Cho e hỏi chút.
Nếu data được INPUT từ form nhập của VBA thì code sẽ như nào vậy.
E nhập tay thì dc. Nhưng nếu dùng form input data vào ô thì ko copy dc công thức

Quangle@ · 3/11/21

tedaynui đã viết:
Nếu chèn dòng bất kỳ và chỉ chép ô công thức thì bạn làm theo cách của thầy Long (Kiểm tra ô nào là công thức mới copy)

Nếu chèn dòng bất kỳ tại ô hiện hành và Fill cả dòng xuống thì bạn làm như sau

Mã:

With ActiveCell.EntireRow.Select Selection.Insert Shift:=xlDown Selection.FillDown End With

TDN

Chào bạn cho mình hỏi, mình muốn chọn dòng tuỳ ý, thì phải như thế nào

khoang lang hg86 · 26/11/21

Mọi người cho em hỏi là tại sao dòng code của thầy Long lại chỉ đúng từ dòng thứ 6 trở đi với ạ?

VBA copy công thức khi thêm dòng.

Thành viên thường trực

(*_*)

Thành viên thường trực

Thành viên mới

(*_*)

-

File đính kèm

(*_*)

Thành viên mới

Thành viên thường trực

==(^o^)==

Thành viên thường trực

GPE - My love

Thành viên thường trực

GPE - My love

File đính kèm

Thành viên thường trực

Thành viên mới

Thành viên mới

Thành viên mới

Thành viên mới

File đính kèm

Facebook

Group

(_)

(_)

(_)