Patrick has a weekly Expense Ledger Spreadsheet set up; he wants to create a Macro to copy the Totals from Report 1 to a corresponding Week Number Row on the Summary worksheet. This Macro has to use the FIND command in VBA to figure out where to paste special the data. Follow along with Episode #1470 as Bill shows us how to accomplish the task.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1470: copy to row for week number.
Hey, this is a wild one.
This is from Patrick.
Patrick sent me a great email.
He’s trying to write some macros and Patrick-- his workbook is different than this one, but he basically has a weekly entry kind of thing.
He has some expense categories going down here and he has a few more than that I have.
Then days going across and right here, he's entering the week number.
The week number.
Now, at the end of the week, he wants to press a button and have these totals go back to the summary page, right?
So, going across-- he just wants the totals going across and he wants to find the week number.
Right now, he's manually selecting where it's going to go.
He’s like, “Hey, there has to be a better way to do that.” So, I actually-- I have two ways to do this.
The find command-- and it's just superstitious.
I don't like the find command because when the find command doesn't find it, it causes problems.
Also, to use the find command, we have to-- we have to turn on-- we have to turn on these settings that say, match entire cell contents, all right?
So, I run my macro and then six hours later, the find command stops working the way I think it is because you can't see that those weird settings have been set.
The other way to do it is to loop through all the cells.
Now, we only have 100 cells; it would be really easy to loop.
If it was me, I would loop, but I know I’d get some heat from people saying, why didn’t you just use find?
So, I'm bowing to the pressure.
I'm using find.
To make life a little bit more interesting, I assume that people might be adding a few extra blank rows here.
Maybe they enter notes or something like that, so I want the macro to be able to deal with that.
So, I started the macro-- I turned on the macro and I recorded using find to find the number 40 and column B, but then I've adapted that code of a fair amount.
All right, first thing I do, I define a variable called WSD.
That's the worksheet that's going to be the weekly entry sheet and WSS is another variable that is the worksheet called Summary.
The value to find is coming from the weekly entrance sheet cell E2.
Now, Patrick, wherever your week number is, I remember seeing the formula was in a nice little box, you want to plug that cell in.
Then here, this is kind of the recorded code.
We're going to go to the weekly summary sheet column’s 2, that's column B and we're going to find that number 40.
We're going to look after row 1 column 2.
We're looking at values, looking at the whole thing, going by rows-- doesn't matter, finding the next, match case false, search format equals false.
Now, the recorded macro actually activates that cell.
I don't want to activate that cell.
I'm just saying, hey, tell me what row it's in, right?
Tell me what row it’s in and then that’s saved to a variable.
So, this is pretty cool here.
If I run this, it says, hey, it's going to copy to row 52 and if we go in and look on the summary sheet down to row 52, sure enough, there's the 40.
If we would insert a few rows here, all right, and then switch back to VBA and run it, it's going to be great.
Now, the one danger here is that someone enters that this is we-- you know, they mean to enter that it’s week 41, they enter 141 and then it's not going to find it.
Then we're going to have an error.
Anyway, here we are.
Okay, so, now that I know that that's the hard part, figuring out where it's going to go, I'm going to copy from WSD.
So, WSD dot range and I’m going to switch back here and see that it is in I4 to I10.
I four colon I ten dot copy.
All right, now, if I was going to paste the formulas, I could do the copy and paste all in one, but unfortunately, that is not the situation here.
I have to paste these values and I have to transpose, which means turn it sideways.
So, WSS, that's my summary sheet, dot cells, select range, but I have to specify a row and a column.
Copy to row, that's a variable that tells me that it's in row 52 or 54 and the column number.
I need to go look and we’re copying it to column 3.
All right, dot paste special.
All right, this is annoying.
Paste special doesn't offer the IntelliSense when you use the cells, but if I would just come back here and say-- let's scroll up a little bit, I just come back here and say range dot paste special, then they actually-- they give me the IntelliSense.
That helps because it’s paste values comma and then they want us to enter a whole bunch of other stuff, but I know that I need that last thing out there, the transpose.
Transpose colon equals true.
So, you're allowed to do this.
You're allowed to not start naming the arguments, but then rather than enter them all in order, you can just come out here and hit the last one.
All right, so I did that for IntelliSense purposes and then I'm going to just delete back here.
Paste special is the one every time it-- the IntelliSense doesn't want to work.
Let’s make sure we can see.
Actually, I will add a continuation character here so you can see that whole thing.
So, paste special, paste values, transpose equals true.
We don't really need the message box anymore.
That was just kind of for my own purposes to know that it was working.
All right, so this is called copy totals.
Let’s go here, weekly entry, Alt F8, copy totals, run.
All right, something happened because the marching ants are there.
Come back and look and it is in row 40.
Let's do another little test here.
Let's just use week 14 and we'll put in a big number there and we'll run again.
Alt F8, copy totals, click run.
Now, we'll go look at week 14.
There we are.
We are in good shape with the right numbers and they have converted from formulas to values.
All right, so, to Patrick's question; yes, absolutely.
It's possible to do what you want to do.
We use the find command here just to locate the row where we want it to go and then copy and paste special.
All right, there you go.
Well, thanks to everyone for hanging in.
It’s Monday.
What a horrible long podcast to go through, but every once in a while, we should do a little VBA just to remind you that it can be a huge, huge lifesaver now and again.
Hey, I want to thank Patrick for sending that question in.
I want to thank you for stopping by.
See you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode 1470: copy to row for week number.
Hey, this is a wild one.
This is from Patrick.
Patrick sent me a great email.
He’s trying to write some macros and Patrick-- his workbook is different than this one, but he basically has a weekly entry kind of thing.
He has some expense categories going down here and he has a few more than that I have.
Then days going across and right here, he's entering the week number.
The week number.
Now, at the end of the week, he wants to press a button and have these totals go back to the summary page, right?
So, going across-- he just wants the totals going across and he wants to find the week number.
Right now, he's manually selecting where it's going to go.
He’s like, “Hey, there has to be a better way to do that.” So, I actually-- I have two ways to do this.
The find command-- and it's just superstitious.
I don't like the find command because when the find command doesn't find it, it causes problems.
Also, to use the find command, we have to-- we have to turn on-- we have to turn on these settings that say, match entire cell contents, all right?
So, I run my macro and then six hours later, the find command stops working the way I think it is because you can't see that those weird settings have been set.
The other way to do it is to loop through all the cells.
Now, we only have 100 cells; it would be really easy to loop.
If it was me, I would loop, but I know I’d get some heat from people saying, why didn’t you just use find?
So, I'm bowing to the pressure.
I'm using find.
To make life a little bit more interesting, I assume that people might be adding a few extra blank rows here.
Maybe they enter notes or something like that, so I want the macro to be able to deal with that.
So, I started the macro-- I turned on the macro and I recorded using find to find the number 40 and column B, but then I've adapted that code of a fair amount.
All right, first thing I do, I define a variable called WSD.
That's the worksheet that's going to be the weekly entry sheet and WSS is another variable that is the worksheet called Summary.
The value to find is coming from the weekly entrance sheet cell E2.
Now, Patrick, wherever your week number is, I remember seeing the formula was in a nice little box, you want to plug that cell in.
Then here, this is kind of the recorded code.
We're going to go to the weekly summary sheet column’s 2, that's column B and we're going to find that number 40.
We're going to look after row 1 column 2.
We're looking at values, looking at the whole thing, going by rows-- doesn't matter, finding the next, match case false, search format equals false.
Now, the recorded macro actually activates that cell.
I don't want to activate that cell.
I'm just saying, hey, tell me what row it's in, right?
Tell me what row it’s in and then that’s saved to a variable.
So, this is pretty cool here.
If I run this, it says, hey, it's going to copy to row 52 and if we go in and look on the summary sheet down to row 52, sure enough, there's the 40.
If we would insert a few rows here, all right, and then switch back to VBA and run it, it's going to be great.
Now, the one danger here is that someone enters that this is we-- you know, they mean to enter that it’s week 41, they enter 141 and then it's not going to find it.
Then we're going to have an error.
Anyway, here we are.
Okay, so, now that I know that that's the hard part, figuring out where it's going to go, I'm going to copy from WSD.
So, WSD dot range and I’m going to switch back here and see that it is in I4 to I10.
I four colon I ten dot copy.
All right, now, if I was going to paste the formulas, I could do the copy and paste all in one, but unfortunately, that is not the situation here.
I have to paste these values and I have to transpose, which means turn it sideways.
So, WSS, that's my summary sheet, dot cells, select range, but I have to specify a row and a column.
Copy to row, that's a variable that tells me that it's in row 52 or 54 and the column number.
I need to go look and we’re copying it to column 3.
All right, dot paste special.
All right, this is annoying.
Paste special doesn't offer the IntelliSense when you use the cells, but if I would just come back here and say-- let's scroll up a little bit, I just come back here and say range dot paste special, then they actually-- they give me the IntelliSense.
That helps because it’s paste values comma and then they want us to enter a whole bunch of other stuff, but I know that I need that last thing out there, the transpose.
Transpose colon equals true.
So, you're allowed to do this.
You're allowed to not start naming the arguments, but then rather than enter them all in order, you can just come out here and hit the last one.
All right, so I did that for IntelliSense purposes and then I'm going to just delete back here.
Paste special is the one every time it-- the IntelliSense doesn't want to work.
Let’s make sure we can see.
Actually, I will add a continuation character here so you can see that whole thing.
So, paste special, paste values, transpose equals true.
We don't really need the message box anymore.
That was just kind of for my own purposes to know that it was working.
All right, so this is called copy totals.
Let’s go here, weekly entry, Alt F8, copy totals, run.
All right, something happened because the marching ants are there.
Come back and look and it is in row 40.
Let's do another little test here.
Let's just use week 14 and we'll put in a big number there and we'll run again.
Alt F8, copy totals, click run.
Now, we'll go look at week 14.
There we are.
We are in good shape with the right numbers and they have converted from formulas to values.
All right, so, to Patrick's question; yes, absolutely.
It's possible to do what you want to do.
We use the find command here just to locate the row where we want it to go and then copy and paste special.
All right, there you go.
Well, thanks to everyone for hanging in.
It’s Monday.
What a horrible long podcast to go through, but every once in a while, we should do a little VBA just to remind you that it can be a huge, huge lifesaver now and again.
Hey, I want to thank Patrick for sending that question in.
I want to thank you for stopping by.
See you next time for another netcast from MrExcel.
