vba 如何创建宏以将数据从一个 Excel 工作表复制到另一个工作表中的下一行

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/13455720/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-08 14:26:52  来源:igfitidea点击:

How to create a macro to copy data from one excel worksheet to the next line in another worksheet

excelexcel-vbacopyvba

提问by user1835992

I'm a bit stumped with this and wondered if anyone could help please? I've created a macro in excel to copy some data from one worksheet to another. It works fine, but obviously each time I run the macro, it just completes the top line (as I pasted to whilst recording). I actually want it work so that every time I run the macro, it copies the data to a new line.

我对此有点困惑,想知道是否有人可以提供帮助?我在 excel 中创建了一个宏来将一些数据从一个工作表复制到另一个工作表。它工作正常,但很明显,每次我运行宏时,它都会完成第一行(正如我在录制时粘贴的那样)。我实际上希望它工作,以便每次运行宏时,它都会将数据复制到新行。

If it helps, I can paste a link to the document. I'm sure it's just a case of pasting some VBA code into the macro. At the moment, the code looks like this:

如果有帮助,我可以粘贴该文档的链接。我确定这只是将一些 VBA 代码粘贴到宏中的情况。目前,代码如下所示:

Sub Macro1()
'
' Macro1 Macro
'

'
    Sheets("Quotation System").Select
    Range("K9").Select
    Selection.Copy
    Sheets("Confirmed Bookings").Select
    Range("A2").Select
    ActiveSheet.Paste
    Range("B2").Select
    Sheets("Quotation System").Select
    Range("K11").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Confirmed Bookings").Select
    ActiveSheet.Paste
    Sheets("Quotation System").Select
    Range("K13").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Confirmed Bookings").Select
    Range("C2").Select
    ActiveSheet.Paste
    Sheets("Quotation System").Select
    Range("K15").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Confirmed Bookings").Select
    Range("D2").Select
    ActiveSheet.Paste
    Columns("D:D").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
    Columns("D:D").ColumnWidth = 10.86
    Sheets("Quotation System").Select
    Range("K17").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Confirmed Bookings").Select
    Range("E2").Select
    ActiveSheet.Paste
    Range("F2").Select
    Sheets("Quotation System").Select
    Range("K19").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Confirmed Bookings").Select
    ActiveSheet.Paste
    Columns("F:F").ColumnWidth = 8.57
    Sheets("Quotation System").Select
    Range("K21").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Confirmed Bookings").Select
    Range("G2").Select
    ActiveSheet.Paste
    Range("H2").Select
    Sheets("Quotation System").Select
    Range("K23").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Confirmed Bookings").Select
    ActiveSheet.Paste
    Range("I2").Select
    Sheets("Quotation System").Select
    Range("K25").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Confirmed Bookings").Select
    ActiveSheet.Paste
    Sheets("Quotation System").Select
    Range("K7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Confirmed Bookings").Select
    Range("J2").Select
    ActiveSheet.Paste
    Range("K2").Select
    Sheets("Quotation System").Select
    Range("G29").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Confirmed Bookings").Select
    ActiveSheet.Paste
    Columns("K:K").EntireColumn.AutoFit
    Columns("K:K").ColumnWidth = 6
    Columns("K:K").ColumnWidth = 7
    Range("K2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "='Quotation System'!R[27]C[-4]"
    Range("K3").Select
    Columns("J:J").EntireColumn.AutoFit
    Range("I19").Select
    Columns("A:A").ColumnWidth = 8.43
End Sub
Sub Booking_confimred()

End Sub

Many thanks!

非常感谢!

回答by Jook

Because you have a very situation-specific macro recorded, I would like to show you just a basic method to improve this macro and place those copies to a specific position:

因为您录制了一个非常特定于特定情况的宏,所以我只想向您展示一种改进此宏并将这些副本放置到特定位置的基本方法:

You start with this

你从这个开始

Public Sub Macro1()

  Application.CutCopyMode = False

then, this is what macro-recoder uses to copy:

然后,这就是宏记录器用来复制的内容:

Sheets("Quotation System").Select
Range("K9").Select
Selection.Copy

This is what you can use instead:

这是您可以使用的:

Sheets("Quotation System").Range("K9").Copy

This is what macro-recorder uses to paste:

这是宏记录器用来粘贴的内容:

Sheets("Confirmed Bookings").Select
Range("A2").Select
ActiveSheet.Paste

This is what you can use instead:

这是您可以使用的:

Sheets("Confirmed Bookings").Range("A2").Paste

And in order to paste it to a new line, it would be like

为了将其粘贴到新行,就像

   With Sheets("Confirmed Bookings")
     .Cells(.UsedRange.Columns(1).Rows.Count + 1, 1).Paste
   End With

However, this is not the only way to solve this, and sureley not the best one, but it might help you at your level of understanding VBA.

但是,这不是解决此问题的唯一方法,当然也不是最好的方法,但它可能会帮助您了解 VBA。

If you want a neater solution, you might want to search around on SO, as there are many similar questions/problems and good solutions to them here.

如果你想要一个更简洁的解决方案,你可能想在 SO 上搜索,因为这里有许多类似的问题/问题和很好的解决方案。