Copying a worksheet

[Guest]

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.

 

[Robin Hammond]

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

 

[Guest]

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.

 

[Guest]

Your code is not too far off but it does need some tweeking... That having
been said it is potentially going to have a few problems. You are copying a
sheet from the source workbook to paste into the destination workbook. I
would explicitly declare the destination workbook the same as you have
declared the source. That will facilitate the paste (wbkDestination.paste
Before:="Sheet1" for example). Now for the problem. If the same sheet name
already exists in the destination workbook the code will complain. I don't
know if this will be an issue you will have to judge for yourself. You can
create a function to modify the name of the worksheet that you are pasting if
necessary.

The past you are using you are trying to paste a worksheet into a cell. That
won't work. If you were just trying to copy the contents of the source sheet
then you need to use cells.copy -> select a cell A1 in a specific sheet in
the destination workbook and paste.

HTH