Run-time error 9 Problem

Hi all,

I've found some VBA code that appears perfect for what I want to do - that is select a range of cells in one workbook, then copy it into another (Report Template). This needs to be done over a large number of workbooks, all going into 'Report Template', hence the need for the macro.

I'm currently using the following code:

Sub CopySelection()
Dim destrange As Range
If Selection.Areas.Count > 1 Then Exit Sub
Set destrange = Sheets("Report Template").Range("A" & _
LastRow(Sheets("Report Template")) + 1)
Selection.Copy destrange
End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Function Lastcol(sh As Worksheet)
On Error Resume Next
Lastcol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function

...but keeping getting a 'Run-time error code '9': Subscript out of range'.

Can anyone suggest what I'm doing wrong?

TIA,

SamuelT

..........

I know. Your welcome.

--
Regards,
Tom Ogilvy

"SamuelT" <SamuelT.1xu3qr_1130875826.101@excelforum-nospam.com> wrote in
message news:SamuelT.1xu3qr_1130875826.101@excelforum-nospam.com...
>
> .........
>
>
> --
> SamuelT
> ------------------------------------------------------------------------
> SamuelT's Profile:
http://www.excelforum.com/member.php...o&userid=27501
> View this thread: http://www.excelforum.com/showthread...hreadid=480836
>

I know. Your welcome.

--
Regards,
Tom Ogilvy

"SamuelT" <SamuelT.1xu3qr_1130875826.101@excelforum-nospam.com> wrote in
message news:SamuelT.1xu3qr_1130875826.101@excelforum-nospam.com...
>
> .........
>
>
> --
> SamuelT
> ------------------------------------------------------------------------
> SamuelT's Profile:
http://www.excelforum.com/member.php...o&userid=27501
> View this thread: http://www.excelforum.com/showthread...hreadid=480836
>

You don't have a sheet named "Report Template" in the activeworkbook when
you run the code. You may have one like

("Report Template ")
or
(" Report Template")

But excel can't find one named "Report Template"

--
Regards,
Tom Ogilvy


"SamuelT" <SamuelT.1xtpmf_1130857519.8305@excelforum-nospam.com> wrote in
message news:SamuelT.1xtpmf_1130857519.8305@excelforum-nospam.com...
>
> Hi all,
>
> I've found some VBA code that appears perfect for what I want to do -
> that is select a range of cells in one workbook, then copy it into
> another (Report Template). This needs to be done over a large number of
> workbooks, all going into 'Report Template', hence the need for the
> macro.
>
> I'm currently using the following code:
>
> Sub CopySelection()
> Dim destrange As Range
> If Selection.Areas.Count > 1 Then Exit Sub
> Set destrange = Sheets("Report Template").Range("A" & _
> LastRow(Sheets("Report Template")) + 1)
> Selection.Copy destrange
> End Sub
>
> Function LastRow(sh As Worksheet)
> On Error Resume Next
> LastRow = sh.Cells.Find(What:="*", _
> After:=sh.Range("A1"), _
> Lookat:=xlPart, _
> LookIn:=xlFormulas, _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlPrevious, _
> MatchCase:=False).Row
> On Error GoTo 0
> End Function
>
> Function Lastcol(sh As Worksheet)
> On Error Resume Next
> Lastcol = sh.Cells.Find(What:="*", _
> After:=sh.Range("A1"), _
> Lookat:=xlPart, _
> LookIn:=xlFormulas, _
> SearchOrder:=xlByColumns, _
> SearchDirection:=xlPrevious, _
> MatchCase:=False).Column
> On Error GoTo 0
> End Function
>
> ..but keeping getting a 'Run-time error code '9': Subscript out of
> range'.
>
> Can anyone suggest what I'm doing wrong?
>
> TIA,
>
> SamuelT
>
>
> --
> SamuelT
> ------------------------------------------------------------------------
> SamuelT's Profile:
http://www.excelforum.com/member.php...o&userid=27501
> View this thread: http://www.excelforum.com/showthread...hreadid=480836
>

Thanks Tom.

You're right - the code is supposed to run in a range of other spreadsheets and feed the data back into "Report Template".

Any ideas on how to do this?

TIA,

SamuelT