MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro in Excel

Posted by Ryan on April 26, 2001 8:14 AM

I would like a macro to automate a lengthy step we go through every day.

Some background: every day, a spreadsheet is updated and we capture that data by just linking that day's number from say, cell W20, by inputting the reference $C$17. Then, we copy and paste special in W20 to capture the values. Then, we just input $C$17 into the cell below that one (W21) to capture tomorrow's value. However, we do this for 15 states, so we do this process 15 times a day.

I would like a macro to do the following:
1) Hard code the value into the current cell
2) Input the cell reference into the cell below it($C$17)
3) Repeat this process for 15 states, all within the same spreadsheet. So, the cell references would be something like $C$17......$Q$17,and they will always be the same.

Thanks for your input,

Posted by Dave Hawley on April 26, 2001 11:36 PM

Hi Ryan

The process could certainly be Automated, but I'm not too clear on the criteria and details. Try the code below to see if I'm on the right track

Sub TryThis()
'Written by OzGrid Business Applications

Selection = .Range("C17")
Selection.Offset(1, 0).Formula = .Range("C17").Formula

End Sub


OzGrid Business Applications