make cell active based on value - copy row it belonf to and paste to another row

Posted by Bryan on August 05, 2000 3:04 PM

Your site is incredibly helpful, please entertain the following question:

I have a spread sheet used to keep track of runtime statistics for batch jobs. One column contains
dates for every day of the year and another column lists the corresponding day of the week with "TODAY"
inserted in the cell in this column corrresponding to todays date. I need to be able to select a range of columns in the row in which this "TODAY" value exists in the day of the week column with a macro in excel '97. Once I
have the data copied, I need to paste it to another row. Please help. I don't know how to select a row to copy the data basd on the value of one cell within that row. How do you activate that cell? Your help is immensely appreciated.

Posted by Bryan on August 06, 0100 8:41 AM

Thanks, I'll give it a try.

Posted by Celia on August 05, 0100 5:21 PM

I have a spread sheet used to keep track of runtime statistics for batch jobs. One column contains

Bryan
You did not provide details of the column ref that contains the weekdays, what you want copied, and where it is to be pasted. I have,therefore, assumed that the weekdays are in column B and you want to copy the data in columns C:F of the "today" row to the next row :-

Sub copyToday()
Dim today As Range, sourceRange As Range, destinationRange As Range
Set today = Range("B:B").Find(What:="today")
Set sourceRange = Range(today.Offset(0, 1), today.Offset(0, 4))
Set destinationRange = sourceRange.Offset(1, 0)
sourceRange.Copy destinationRange
End Sub

Hope this helps

Celia