Import Data From one workbook to another

Question

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.

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.

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

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

Hey Chris.... The error throws at .Copy PasteStart Before I added the second worksheet, the import worked just fine. — BeanCounterWife

Joshua Fenner Joshua Fenner · Accepted Answer · 2017-09-28T09:59:02

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

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

Import Data From one workbook to another

1 Answers