blocks|key|199922|text|将包含数据和公式的区域设置为表格：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|199923|​|199924|📷|atomic|offset|length|199925|199926|然后，在下一行中添加新信息将为新行复制该表中的所有公式。数据验证也将应用于新行，就像应用于整个列一样。这确实是Excel更聪明地处理您的数据。|199927|不需要VBA+...|199928|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/032379f36f0ea649f050b60c777a76b2.jpeg|imageAlt^0|0|0|0|1|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|X|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Y|8|@]|9|@$G|Z|H|10|1|11]]|A|$]]|$1|I|3|C|5|6|7|12|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|13|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|14|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|O|$P|$5|Q|R|S|A|$T|U|V|-4]]]]

Make the area with your data and formulas a Table:

<img src="https://i.stack.imgur.com/2zCsL.jpg" alt="enter image description here">

Then adding new information in the next line will copy all formulas in that table for the new line. Data validation will also be applied for the new row as it was for the whole column. This is indeed Excel being smarter with your data.

NO VBA required...

blocks|key|418275|text|关于复制表中的行，我发现的另一个关键问题是您正在处理的工作表需要被激活。如果您的工作簿包含多个工作表，则需要保存从中调用宏的工作表，然后激活包含表的工作表。完成后，您可以重新激活原始工作表。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|418276|您可以使用Application.ScreenUpdating+=+False来确保用户看不到您正在宏内切换工作表。|418277|如果您没有激活工作表，则副本似乎无法正常工作，即某些内容似乎正常工作，而其他内容则不工作?？|418278|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

One other key thing that I found regarding copying rows within a table, is that the worksheet you are working on needs to be activated.
If you have a workbook with multiple sheets, you need to save the sheet you called the macro from, and then activate the sheet with the table.
Once you are done, you can re-activate the original sheet.

You can use Application.ScreenUpdating = False to make sure the user doesn't see that you are switching worksheets within your macro.

If you don't have the worksheet activated, the copy doesn't seem to work properly, i.e. some stuff seem to work, and other stuff doesn't ??

blocks|key|418353|text|如果您的工作表中有许多行都包含公式，到目前为止，最简单的方法是复制没有数据(但包含公式)的行，然后在要添加的行的下方/上方插入复制的单元格。公式保持不变。在必要的情况下，可以使用包含数据的行。只需清除它或在粘贴后覆盖它。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|418354|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

If you have a worksheet with many rows that all contain the formula, by far the easiest method is to copy a row that is without data (but it does contain formulas), and then "insert copied cells" below/above the row where you want to add. The formulas remain. In a pinch, it is OK to use a row with data. Just clear it or overwrite it after pasting.

blocks|key|418411|text|私有子目标(ByVal+Worksheet_Change+As+Range)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|418412|'data+starts+on+row+3+which+has+the+formulas
'the+sheet+is+protected+-+input+cells+not+locked+-+formula+cells+locked
'this+routine+is+triggered+on+change+of+any+cell+on+the+worksheet+so+first+check+if
'+it's+a+cell+that+we're+interested+in+-+and+the+row+doesn't+already+have+formulas
If+Target.Column+=+3+And+Target.Row+>+3+_
And+Range("M"+&+Target.Row).Formula+=+""+Then

++++On+Error+GoTo+ERROR_OCCURRED

++++'unprotect+the+sheet+-+otherwise+can't+copy+and+paste
++++ActiveSheet.Unprotect
++++'disable+events+-+this+prevents+this+routine+from+triggering+again+when
++++'copy+and+paste+below+changes+the+cell+values
++++Application.EnableEvents+=+False

++++'copy+col+D+(with+validation+list)+from+row+above+to+new+row+(not+locked)
++++Range("D"+&+Target.Row+-+1).Copy
++++Range("D"+&+Target.Row).PasteSpecial

++++'copy+col+M+to+P+(with+formulas)+from+row+above+to+new+row
++++Range("M"+&+Target.Row+-+1+&+":P"+&+Target.Row+-+1).Copy
++++Range("M"+&+Target.Row).PasteSpecial|code-block|syntax|javascript|418413|‘确保重新启用(或不重新启用)发生错误的事件并重新保护工作表|418414|ERROR_OCCURRED：|418415|++++If+Err.Number+<>+0+Then
++++++++MsgBox+"An+error+occurred.+Formulas+may+not+have+been+copied."+&+vbCrLf+&+vbCrLf+&+_
++++++++++++Err.Number+&+"+-+"+&+Err.Description
++++End+If

++++'re-enable+events
++++Application.EnableEvents+=+True
++++'re-protect+the+sheet
++++ActiveSheet.Protect

++++'put+focus+back+on+the+next+cell+after+routine+was+triggered
++++Range("D"+&+Target.Row).Select

End+If|418416|结束子对象|418417|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|U|8|@]|9|@]|A|$E|F]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

Private Sub Worksheet_Change(ByVal Target As Range)

<pre><code>'data starts on row 3 which has the formulas
'the sheet is protected - input cells not locked - formula cells locked
'this routine is triggered on change of any cell on the worksheet so first check if
' it's a cell that we're interested in - and the row doesn't already have formulas
If Target.Column = 3 And Target.Row &gt; 3 _
And Range("M" &amp; Target.Row).Formula = "" Then

 On Error GoTo ERROR_OCCURRED

 'unprotect the sheet - otherwise can't copy and paste
 ActiveSheet.Unprotect
 'disable events - this prevents this routine from triggering again when
 'copy and paste below changes the cell values
 Application.EnableEvents = False

 'copy col D (with validation list) from row above to new row (not locked)
 Range("D" &amp; Target.Row - 1).Copy
 Range("D" &amp; Target.Row).PasteSpecial

 'copy col M to P (with formulas) from row above to new row
 Range("M" &amp; Target.Row - 1 &amp; ":P" &amp; Target.Row - 1).Copy
 Range("M" &amp; Target.Row).PasteSpecial
</code></pre>

'make sure if an error occurs (or not) events are re-enabled and sheet re-protected

ERROR_OCCURRED:

<pre><code> If Err.Number &lt;&gt; 0 Then
 MsgBox "An error occurred. Formulas may not have been copied." &amp; vbCrLf &amp; vbCrLf &amp; _
 Err.Number &amp; " - " &amp; Err.Description
 End If

 're-enable events
 Application.EnableEvents = True
 're-protect the sheet
 ActiveSheet.Protect

 'put focus back on the next cell after routine was triggered
 Range("D" &amp; Target.Row).Select

End If
</code></pre>

End Sub

I have a row in which there are formulas using values of the same row.
The next row is empty, just with a different background color.

Now, if I insert a new row (by right-clicking on the empty row and "insert"), I get a new row with NO background color (which is what I want), but the line also does NOT contain any formulas: how do I get Excel to be smarter and copying the formulas from the previous row, when creating the new row?

One more piece of information: data validation information (i.e. a drop down list) is copied when inserting the new row.

Thanks.

Copying formula to the next row when inserting a new row

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

我有一行，其中有使用同一行的值的公式。下一行是空的，只是背景颜色不同。现在，如果我插入一个新行(通过右击空行并“插入”)，我得到一个没有背景颜色的新行(这是我想要的)，但该行也不包含任何公式:如何让Excel更智能，并在创建新行时复制前一行中的公式？另一条信息:数据验证信息(即下拉列表)在插入新行时被复制。谢谢。

问插入新行时将公式复制到下一行
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问插入新行时将公式复制到下一行EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问插入新行时将公式复制到下一行
EN