Import from another Workbook

[dan]

In Workbook 1, I am running a macro that uses this code to select a
file and copy A:Z on the main worksheet:

Dim myBk As Workbook
Set myBk = Workbooks.Open(Application.GetOpenFilename _
(, , "Select the file"))
Sheets("Main").Select
Range("A:Z").Copy

Next, I want it to switch back to Workbook 1, insert a new worksheet,
paste the data, and name the worksheet "rawdata".

Any suggestions for me? I basically have a workbook I'm starting from,
I want to open a dialog box to allow the user to select another excel
file (filenames can be different so I want user to select), and then
import a specific worksheet from that workbook into my starting
workbook.

Any help would be greatly appreciated, Thanks!

Dan

 

[Ron de Bruin]

Try this

Sub test()
Dim myBk As Workbook
Dim Basesheet As Worksheet
Set Basesheet = Sheets.Add
Basesheet.Name = "rawdata"
Set myBk = Workbooks.Open(Application.GetOpenFilename _
(, , "Select the file"))

myBk.Sheets("Main").Range("A:Z").Copy Basesheet.Range("A1")
myBk.Close False
End Sub

Are you sure that the workbook you select have a sheet named Main

 

[Dave Peterson]

You have another response in .excel

In Workbook 1, I am running a macro that uses this code to select a
file and copy A:Z on the main worksheet:

Dim myBk As Workbook
Set myBk = Workbooks.Open(Application.GetOpenFilename _
(, , "Select the file"))
Sheets("Main").Select
Range("A:Z").Copy

Next, I want it to switch back to Workbook 1, insert a new worksheet,
paste the data, and name the worksheet "rawdata".

Any suggestions for me? I basically have a workbook I'm starting from,
I want to open a dialog box to allow the user to select another excel
file (filenames can be different so I want user to select), and then
import a specific worksheet from that workbook into my starting
workbook.

Any help would be greatly appreciated, Thanks!

Dan