Hi. I am new to VBA. - as part of a custom function, I need to copy a
block of cells from one spreadsheet to another, based on the value in
the first column. For example, if cell A1 = '1', then copy A2:A10 to
new spreadsheet. What is the best way to do that?

Also, the user selects a range, which would be the input to my
function. How can I get the number of rows and columns in that range
and how can I refer to individual cells from there? For example, my
range is XY:ZH - I need to find out how many rows and columns are
there.. and also how do I get to the cell that is, for example, 5 rows
down, 2 columns right in my range? - something like X+2, Y+5 - how do
I convert that to a cell address?

Thank you very much..

Branimir,

See my remarks in-line with your message.

HTH,
Bernie


"Branimir" wrote in message
om...
Hi. I am new to VBA. - as part of a custom function, I need to copy
a
block of cells from one spreadsheet to another, based on the value
in
the first column. For example, if cell A1 = '1', then copy A2:A10 to
new spreadsheet. What is the best way to do that?
It depends on your logic: if there is a way to calc using the number
in cell A1, then that would be best. For example, if you want to copy
nine times the number in cell A1, you could use

Range("A2").Resize(Range("A1").Value*9,1).Copy

That does the copy. Where do you want to paste it? To a blank
worksheet, to a specific row in a worksheet, or at the end of the
column in a worksheet? Where you want will affect the needed code.


Also, the user selects a range, which would be the input to my
function.
The range you could use is simply
Activesheet.Selection

How can I get the number of rows and columns in that range
Activesheet.Selection.Rows.Count
Activesheet.Selection.Columns.Count

and how can I refer to individual cells from there?
Activesheet.Selection.Cells(1,1)
is the upper left cell of the selection

For example, my
range is XY:ZH - I need to find out how many rows and columns are
there.. and also how do I get to the cell that is, for example, 5
rows
down, 2 columns right in my range?
Range(XY:ZH).Cells(6,3)
or
Range(XY:ZH).Cells(1,1).Offset(5,2)
refers to that cell.

- something like X+2, Y+5 - how do
I convert that to a cell address?
Range(XY:ZH).Cells(5,2).Address

Thank you very much.

You're welcome very much.

Bernie