I have a worksheet that gets data from a worksheet in another file. Each
month the cell references have to change to the next row. For example, this
month I'm referencing C134, which is the June line on the other file. Next
month, I need to reference C135, the July line.

How can I automate this? My first thought is to use a macro, but I can't
find an example anywhere of a VBA macro that moves a cell reference to the
next row.

Any suggestions would be appreciated.

Thank you,
Paula

To find the correct column you need to search the header row of the worksheet
for the correct month. You can do this either with a worksheet function of
VBA macro. the VBA macro would look like this

set bk = workbooks.open(filename:="book1.xls")
sest sht = bk.sheets("Sheets1")

set c = sht.rows(1).find(what:="June",lookin:=xlvalues ,lookat:=xlwhole)
if c is nothing then
msgbox("could not find June")
else
LastColumn = c.column
end if

"Paula" wrote:

I have a worksheet that gets data from a worksheet in another file. Each
month the cell references have to change to the next row. For example, this
month I'm referencing C134, which is the June line on the other file. Next
month, I need to reference C135, the July line.

How can I automate this? My first thought is to use a macro, but I can't
find an example anywhere of a VBA macro that moves a cell reference to the
next row.

Any suggestions would be appreciated.

Thank you,
Paula

Hi,
What you can do is to have a sheet (I call it "Reference"column A the month
and column B the cell reference starting in row 2)with a list of the month
and the cell reference, you can hide this tab, then somewhere your
spreadsheet you have the actual month let's say in A1, I suppose that the
cell referencing is in B2 so there enter
=sumproduct(--(A1=reference!$A$2:$A$14),reference!$b$2:$b$14)


"Paula" wrote:

I have a worksheet that gets data from a worksheet in another file. Each
month the cell references have to change to the next row. For example, this
month I'm referencing C134, which is the June line on the other file. Next
month, I need to reference C135, the July line.

How can I automate this? My first thought is to use a macro, but I can't
find an example anywhere of a VBA macro that moves a cell reference to the
next row.

Any suggestions would be appreciated.

Thank you,
Paula

Paula,

You have a lot of options.

1) Select the cells and do a find and replace of 134 with 135, choosing the look in formulas option.

2) Copy the cells down one row, then delete the old cells - will work if you are not specifically
referencing the old cells.

3) Change your formulas to something something that will auto-update, like

=OFFSET(C128,DATEDIF(DATEVALUE("1/1/2009"),NOW(),"m"),0)


HTH,
Bernie
MS Excel MVP


"Paula" wrote in message
...
I have a worksheet that gets data from a worksheet in another file. Each
month the cell references have to change to the next row. For example, this
month I'm referencing C134, which is the June line on the other file. Next
month, I need to reference C135, the July line.

How can I automate this? My first thought is to use a macro, but I can't
find an example anywhere of a VBA macro that moves a cell reference to the
next row.

Any suggestions would be appreciated.

Thank you,
Paula