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

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" wrote in
message ...

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

...........


--
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" wrote in
message ...

.........


--
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 othe
spreadsheets and feed the data back into "Report Template".

Any ideas on how to do this?

TIA,

Samuel

--
Samuel
-----------------------------------------------------------------------
SamuelT's Profile: http://www.excelforum.com/member.php...fo&userid=2750
View this thread: http://www.excelforum.com/showthread.php?threadid=48083

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

--
Samuel
-----------------------------------------------------------------------
SamuelT's Profile: http://www.excelforum.com/member.php...fo&userid=2750
View this thread: http://www.excelforum.com/showthread.php?threadid=48083