VBA Excel 脚本从一个工作表复制包含数据的行并粘贴到另一个而不覆盖
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5992721/
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
VBA Excel Script to copy rows with data from one worksheet & paste into another with out overwritting
提问by StillWritingAccounts
Excel 2007
Excel 2007
I'm trying to run a VBA script that will copy a list of sales Ledgers from one worksheet titled MSL with the columns DATE, INVOICE NO, COMPANY NAME, TOTAL, SUB-TOTAL, NETT, VAT.
我正在尝试运行一个 VBA 脚本，该脚本将从名为 MSL 的工作表中复制销售分类帐列表，其中包含日期、发票编号、公司名称、总计、小计、净额、增值税。
1st problem i'm having is i only want to copy rows 2 and onward which contain a record and this number will change each month depending on sales.
我遇到的第一个问题是我只想复制包含记录的第 2 行及以后的行，并且这个数字会根据销售情况每月更改。
e.g. Jan has 30 rows Feb has 24 rows Mar has 40 rows
例如 Jan 有 30 行 Feb 有 24 行 Mar 有 40 行
Next i need to paste the data in to a new worksheet titled "SalesDB" with the same columns DATE, INVOICE NO, COMPANY NAME, TOTAL, SUB-TOTAL, NETT, VAT.
接下来，我需要将数据粘贴到名为“SalesDB”的新工作表中，该工作表具有相同的列日期、发票编号、公司名称、总计、小计、净额、增值税。
Next problem im having is the data is overwriting.
我遇到的下一个问题是数据被覆盖。
Thanks for any help will be out for the next hour collecting kids from school
感谢您提供任何帮助，我们 将在接下来的一个小时内从学校接孩子
回答by Alex P
Assuming Worksheet MLSis like the following and Worksheet SalesDBis same format:
假设Worksheet MLS如下所示并且Worksheet SalesDB格式相同：
A B C D E F G
1 Date Invoice No Company Name Total Sub-Total Nett VAT
2
This code will grab data from MLStaking account of number of entries and add to SalesDBand avoid overwriting.
此代码将从MLS考虑条目数中获取数据并添加到SalesDB并避免覆盖。
Sub CopyPasteSales
Dim salesData as range, targetRng as range
Set salesData = Worksheets("MLS").Range("A2:G" & Range("A1").end(xlDown).Row)
If Worksheets("SalesDB").Range("A2") = vbNullString Then
Set targetRng = Worksheets("SalesDB").Range("A2") //If no data in SalesDB start in row 2
Else
Set targetRng = Worksheets("SalesDB").Range("A1").end(xlDown).Offset(1,0) //If data already in SalesDB, find next free row
End if
salesData.Copy Destination:= targetRng
End Sub
回答by Patrick Honorez
Short and dynamic: (not tested, may contain typos)
简短而动态：（未经测试，可能包含错别字）
Sub CopyData()
Dim src As Range, dest As Range
'set source, exclude first row
Set src = Worksheets("MLS").Range("A2").CurrentRegion.Offset(1, 0)
'destination is one row below last row
Set dest = Worksheets("SalesDB").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
src.Copy Destination:=dest
End Sub
