Workbook import

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dan - 24 Jul 2006 17:19 GMT

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

Reply to this Message

Dave Peterson - 24 Jul 2006 17:32 GMT

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!

> In Workbook 1, I am running a macro that uses this code to select a
> file and copy A:Z on the main worksheet:
[quoted text clipped - 17 lines]
>
> Dan

Signature

Dave Peterson

Reply to this Message

Rate this thread: