MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Copy Rows


Posted by Chris on November 21, 2001 7:17 AM

I'm trying to create a macro that copies a formula from one row to the next, and it changes the formula depending on the row it's in.

THe formula is this:

='01-94'!C$220

I have sheets labelled 01-94 to 12-99 (ie Jan 94 to Dec 99)

In the summary sheet i have the dates listed in one column i.e. January 1994 to December 1999
I now want to copy the above formula next to each month but without having to manually change it myself all the way down

So in the March 1995 row, the formula would be '03-95'!C$220

in the November 1999 row the formula would be '11-99'!C$220
etc

I have my macro set up as follows

dim year, month as integer
dim years, months as string

sub copyrows()

Range("B11").Select (this is where the formula is located)
Selection.copy

months = month
yeats = year

for year = 94 to 99
for month = 1 to 12

if month < 10 then
months = "0" & month
end if

ActiveCell = "='" & months & "-" & years & "'!R220C[1]"

THIS IS WHERE I WANT THE MACRO TO THEN GO TO THE NEXT ROW

next month
next year

end sub


ay help would be appreciated!


Posted by Juan Pablo on November 21, 2001 8:32 AM

ActiveCell.Offset(1).Select