vba 从excel工作表中获取数据并将其复制到活动工作簿中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19794223/
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
Get data from excel worksheet and copy it into the active workbook
提问by user2957184
I'm new to using VBA and have found the code below. It works fine but I need all the rows in the source file. How can I change the code so I'm not limited to using the row numbers as the will be differten every time.
我是使用 VBA 的新手，并找到了下面的代码。它工作正常，但我需要源文件中的所有行。如何更改代码，以便我不限于使用行号，因为每次都会有所不同。
Private Sub Import1_Click()
' Get customer workbook...
Dim customerBook As Workbook
Dim filter As String
Dim caption As String
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook
' make weak assumption that active workbook is the target
Set targetWorkbook = Application.ActiveWorkbook
' get the customer workbook
filter = "Text files (*.xlsx),*.xlsx"
caption = "Please Select an input file "
customerFilename = Application.GetOpenFilename(filter, , caption)
Set customerWorkbook = Application.Workbooks.Open(customerFilename)
' assume range is A1 - G10 in sheet1
' copy data from customer to target workbook
Dim targetSheet As Worksheet
Set targetSheet = targetWorkbook.Worksheets(1)
Dim sourceSheet As Worksheet
Set sourceSheet = customerWorkbook.Worksheets(1)
targetSheet.Range("A6", "G10").Value = sourceSheet.Range("A2", "G6").Value
' Close customer workbook
customerWorkbook.Close
End Sub
采纳答案by John Bustos
Assuming you want to copy the entire sheet onto a blank sheet in your current workbook and only keep values (your question doesn't specify that too well), you could replace this line:
假设您想将整个工作表复制到当前工作簿中的一张空白工作表上并且只保留值（您的问题没有很好地说明这一点），您可以替换这一行：
targetSheet.Range("A6", "G10").Value = sourceSheet.Range("A2", "G6").Value
With this:
有了这个：
sourceSheet.UsedRange.Copy targetSheet.Range("A1")
sourceSheet.UsedRange.Value = sourceSheet.UsedRange.Value
Hope that proves to do the trick!
希望证明可以解决问题！
UPDATE
更新
Baed upon your last comment, try replacing this line:
根据您的最后一条评论，尝试替换此行：
sourceSheet.UsedRange.Copy targetSheet.Range("A1")
With this line:
有了这条线：
Intersect(sourceSheet.UsedRange, sourceSheet.UsedRange.Offset(1,0)).Copy targetSheet.Range("A1")
The final part Range("A1")can be updated to paste the results wherever you want them.
Range("A1")可以更新最后一部分以将结果粘贴到您想要的任何位置。
Hope this does the trick!!
希望这能解决问题！！
回答by Menezes Sousa
Just the following line has to be changed like I've given below:
只需像我在下面给出的那样更改以下行：
targetSheet.Range("A6", "G10").Value = sourceSheet.Range("A2", "G6").Value
First, using name ranges (top-left of excel where the name of the cell appears), name the start and end cell of the source sheet. Say, you call them "start" and "end".
Now, you can also name your start cell in targetsheet. End cell is not required because it will paste everything anyways. This is optional and you can stay without naming it like below.
The code will then look like...
targetSheet.Range("A6").Value = sourceSheet.Range("start", "end").ValueIf you name the targetSheet start cell ("start_target" for example) as well then it will be like this :
targetSheet.Range("start_target").Value = sourceSheet.Range("start", "end").Value
首先，使用名称范围（显示单元格名称的 Excel 左上角），命名源工作表的开始和结束单元格。比如说，你称它们为“开始”和“结束”。
现在，您还可以在目标表中命名您的起始单元格。不需要结束单元格，因为它无论如何都会粘贴所有内容。这是可选的，您可以不用命名，如下所示。
然后代码看起来像......
targetSheet.Range("A6").Value = sourceSheet.Range("start", "end").Value如果您也命名 targetSheet 起始单元格（例如“start_target”），则它将如下所示：
targetSheet.Range("start_target").Value = sourceSheet.Range("start", "end").Value
