Copying block (range of cells) based on a value of a specific cell-VBA

  • Thread starter Thread starter Branimir
  • Start date Start date
B

Branimir

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 said:
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?
Click to expand...
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.
Click to expand...
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?
Click to expand...
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?
Click to expand...
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.
Click to expand...

You're welcome very much.

Bernie
 
Back
Top