Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of range....
Hi
I am trying to run the below code to import the contents of a sheet from an external wb to the current workbook but it is coming up with the error 'Subscript out of range' on line Workbooks(CurWkBk).Worksheets("ZPODetails").Range( "a1").Paste Can anyone help me fix this and also give me a reason why this error fires? TIA Sub Import_ZPODetails() Dim FName As String Dim WkBk As Workbook Dim CurWkBk As String CurWkBk = ActiveWorkbook.Name FName = Application.GetOpenFilename() If FName < "False" Then Set WkBk = Workbooks.Open(FName) WkBk.Worksheets(1).Cells.Copy Workbooks(CurWkBk).Worksheets("ZPODetails").Range( "a1").Paste WkBk.Close SaveChanges:=False End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of range....
Seems "ZPODetails" is the problem.
Veryfy the name is correct and that this sheet exists in CurWkBk. Then it should not be only .Paste You need to use PasteSpecial You can replace .Paste with .PasteSpecial xlPasteAll OR WkBk.Worksheets(1).Cells.Copy Denstination:= _ & Workbooks(CurWkBk).Worksheets("ZPODetails").Range( "a1") Sharad "Edgar Thoemmes" wrote in message ... Hi I am trying to run the below code to import the contents of a sheet from an external wb to the current workbook but it is coming up with the error 'Subscript out of range' on line Workbooks(CurWkBk).Worksheets("ZPODetails").Range( "a1").Paste Can anyone help me fix this and also give me a reason why this error fires? TIA Sub Import_ZPODetails() Dim FName As String Dim WkBk As Workbook Dim CurWkBk As String CurWkBk = ActiveWorkbook.Name FName = Application.GetOpenFilename() If FName < "False" Then Set WkBk = Workbooks.Open(FName) WkBk.Worksheets(1).Cells.Copy Workbooks(CurWkBk).Worksheets("ZPODetails").Range( "a1").Paste WkBk.Close SaveChanges:=False End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of range....
Typo alert:
WkBk.Worksheets(1).Cells.Copy Denstination:= _ & Workbooks(CurWkBk).Worksheets("ZPODetails").Range( "a1") WkBk.Worksheets(1).Cells.Copy _ Destination:=Workbooks(CurWkBk).Worksheets("ZPODet ails").Range("a1") Sharad Naik wrote: Seems "ZPODetails" is the problem. Veryfy the name is correct and that this sheet exists in CurWkBk. Then it should not be only .Paste You need to use PasteSpecial You can replace .Paste with .PasteSpecial xlPasteAll OR WkBk.Worksheets(1).Cells.Copy Denstination:= _ & Workbooks(CurWkBk).Worksheets("ZPODetails").Range( "a1") Sharad "Edgar Thoemmes" wrote in message ... Hi I am trying to run the below code to import the contents of a sheet from an external wb to the current workbook but it is coming up with the error 'Subscript out of range' on line Workbooks(CurWkBk).Worksheets("ZPODetails").Range( "a1").Paste Can anyone help me fix this and also give me a reason why this error fires? TIA Sub Import_ZPODetails() Dim FName As String Dim WkBk As Workbook Dim CurWkBk As String CurWkBk = ActiveWorkbook.Name FName = Application.GetOpenFilename() If FName < "False" Then Set WkBk = Workbooks.Open(FName) WkBk.Worksheets(1).Cells.Copy Workbooks(CurWkBk).Worksheets("ZPODetails").Range( "a1").Paste WkBk.Close SaveChanges:=False End If End Sub -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of range....
Thanks Dave,
I usually only break the message boxes, and let the othercode remain in one line. So kinda got a habit of putting an "&" after _ Thanks. Sharad "Dave Peterson" wrote in message ... Typo alert: WkBk.Worksheets(1).Cells.Copy Denstination:= _ & Workbooks(CurWkBk).Worksheets("ZPODetails").Range( "a1") WkBk.Worksheets(1).Cells.Copy _ Destination:=Workbooks(CurWkBk).Worksheets("ZPODet ails").Range("a1") Sharad Naik wrote: Seems "ZPODetails" is the problem. Veryfy the name is correct and that this sheet exists in CurWkBk. Then it should not be only .Paste You need to use PasteSpecial You can replace .Paste with .PasteSpecial xlPasteAll OR WkBk.Worksheets(1).Cells.Copy Denstination:= _ & Workbooks(CurWkBk).Worksheets("ZPODetails").Range( "a1") Sharad "Edgar Thoemmes" wrote in message ... Hi I am trying to run the below code to import the contents of a sheet from an external wb to the current workbook but it is coming up with the error 'Subscript out of range' on line Workbooks(CurWkBk).Worksheets("ZPODetails").Range( "a1").Paste Can anyone help me fix this and also give me a reason why this error fires? TIA Sub Import_ZPODetails() Dim FName As String Dim WkBk As Workbook Dim CurWkBk As String CurWkBk = ActiveWorkbook.Name FName = Application.GetOpenFilename() If FName < "False" Then Set WkBk = Workbooks.Open(FName) WkBk.Worksheets(1).Cells.Copy Workbooks(CurWkBk).Worksheets("ZPODetails").Range( "a1").Paste WkBk.Close SaveChanges:=False End If End Sub -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of range....
I feel your pain <bg.
But I figured I might be able to save a followup post (and a little time for the OP). Sharad Naik wrote: Thanks Dave, I usually only break the message boxes, and let the othercode remain in one line. So kinda got a habit of putting an "&" after _ Thanks. Sharad "Dave Peterson" wrote in message ... Typo alert: WkBk.Worksheets(1).Cells.Copy Denstination:= _ & Workbooks(CurWkBk).Worksheets("ZPODetails").Range( "a1") WkBk.Worksheets(1).Cells.Copy _ Destination:=Workbooks(CurWkBk).Worksheets("ZPODet ails").Range("a1") Sharad Naik wrote: Seems "ZPODetails" is the problem. Veryfy the name is correct and that this sheet exists in CurWkBk. Then it should not be only .Paste You need to use PasteSpecial You can replace .Paste with .PasteSpecial xlPasteAll OR WkBk.Worksheets(1).Cells.Copy Denstination:= _ & Workbooks(CurWkBk).Worksheets("ZPODetails").Range( "a1") Sharad "Edgar Thoemmes" wrote in message ... Hi I am trying to run the below code to import the contents of a sheet from an external wb to the current workbook but it is coming up with the error 'Subscript out of range' on line Workbooks(CurWkBk).Worksheets("ZPODetails").Range( "a1").Paste Can anyone help me fix this and also give me a reason why this error fires? TIA Sub Import_ZPODetails() Dim FName As String Dim WkBk As Workbook Dim CurWkBk As String CurWkBk = ActiveWorkbook.Name FName = Application.GetOpenFilename() If FName < "False" Then Set WkBk = Workbooks.Open(FName) WkBk.Worksheets(1).Cells.Copy Workbooks(CurWkBk).Worksheets("ZPODetails").Range( "a1").Paste WkBk.Close SaveChanges:=False End If End Sub -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
9: Subscript out of range | Excel Discussion (Misc queries) | |||
Subscript out of Range | Excel Programming | |||
Subscript is out of Range | Excel Programming | |||
Subscript out of Range Q | Excel Programming | |||
Subscript Out of Range | Excel Programming |