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