将数据从一个工作簿导入另一个工作簿

I currently have a master workbook that I use to create external pdf forms. My current code lets me browse to a folder and select another workbook to pull specific data from and paste it into the Master.

我目前有一个用于创建外部pdf表单的主工作簿。我当前的代码让我浏览到一个文件夹并选择另一个工作簿来从中提取特定数据并将其粘贴到主文件中。

The data is copied from external workbook, sheet1, and pasted to active workbook, sheet1. The code I am working with is "For Each Sheet." Works like a charm.

数据从外部工作簿sheet1复制并粘贴到活动工作簿sheet1。我正在使用的代码是“For Each Sheet”。奇迹般有效。

The problem is that I have added another sheet to the workbooks and it's throwing a Run-time error 1004. I need to recode it so it copies from only sheet1 in the external workbook and pastes only to sheet1 of the Master (active).

问题是我已经在工作簿中添加了另一个工作表，并且它抛出了运行时错误1004.我需要对其进行重新编码，使其仅从外部工作簿中的sheet1复制并仅粘贴到Master（活动）的sheet1。

Any ideas? Thanks for any help you can pass along.

有任何想法吗？感谢您提供的任何帮助。

Sub ImportData()

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim Sheet As Worksheet
Dim PasteStart As Range

Dim sh As Worksheet
Dim strXLSFile As String, strPDFFile As String, strFolder As String

strFolder = "H:\Company Data\Firm Files\Client Data Workbooks\Excel Data-Client Info"

Set wb1 = ActiveWorkbook
Set PasteStart = [Client_Data]

FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a Report to Parse", _
FileFilter:="Report Files *.xlsm (*.xlsm),")

If FileToOpen = False Then
    MsgBox "No File Specified.", vbExclamation, "ERROR"
    Exit Sub
Else
    Set wb2 = Workbooks.Open(Filename:=FileToOpen)
For Each Sheet In wb2.Sheets
    With Sheet.Cells.Range("B9:B27")
        .Copy PasteStart
        Set PasteStart = PasteStart.Offset(.Rows.Count)
    End With
    Next Sheet

End If

wb2.Close False

End Sub

1 个解决方案

#1

if you only want Sheet1 then get rid of the loop.

如果你只想要Sheet1然后摆脱循环。

Instead of:

代替：

For Each Sheet In wb2.Sheets
With Sheet.Cells.Range("B9:B27")
    .Copy PasteStart
    Set PasteStart = PasteStart.Offset(.Rows.Count)
End With
Next Sheet

Try:

尝试：

wb2.sheets(1).range("B9:B27").copy PasteStart

it seems like this is a one time use so no need to Set PasteStart = again. Hope this helps

看起来这是一次性使用所以不需要再次设置PasteStart =。希望这可以帮助

#1