HI all,
I’m not a VBA user, so please be simple and clear in any answers.
I’ve created a wonderful excel spreadsheet that I rely on heavily to keep track of my daily status updates for any number of projects, aligned in rows. Sometimes I don’t have an update for a particular project, which would leave a cell blank for the current date, in that row. The cool thing is I can sort my projects any way I want to and the updates still work.
The spreadsheet is organized in a dateline format. My most current version starts at the beginning of September. The dates run left to right in ascending order in the topmost row. The most recent date is to the right. Beyond that, I have a column with the names of all the projects, each in its own row. Just to the right of that is a column JUST meant for the most recent status. And in one more column to the right, the date for that status. Every week I insert 7 blank columns between the last week, and the project name column. I fill the date row with the new dates. In this way, I can see in a snapshot the status of all my projects, no matter how far back they go.
To fetch the status, I have a macro that starts in the status column. It jumps to the left, finds the first filled cell (which is the most recent status update), copies it, jumps back to the right, and fills the status column cell.
That’s easy.
The hard part, and the one that I think gets funky and corrupted, and crashes the sheet from time to time, is fetching the date. Again, I start in the status column, jump to the left to the first filled cell, and then move UPWARD a certain number of rows, to land on the date at the top. Then back down the same number of moves, and to the right to fill in the date column. The problem here being that excel doesn’t have a single keyboard keystroke to jump to the top of any column. So I have to build a macro with a separate statement for each project row, starting with 1 up, 2 up, 3 up etc. I have about 60 rows so that’s a lot of processing. This is the part I’d like to automate in a better way.
I’ve just created a little example of my current macros.
Thank you!!
Naomi
