Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying a worksheet
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying a worksheet
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying a worksheet
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying a worksheet
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 Befo="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 "Rain" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet formula incorrect after copying to another worksheet | Excel Worksheet Functions | |||
Copying worksheet cells into another worksheet using autofill | Excel Discussion (Misc queries) | |||
Looking up a variable in one worksheet and copying information from another column to another worksheet?? | Excel Discussion (Misc queries) | |||
Copying a worksheet witrh protected cells to a new worksheet | Excel Worksheet Functions | |||
Copying Worksheet triggers Click event of combobox on another worksheet | Excel Programming |