Workbook import

[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

 

[Dave Peterson]

I'd use variables to represent each workbook and qualify all the other objects.

Dim WB1 as workbook
dim WB2 as workbook
dim WB2Name as Variant
dim wksNew as worksheet

set wb1 = thisworkbook 'activeworkbook or workbooks("book1.xls")

wb2name = application.getopenfilename("Excel files, *.xls")

if wb2name = false then
'user hit cancel
exit sub
end if

set wb2 = workbooks.open(filename:=wb2name)

set wksnew = wb1.worksheets.add
on error resume next
application.displayalerts = false
wb1.worksheets("rawdata").delete
application.displayalerts = true
on error goto 0

wksnew.name = "RawData"

wb2.worksheets("Main").range("a:z").copy _
destination:=wksnew.range("a1")

'close wb2 without saving????
wb2.close savechanges:=false

========
Untested, uncompiled. Watch for typos!