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

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" <EdgarThoemmes@discussions.microsoft.com> wrote in message
news:F1FBE9D3-A9D9-4E79-8B55-6142D8E85140@microsoft.com...
> 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
>

Typo alert:

WkBk.Worksheets(1).Cells.Copy Denstination:= _
& Workbooks(CurWkBk).Worksheets("ZPODetails").Range("a1")

WkBk.Worksheets(1).Cells.Copy _
Destination:=Workbooks(CurWkBk).Worksheets("ZPODetails").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" <EdgarThoemmes@discussions.microsoft.com> wrote in message
> news:F1FBE9D3-A9D9-4E79-8B55-6142D8E85140@microsoft.com...
> > 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

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" <ec35720@netscapeXSPAM.com> wrote in message
news:41E4860E.B20F09CD@netscapeXSPAM.com...
> Typo alert:
>
> WkBk.Worksheets(1).Cells.Copy Denstination:= _
> & Workbooks(CurWkBk).Worksheets("ZPODetails").Range("a1")
>
> WkBk.Worksheets(1).Cells.Copy _
> Destination:=Workbooks(CurWkBk).Worksheets("ZPODetails").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" <EdgarThoemmes@discussions.microsoft.com> wrote in
>> message
>> news:F1FBE9D3-A9D9-4E79-8B55-6142D8E85140@microsoft.com...
>> > 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

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" <ec35720@netscapeXSPAM.com> wrote in message
> news:41E4860E.B20F09CD@netscapeXSPAM.com...
> > Typo alert:
> >
> > WkBk.Worksheets(1).Cells.Copy Denstination:= _
> > & Workbooks(CurWkBk).Worksheets("ZPODetails").Range("a1")
> >
> > WkBk.Worksheets(1).Cells.Copy _
> > Destination:=Workbooks(CurWkBk).Worksheets("ZPODetails").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" <EdgarThoemmes@discussions.microsoft.com> wrote in
> >> message
> >> news:F1FBE9D3-A9D9-4E79-8B55-6142D8E85140@microsoft.com...
> >> > 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