Hi Robin,

Thanks a lot for the reply.
I just tried this, and it worked for me.

Sub CopyFromSoure()

Workbooks.Open Filename:=ActiveWorkbook.Path & "\Test2.xls"
Workbooks("Test2.xls").Worksheets("Sheet1").Range( "A:IV").Copy
ActiveSheet.Paste Destination:=ThisWorkbook.Worksheets(2).Range("A1" )
Application.CutCopyMode = False
Workbooks("Test2.xls").Close savechanges:=False

End Sub

Thanks.
"Robin Hammond" wrote:

Rain,

Try something like this. I haven't tried to perfect it but it seems to work.

Sub CopyBook()
Dim SourceWkbk As Workbook
Dim wbTgt As Workbook
Dim shTgt As Worksheet
Set wbTgt = ActiveWorkbook
Set SourceWkbk = Workbooks.Open(Filename:="C:\book1.xls")
Sheets("Sheet3").Copy After:=wbTgt.Sheets(wbTgt.Sheets.Count)
wbTgt.Sheets(wbTgt.Sheets.Count).Visible = xlSheetHidden
SourceWkbk.Close savechanges:=False
End Sub

Robin Hammond
www.enhanceddatasystems.com

"Rain" wrote in message
...
I am trying to do the following using VBA:
1. Open a workbook
2. Copy a sheet
3. Paste it on a destination WorkBook.Sheet where this sheet it hidden
4. Close the source workbook

I tried the following:
Sub Copyfromworkbook()

Dim SourceWkbk As Workbook
Set SourceWkbk = Workbooks.Open(Filename:="Book2.xls")
SourceWkbk.Sheets("Sheet3").Copy

' ActiveSheet.Paste Destination:=Sheets("Sheet4").Range("A1")
Sheets("Sheet4").Range("A1").Select
ActiveSheet.Paste

SourceWkbk.Close savechanges:=False

End Sub

I get a subscript out of range error.
Can you please tell me where I am going wrong.