Function
 

The below code selects a range of cells. Using the below
method, how do I make this code into a function?


Dim RowValue
Dim ColumnValue
Dim ColLetter

RowValue = Worksheets("Agents").Range("A65536").End
(xlUp).Row
ColumnValue = Worksheets("Agents").Range("B:B").End
(xlToRight).Column
Worksheets("Agents").Range("A1").Offset(0, ColumnValue -
1).Select
'Converts column number to column letter
ColLetter = Left(ActiveCell.Address(, 0), InStr
(ActiveCell.Address(, 0), "$") - 1)
Worksheets("Agents").Range("A2:" & ColLetter &
RowValue).Select
Selection.Copy



Thanks
Todd Huttenstine

Function
 

go to tools-macro-visual basic editor


type in the editor


sub test()
Dim RowValue
Dim ColumnValue
Dim ColLetter

RowValue = Worksheets("Agents").Range("A65536").End
(xlUp).Row
ColumnValue = Worksheets("Agents").Range("B:B").End
(xlToRight).Column
Worksheets("Agents").Range("A1").Offset(0, ColumnValue -
1).Select
'Converts column number to column letter
ColLetter = Left(ActiveCell.Address(, 0), InStr
(ActiveCell.Address(, 0), "$") - 1)
Worksheets("Agents").Range("A2:" & ColLetter &
RowValue).Select
Selection.Copy
end sub


and to execute it go to tools-macro-macros, you will see tes
procedure listed there to run

--
anilsolipura
-----------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...fo&userid=1627
View this thread: http://www.excelforum.com/showthread.php?threadid=27831

Function
 

Functions ( I assume you mean a UDF) return value as a regular function.
However, you code could be simplified and withOUT selections

with worksheets("Agents")
lr=.cells(rows.count,"a").end(xlup).row
'======
lc=.cells(columns.count,"b").end(xltoleft).column
'or
'lc=activecell.column
'========
..range(cells(2,1),cells(lr,lc).copy _
sheets("destinationsheet").range("a1")
end with
===========

--
Don Guillett
SalesAid Software

"Todd Huttenstine" wrote in message
...
The below code selects a range of cells. Using the below
method, how do I make this code into a function?


Dim RowValue
Dim ColumnValue
Dim ColLetter

RowValue = Worksheets("Agents").Range("A65536").End
(xlUp).Row
ColumnValue = Worksheets("Agents").Range("B:B").End
(xlToRight).Column
Worksheets("Agents").Range("A1").Offset(0, ColumnValue -
1).Select
'Converts column number to column letter
ColLetter = Left(ActiveCell.Address(, 0), InStr
(ActiveCell.Address(, 0), "$") - 1)
Worksheets("Agents").Range("A2:" & ColLetter &
RowValue).Select
Selection.Copy



Thanks
Todd Huttenstine

Function
 

one way:

Public Function CopyRange()
With Worksheets("Agents")
.Range(.Cells(2, 1), _
.Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, _
.Cells(1, 2).End(xlToRight).Column)).Copy
End With
End Function

note however, that this function won't do anything if called from the
worksheet (as functions can only return values to cells).

In article ,
"Todd Huttenstine" wrote:

The below code selects a range of cells. Using the below
method, how do I make this code into a function?


Dim RowValue
Dim ColumnValue
Dim ColLetter

RowValue = Worksheets("Agents").Range("A65536").End
(xlUp).Row
ColumnValue = Worksheets("Agents").Range("B:B").End
(xlToRight).Column
Worksheets("Agents").Range("A1").Offset(0, ColumnValue -
1).Select
'Converts column number to column letter
ColLetter = Left(ActiveCell.Address(, 0), InStr
(ActiveCell.Address(, 0), "$") - 1)
Worksheets("Agents").Range("A2:" & ColLetter &
RowValue).Select
Selection.Copy



Thanks
Todd Huttenstine

Function
 

I forgot a ). Should test.
..range(cells(2,1),cells(lr,lc)).copy
--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
Functions ( I assume you mean a UDF) return value as a regular function.
However, you code could be simplified and withOUT selections

with worksheets("Agents")
lr=.cells(rows.count,"a").end(xlup).row
'======
lc=.cells(columns.count,"b").end(xltoleft).column
'or
'lc=activecell.column
'========
.range(cells(2,1),cells(lr,lc).copy _
sheets("destinationsheet").range("a1")
end with
===========

--
Don Guillett
SalesAid Software

"Todd Huttenstine" wrote in message
...
The below code selects a range of cells. Using the below
method, how do I make this code into a function?


Dim RowValue
Dim ColumnValue
Dim ColLetter

RowValue = Worksheets("Agents").Range("A65536").End
(xlUp).Row
ColumnValue = Worksheets("Agents").Range("B:B").End
(xlToRight).Column
Worksheets("Agents").Range("A1").Offset(0, ColumnValue -
1).Select
'Converts column number to column letter
ColLetter = Left(ActiveCell.Address(, 0), InStr
(ActiveCell.Address(, 0), "$") - 1)
Worksheets("Agents").Range("A2:" & ColLetter &
RowValue).Select
Selection.Copy



Thanks
Todd Huttenstine

Function
 

You also forgot that Cells should have the prepended dot...

.range(.cells(2,1),.cells(lr,lc)).copy _


In article ,
"Don Guillett" wrote:

I forgot a ). Should test.
.range(cells(2,1),cells(lr,lc)).copy
--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
Functions ( I assume you mean a UDF) return value as a regular function.
However, you code could be simplified and withOUT selections

with worksheets("Agents")
lr=.cells(rows.count,"a").end(xlup).row
'======
lc=.cells(columns.count,"b").end(xltoleft).column
'or
'lc=activecell.column
'========
.range(cells(2,1),cells(lr,lc).copy _
sheets("destinationsheet").range("a1")

Function
 

Thanks JE
haste and failure to test make waste.

--
Don Guillett
SalesAid Software

"JE McGimpsey" wrote in message
...
You also forgot that Cells should have the prepended dot...

.range(.cells(2,1),.cells(lr,lc)).copy _


In article ,
"Don Guillett" wrote:

I forgot a ). Should test.
.range(cells(2,1),cells(lr,lc)).copy
--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
Functions ( I assume you mean a UDF) return value as a regular
function.
However, you code could be simplified and withOUT selections

with worksheets("Agents")
lr=.cells(rows.count,"a").end(xlup).row
'======
lc=.cells(columns.count,"b").end(xltoleft).column
'or
'lc=activecell.column
'========
.range(cells(2,1),cells(lr,lc).copy _
sheets("destinationsheet").range("a1")

Function
 

Yup. But if I had a nickel for every time *I've* made that particular
error...

In article ,
"Don Guillett" wrote:

Thanks JE
haste and failure to test make waste.

--
Don Guillett
SalesAid Software

"JE McGimpsey" wrote in message
...
You also forgot that Cells should have the prepended dot...

.range(.cells(2,1),.cells(lr,lc)).copy _