asked on

Import Data from one workbook to another using VBA

Hello Experts...

I have a workbook open which has a tab called "Previous". I want to open another workbook using VBA, copy the data from Sheet1 in that workbook, and paste it into the "Previous" tab in the current workbook.

I can get the macro to:
1.) Prompt for the workbook to open
2.) Open the Workbook

Using this code (Found by Googling)

Sub Get_Data()

Dim wbk As Workbook
Dim destWbk As Workbook

Set destWbk = ActiveWorkbook

FileToOpen = Application.GetOpenFilename _
(Title:="Please choose the previous file to import", _
FileFilter:="Excel Files *.xls (*.xls),")
''
    If FileToOpen = False Then
        MsgBox "No file specified.", vbExclamation, "Duh!!!"
        Exit Sub
    Else
        Set wbk = Workbooks.Open(FileToOpen)
    End If

' Need to copy the source and paste it

End Sub

Open in new window

What I can't figure out is how to copy the data from the workbook I opened into my current workbook tab named "Previous". I've tried many examples, but I just keep getting Paste errors (like error 1004).

I need some help to:
1.) Prompt the user for a workbook to open
2.) Select and copy the contents of the first sheet of that workbook
3.) Paste the copied contents into the "Previous" tab of my current workbook.

Thanks!

SANTABABY

Try the following code(ATTACHED)


Sub Get_Data()
Dim wbk As Workbook
Dim destWbk As Workbook
Dim xlSheet As Excel.Worksheet
Dim fn As String
        
    Set destWbk = ActiveWorkbook
    FileToOpen = Application.GetOpenFilename _
                (Title:="Please choose the previous file to import", _
                FileFilter:="Excel Files *.xls (*.xls),")

    If FileToOpen = False Then
        MsgBox "No file specified.", vbExclamation, "Duh!!!"
        Exit Sub
    Else
        Set wbk = Workbooks.Open(FileToOpen)
    End If

    Application.CutCopyMode = False
    wbk.Sheets(1).Cells.Select
    Selection.Copy
    destWbk.Activate
    Sheets("Previous").Cells(1, 1).Select
    ActiveSheet.Paste
    
    wbk.Close



End Sub

Open in new window

Gridcaster

ASKER

When I try the code, I get the following error message:

Run-time error '1004': Select method of Range class failed

It is getting stuck on:

[code]
Sheets("Previous").Cells(1, 1).Select
[/code]

Gridcaster

ASKER

Grr... stupid code tags didn't work...

It is getting stuck on:

Sheets("Previous").Cells(1, 1).Select

Gridcaster

ASKER

I am using Excel 2003 if that makes a difference...

Gridcaster

ASKER

Oh... and I can click in the "Previous" tab and go to Edit -> Paste (or hit Ctrl-V) and the data is pasted into the workbook. So the select and copy part is working... just the paste part is not.

Gridcaster

ASKER

Ok... so I think I just solved my own problem... The code below seems to do the copy. I don't know if there is an easier way (or more efficient way) to do this without having to copy everything from the opened workbook (including the bank cells)... So I'm going to leave this question open a bit longer to see if anyone has any suggestions.

Sub Get_Data ()
Dim wbk As Workbook
Dim destWbk As Workbook
        
    Set destWbk = ActiveWorkbook
    FileToOpen = Application.GetOpenFilename _
                (Title:="Please choose the previous file to import", _
                FileFilter:="Excel Files *.xls (*.xls),")

    If FileToOpen = False Then
        MsgBox "No file specified.", vbExclamation, "Duh!!!"
        Exit Sub
    Else
        Set wbk = Workbooks.Open(FileToOpen)
    End If
    
wbk.Sheets(1).Range("A1:IV65536").Copy Destination:=destWbk.Sheets("Previous").Range("A1")
wbk.Close

End Sub

Open in new window

SANTABABY

Glad to hear that you made some progress. Please elaborate what your are trying to copy and what you prefer not to be copied.
Your original posting did not have much detail.
Thanks.

ASKER CERTIFIED SOLUTION

MWGainesJR

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gridcaster

ASKER

Works perfectly! Thanks for your help!