将工作表内容复制到另一个工作表 [英] copy sheet content to another sheet

问题描述

我想在另一张纸的末尾复制一张纸的内容，我已经尝试过此vba代码并且它可以正常工作，

I want to copy the content of one sheet at the end of the other, i have tried this vba code and it works,

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.

推荐答案

不需要使用太多的 Select ，这会减慢代码的速度，您可以使用下面的1行将整个 Sheet("B")的内容到 Sheet("A")中列"A"的第一个空行.

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").

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

这篇关于将工作表内容复制到另一个工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！