Workbooks when to use
 

Hi all,

I am bit confused.

I have a workbook with a VBA program called prog.xls that I try to do the
following with

open a file file.xls using workbooks.open filename:=file.xls

and then open file1.xls copy a chunk of data
activate file.xls using workbooks(file.xls).activate
create a new sheet with name file1 and then paste the chunk of data
there using range("A1").select and activesheet.paste.

But it doesn't do it for me, instead it creates the new sheet file1 in
prog.xls and puts the chunk of data there.

How do I correctly refer to the workbook file.xls and put the data in that
book instead of the prog.xls book?

Thanks for any help.

Regards,
Milos.

Workbooks when to use
 

This will do what you want when opening prog.xls. Assumes
all files are in the current directory. You will be
promted to save 'file.xls' before it closes:-

Private Sub Workbook_Open()

Dim wkb1 As Workbook, wkb2 As Workbook

With Workbooks
Set wkb1 = .Open("file.xls")
Set wkb2 = .Open("file1.xls")
End With

With Application
.ScreenUpdating = False
.Goto Range("Data")
Selection.Copy
End With

With wkb1
.Sheets.Add
.Sheets(Sheets.Count).Activate
Range("A1").Select
Selection.PasteSpecial
Application.CutCopyMode = False
End With

wkb1.Close
Set wkb1 = Nothing

wkb2.Close
Set wkb2 = Nothing

End Sub

hth,

Tony

-----Original Message-----
Hi all,

I am bit confused.

I have a workbook with a VBA program called prog.xls that
I try to do the
following with

open a file file.xls using workbooks.open
filename:=file.xls

and then open file1.xls copy a chunk of data
activate file.xls using workbooks(file.xls).activate
create a new sheet with name file1 and then paste the
chunk of data
there using range("A1").select and activesheet.paste.

But it doesn't do it for me, instead it creates the new
sheet file1 in
prog.xls and puts the chunk of data there.

How do I correctly refer to the workbook file.xls and put
the data in that
book instead of the prog.xls book?

Thanks for any help.

Regards,
Milos.




.

Workbooks when to use
 

Hi Milos,

Maybe when you create the new sheet, the activeworkbook is
prog.xls or you use thisworkbook function. Could you post
the code so that we can analysis?

Best Regards

Bill


-----Original Message-----
Hi all,

I am bit confused.

I have a workbook with a VBA program called prog.xls that
I try to do the
following with

open a file file.xls using workbooks.open
filename:=file.xls

and then open file1.xls copy a chunk of data
activate file.xls using workbooks(file.xls).activate
create a new sheet with name file1 and then paste the
chunk of data
there using range("A1").select and activesheet.paste.

But it doesn't do it for me, instead it creates the new
sheet file1 in
prog.xls and puts the chunk of data there.

How do I correctly refer to the workbook file.xls and put
the data in that
book instead of the prog.xls book?

Thanks for any help.

Regards,
Milos.




.