VBA to Open Excel File and Paste Sheet 1 Data into “Main” Sheet in Current Workbook

2
votes

Ok so I have a current workbook (Original Workbook) with one Sheet.

I would like to open an existing workbook (Data Workbook) and copy all of the contents in Sheet 1 of 'Data Workbook', then paste everything into Sheet "Main" of 'Original Workbook'.

At the end of this process I would like to close the 'Data Workbook' So far I have the following code.

however it gives me an error message

"Run-time error'1004': Cannot paste that macro formula onto a worksheet":

Sub ImportData()

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

Set wb1 = ActiveWorkbook
Set PasteStart = [Main!A1]

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

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.UsedRange
        .Copy PasteStart
        Set PasteStart = PasteStart.Offset(.Rows.Count)
    End With
Next Sheet
End If
wb2.Close

End Sub
1
vbaexcel

1 Answers

0
votes

Hello please refer the code below and make changes according to your need. It does what you need.

Option Explicit
Sub import()
Dim filename As String
Dim curfilename As String

curfilename = ThisWorkbook.Name

filename = Application.GetOpenFilename
Application.ScreenUpdating = False

Dim x As Workbook
Set x = Workbooks.Open(filename)

With Sheets("1")
x.Sheets("1").Range("A1:Z10000").Copy   '/Provide the range
End With

Dim y As Workbook
Set y = Workbooks(curfilename)

With Sheets("Main")
y.Sheets("Main").Range("A1").PasteSpecial xlPasteFormats
Application.DisplayAlerts = False
End With

x.Close SaveChanges:=False
Range("A1").Select

End Sub