vba 将工作表内容复制到另一个工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43468467/
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
copy sheet content to another sheet
提问by zenami
I want to copy the content of one sheet at the end of the other, i have tried this vba code and it works,
我想在另一张纸的末尾复制一张纸的内容，我试过这个 vba 代码并且它有效，
Private Sub CommandButton1_Click()
Sheets("B").Select
Range("A1:H14").Select
Range("A1:H14").Copy
Sheets("A").Select
' Find the last row of data
Range("B48:I48").Select
ActiveSheet.Paste
Sheets("A").Select
End Sub
but what i want is to copy without having to specify the range of the data, because i have many files and many data and it's gonna be hard to do all of that manually and change the range a each time.
但我想要的是复制而不必指定数据的范围，因为我有很多文件和很多数据，并且很难手动完成所有这些操作并每次更改范围 a。
采纳答案by Shai Rado
There's no need to use so many Select, which slows down the code, you can use the 1 line below will copy the entire contents of Sheet("B")to the first empty row at Column "A" in Sheet("A").
没有必要使用这么多Select，这会减慢代码的速度，您可以使用下面的 1 行将 的全部内容复制Sheet("B")到Sheet("A").
Dim Rng As Range
Dim lRow As Long
Dim lCol As Long
Dim lPasteRow As Long
With Sheets("B")
lRow = .Cells.Find(What:="*", _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
lCol = .Cells.Find(What:="*", _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
lPasteRow = Sheets("A").Cells.Find(What:="*", _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
.Range(.Cells(1, 1), .Cells(lRow, lCol)).Copy Destination:=Sheets("A").Range("A" & lPasteRow + 1)
End With
回答by gr8tech
I usually do something like this, assuming sheet1 is the sheet to be updated by data from sheet2;
我通常会这样做，假设 sheet1 是要由 sheet2 中的数据更新的工作表；
dim destLen as Long 'rows used in sheet 1
dim sourceLen as Long 'rows used in sheet 2
Open directory with source files and loop through each file and do the following
打开包含源文件的目录并遍历每个文件并执行以下操作
destLen = Sheet1.Range("A"&Rows.Count).End(xlUp).Row
sourceLen = Sheet2.Range("A"&Rows.Count).End(xlUp).Row
Sheet2.Range("B1" & ":I" & sourceLen).copy
Sheet1.Range("A" & destLen + 1).pasteSpecial xlValues
Application.CutCopyMode = False
回答by Erdem Akkas
Below will copy entire content in Sheet Bto Sheet A
下面将把整个内容复制Sheet B到Sheet A
Sheets("B").Cells.Copy Destination:=Sheets("A").Range("A1")
You do not need to select cells while copying.
复制时不需要选择单元格。
