I am new to using macros, and having trouble with the copy function. I need
to copy one cell's contents to a range of cells above it to cell location A1.
The range is determined by how many cells contain data in column B. I use
end-down to determine the number of cells in column B, and this can vary each
time I run the report. How can I move to left one cell to column A when I
find the bottom of column B, enter a value, and then copy this value up to
cell A1?

Any help is greatly appreciated. Thanks.

Oddly enough you are better to move up from the bottom than down from the
top... So you can use something like this (untested but it sould be very
close)...

Sub CopyRange()
dim rng as range
dim wks as worksheet

set wks = sheets("Sheet1")
set rng = wks.range("B65536").end(xlUp).offset(0, -1)

rng.value = "Whatever" 'This could be changed to rng.formula = ...
rng.copy range(rng, wks.range("A2"))
End Sub
--
HTH...

Jim Thomlinson


"shoup" wrote:

I am new to using macros, and having trouble with the copy function. I need
to copy one cell's contents to a range of cells above it to cell location A1.
The range is determined by how many cells contain data in column B. I use
end-down to determine the number of cells in column B, and this can vary each
time I run the report. How can I move to left one cell to column A when I
find the bottom of column B, enter a value, and then copy this value up to
cell A1?

Any help is greatly appreciated. Thanks.

Try something similar to:

MyRow = ActiveCell.Row
MyCol = ActiveCell.Column - 1
Range(Cells(1, MyCol), Cells(MyRow, MyCol)) = "YourValueHere"


This can be done without moving the activecell, which is normally preferable.

"shoup" wrote:

I am new to using macros, and having trouble with the copy function. I need
to copy one cell's contents to a range of cells above it to cell location A1.
The range is determined by how many cells contain data in column B. I use
end-down to determine the number of cells in column B, and this can vary each
time I run the report. How can I move to left one cell to column A when I
find the bottom of column B, enter a value, and then copy this value up to
cell A1?

Any help is greatly appreciated. Thanks.