如何用vba实现自动循环复制前面的行? vba 如何实现在插入新行后自动复制上一行某个单元格公式?
\u600e\u6837\u5728excel\u91cc\u7528vba\u4e4b\u7c7b\u7684\u5b9e\u73b0\u70b9\u51fb\u4e00\u4e2a\u6309\u94ae\u540e\u4e00\u884c\u6570\u636e\u81ea\u52a8\u590d\u5236\u7c98\u8d34\u5230\u6307\u5b9a\u884c\uff1f\u5047\u8bbe\u4f60\u6570\u636e\u5728A2\u5230F2\u91cc\u8f93\u5165\u3002\u4efb\u610f\u63d2\u5165\u4e00\u56fe\u5f62\uff0c\u53f3\u952e\u70b9\u8be5\u56fe\u5f62\uff0c\u6307\u5b9a\u5b8f\uff0c\u65b0\u5efa\u3002\u5728\u51fa\u6765\u7684\u7a97\u53e3\u4e2d\u518dSub\u548cEnd Sub\u4e4b\u95f4 \u7c98\u8d34\u5982\u4e0b\u4ee3\u7801\uff1a
Dim i, j As Integer
i = --Range("A2")
For j = 1 To 6
Cells(i, j) = Cells(2, j)
Next j
\u56de\u5230Excel\uff0c\u5c31\u6709\u4e86\u4f60\u8981\u7684\u3002
Dim formula As Variant
Dim r As Integer
'\u5148\u83b7\u53d6\u5f53\u524d\u9009\u62e9\u7684\u884c
r = Selection.Row
'\u53d6\u4e0a\u4e00\u884cJ\u5217\u7684\u516c\u5f0f
formula = Range("J" & CStr(r - 1)).formula '\u5fc5\u987b\u5728\u7b2c\u4e8c\u884c\u4ee5\u540e\u63d2\u5165
'\u63d2\u5165\u4e00\u884c
Selection.Insert Shift:=xlDown
'\u8bbe\u7f6e\u5f53\u524d\u884c\u7684J\u884c\u516c\u5f0f\u4e3a\u4e0a\u4e00\u884c
Range("J" & CStr(r)).formula = formula
Sub MCOPY()
Rows("1:2").Copy'将第1/2行数据复制到粘贴板中
For i = 1 To 100'循环100次
Range("A" & i * 2 + 1).PasteSpecial‘从第“A3"开始,每隔一个(即A3、A5、A7...)粘贴粘贴板中的数据
Next
End Sub
这里需要讲解的一点小知识就是,在Excel中复制行时,可以选择第一个单元格进行粘贴,就能粘贴整行内容。
以下代码满足你的要求,此程序复制100次,改变此数满足你的次数即可
Sub MyCopy()
Rows("1:2").Copy
For i = 1 To 100
Range("A" & i * 2 + 1).PasteSpecial
Next
End Sub
绛旓細杩斿洖Excel涓荤晫闈紝鐐瑰嚮"寮鍙戝伐鍏"涓嬬殑"瀹"锛屾壘鍒板苟閫夋嫨"澶嶅埗琛"锛岀劧鍚庣偣鍑"鎵ц"鎸夐挳銆傛鏃讹紝"宸ヤ綔琛1"涓殑鎸囧畾宸ヤ綔琛"绉诲姩琛"宸叉垚鍔熷鍒跺埌"宸ヤ綔琛2"鐨勭1涓伐浣滆〃涓紝鎿嶄綔杩囩▼鐩磋涓旈珮鏁堛傞氳繃杩欑鏂瑰紡锛屼綘鍙互杞绘澗鍦浣跨敤VBA鍦‥xcel涓瀹炵幇宸ヤ綔琛ㄧ殑澶嶅埗锛屾棤闇鎵嬪姩鎿嶄綔锛屽ぇ澶ф彁楂樹簡宸ヤ綔鏁堢巼銆
绛旓細Sheets("Sheet1").Select Rows("1:1").Select Selection.Copy Sheets("Sheet2").Select Rows("2:2").Select ActiveSheet.Paste ActiveSheet.Paste 杩欐槸鎶妔heet1鐨勭涓琛澶嶅埗鍒皊heet2鐨勭浜岃鐨勪唬鐮侊紝鑷充簬姣忔澶嶅埗鍒颁笅涓琛岋紝妤间富鍙互鏇存敼Row锛堚2锛2鈥滐級閲岄潰鐨2锛屾崲鎴恑锛屾瘡娆=i+1,鎴栬呯敤...
绛旓細鍙互浣跨敤VBA涓殑If璇彞鍜孎or Each寰幆鏉瀹炵幇銆傞鍏堬紝鍦╒BA涓畾涔夌鍚堟潯浠剁殑鏁版嵁锛屽彲浠ヤ娇鐢ㄧ被浼间笅闈㈢殑浠g爜锛欴im targetSheet As Worksheet Dim sourceSheet As Worksheet Dim lastRow As Long Dim i As Long Set sourceSheet = Worksheets("Sheet1") ' 鎸囧畾婧愭暟鎹墍鍦ㄥ伐浣滆〃 Set targetSheet = ...
绛旓細Sub EXCEL鍐呭澶嶅埗鏂囨。() Dim lStartRow As Long Dim lEndRow As Long Dim lRow As Long Dim lCount As Long Dim Arr Dim str As String Dim objFSO As Object Dim objText As Object Set objFSO = CreateObject("Scripting.FileSystemObject") lStartRow = 1 lEndRow = ...
绛旓細Dim a As Integer For a = 1 To Worksheets.Count Cells(a, 1) = Worksheets(a).Cells(3, 3)Dim rg As Range Set rg = Worksheets(a + 1).Range("c3").End(xlToRight).End(xlDown)Worksheets(a + 1).Activate Worksheets(a + 1).Range("c3", rg).Select Selection.Copy Worksheets...
绛旓細杩欎釜鏃ユ湡涓嶈璁╁畠鑷姩鏇存柊锛岀▼搴忕殑鍔熻兘濡備笅 鎵撳紑鏃跺厛妫鏌ヤ竴涓嬭繖涓棩鏈燂紝鏄惁鏄粖澶╂棩鏈燂紝濡傛灉鏄紝璇存槑浠婂ぉ宸茬粡鎵撳紑杩囦簡锛屽鏋滀笉鏄紝鍒欏皢浣犺鐨勬湡鏈暟澶嶅埗鍒板叾鍒濇暟锛屽悓鏃舵竻绌烘湡鏈爮锛屽悓鏃惰繕瑕佹洿鏂伴偅涓棩鏈熶负浠婂ぉ鐨勬棩鏈燂紝璇曡瘯鍚 璁颁綇浜嗭紝鐢ㄨ繖涓姛鑳界殑鍓嶆彁鏄紝绯荤粺鏃ユ湡涓嶈兘鍑洪敊锛屽惁鍒欍傘傘
绛旓細Sub 澶嶅埗()For i = 2 To Range("a65536").End(xlUp).Row For n = 2 To Sheets.Count If Cells(i, "g") = n Then x = Sheets(n).Range("a65536").End(xlUp).Row Range(Cells(i, "A"), Cells(i, "I")).Copy Sheets(n).Cells(x + 1, "A")End If Next Next End ...
绛旓細Sub 鍗曠エ()Dim x As Long x = 3 With Sheet9 If IsNumber(.TextBox1.Value) Then If .TextBox1.Value > 3 Then x = .TextBox1.Value End If Else .TextBox1.Value = 3 End If .Range("b7").Value = Sheet2.Range("AA" & x).Value .Range("c7").Value = Sheet2.Range("...
绛旓細Dim formula As Variant Dim r As Integer '鍏堣幏鍙栧綋鍓嶉夋嫨鐨勮 r = Selection.Row '鍙栦笂涓琛孞鍒楃殑鍏紡 formula = Range("J" & CStr(r - 1)).formula '蹇呴』鍦ㄧ浜岃浠ュ悗鎻掑叆 '鎻掑叆涓琛 Selection.Insert Shift:=xlDown '璁剧疆褰撳墠琛岀殑J琛屽叕寮忎负涓婁竴琛 Range("J" & CStr(r))....
绛旓細Sub 澶嶅埗() For Each rng In Range("c1:c" & [c65536].End(3).Row) If [a1] = "" Then i = 1 Else i = [a65536].End(3).Row + 1 If rng <> "" Then Range(Cells(rng.Row, 3), Cells(rng.Row, 4)).Copy Cells(i, 1).PasteSpecial Paste:=xlValues ...