Run-time error 9 Problem

[SamuelT]

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

 

[Tom Ogilvy]

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"

 

[SamuelT]

...........

 

[Tom Ogilvy]

I know. Your welcome.

 

[SamuelT]

Thanks Tom.

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

Any ideas on how to do this?

TIA,

Samuel

 

[SamuelT]

Thanks Tom.

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

Any ideas on how to do this?

TIA,

Samuel