Kim has designed a simple data entry spreadsheet and would like to have a way to paste the data into the next empty row of a database worksheet she has created. In Episode #1272, takes a look at how to finesse the macro recorder to solve this problem.
Transcript of the video:
The MrExcel podcast is sponsored by Easy Excel. Learn Excel from MrExcel podcast, episode 1272.
Save a Form. Well, hey, welcome back to the MrExcel net cast. I'm Bill Jelen.
Today's question sent in by Kim.
Kim's been watching the podcast and says that she, uh, wants to have a way to take data from a form.
She might get us to a customer, something like that, and copy that data and paste it into the next spot on sort of like a database kind of thing.
And she says, you know, hey, look, I don't want to get in and code a lot of VBA.
Can you do this somehow without making me code any VBA, she tried to record a macro that would copy and paste transpose.
However, couldn't figure out how to get it in the next row.
So we're going to use the macro recorder here. I'm just going to select a cell.
Before I start going to a record macro, let's call this a post to database control shift, uh, S or save, click OK. All right.
So now the macro recorder is running and most of the time I tell people that they need to go in and turn on relative reference.
But I don't want to do that here in this case, because the first part of this macro is going to be absolute.
Every time we run the macro, we want to come here to the form, a worksheet and select those four cells. Every single time.
I don't care where I started, where the cursor was to begin with. I always want to select those cells. I'm going to copy.
I can just control C or I can come back here on the home tab and choose copy, right then we're going to go to database. And again, this next step is also going to be absolute, not relative.
I'm going to use control G or F5 for go-to.
I'm going to go to the last cell in the spreadsheet.
So a one Oh four eight five seven six, by the way, A, B, C you should choose a column that is always going to be filled in.
So if one of these is optional, don't choose that column. Choose another column, click. OK. That sends us down to the bottom.
Now, now at this point, where to change the macro recorder into relative mode, relative mode.
So we go back to view macros use relative references. And from here, I'm going to press control up.
Arrow control up arrow will get me to the point where I'm on the last row data, because relative is turned on and now pressed down one more time and we can go to home paste and transposed.
There it is right there and it paste it. And we're now good to go at this point.
I want to get out of relative mode and go back into regular mode.
So I go back to view macros, turn off relative reference, go back to form. Probably clear the data.
I'm not sure if Kim wants to do that or not. Let's assume we want to clear the data.
So re-select the data press delete, and then select the one cell.
So where the person is ready to start filling out the form. All right. So we stop recording right now.
It's control shift S if you're a keyboard person, if you're not a keyboard person, let's just create an easy way to save this form.
We'll come back here. Uh, we can either do clip art, or we can do shapes. Just choose something.
The lightning bolt, a little lightning bolt there. Oh, that's horrible. Let's just go with plain and right.
Click assign macro. And that's the macro Radis recording click. Okay. All right.
So now, uh, PO number, the let in department is manufacturing.
Contact name is Joe and the contact number five, five, five dash one two one two. Click the lightning bolt, bam.
And you saw the screen flash there a little bit because we didn't get into VBA.
We couldn't turn off screen updating, but when we go look, you'll see that it is now written to the next row.
So it seems like a bit of a hassle having to go to the last row control up arrow, to get to the, uh, the last row with data.
And then down one, but solves a problem that, that whole thing with the macro recorder never had to go look at VBA or, Hey, I want to thank you for stopping by. We'll see you. Next time another net cast from MrExcel.
Save a Form. Well, hey, welcome back to the MrExcel net cast. I'm Bill Jelen.
Today's question sent in by Kim.
Kim's been watching the podcast and says that she, uh, wants to have a way to take data from a form.
She might get us to a customer, something like that, and copy that data and paste it into the next spot on sort of like a database kind of thing.
And she says, you know, hey, look, I don't want to get in and code a lot of VBA.
Can you do this somehow without making me code any VBA, she tried to record a macro that would copy and paste transpose.
However, couldn't figure out how to get it in the next row.
So we're going to use the macro recorder here. I'm just going to select a cell.
Before I start going to a record macro, let's call this a post to database control shift, uh, S or save, click OK. All right.
So now the macro recorder is running and most of the time I tell people that they need to go in and turn on relative reference.
But I don't want to do that here in this case, because the first part of this macro is going to be absolute.
Every time we run the macro, we want to come here to the form, a worksheet and select those four cells. Every single time.
I don't care where I started, where the cursor was to begin with. I always want to select those cells. I'm going to copy.
I can just control C or I can come back here on the home tab and choose copy, right then we're going to go to database. And again, this next step is also going to be absolute, not relative.
I'm going to use control G or F5 for go-to.
I'm going to go to the last cell in the spreadsheet.
So a one Oh four eight five seven six, by the way, A, B, C you should choose a column that is always going to be filled in.
So if one of these is optional, don't choose that column. Choose another column, click. OK. That sends us down to the bottom.
Now, now at this point, where to change the macro recorder into relative mode, relative mode.
So we go back to view macros use relative references. And from here, I'm going to press control up.
Arrow control up arrow will get me to the point where I'm on the last row data, because relative is turned on and now pressed down one more time and we can go to home paste and transposed.
There it is right there and it paste it. And we're now good to go at this point.
I want to get out of relative mode and go back into regular mode.
So I go back to view macros, turn off relative reference, go back to form. Probably clear the data.
I'm not sure if Kim wants to do that or not. Let's assume we want to clear the data.
So re-select the data press delete, and then select the one cell.
So where the person is ready to start filling out the form. All right. So we stop recording right now.
It's control shift S if you're a keyboard person, if you're not a keyboard person, let's just create an easy way to save this form.
We'll come back here. Uh, we can either do clip art, or we can do shapes. Just choose something.
The lightning bolt, a little lightning bolt there. Oh, that's horrible. Let's just go with plain and right.
Click assign macro. And that's the macro Radis recording click. Okay. All right.
So now, uh, PO number, the let in department is manufacturing.
Contact name is Joe and the contact number five, five, five dash one two one two. Click the lightning bolt, bam.
And you saw the screen flash there a little bit because we didn't get into VBA.
We couldn't turn off screen updating, but when we go look, you'll see that it is now written to the next row.
So it seems like a bit of a hassle having to go to the last row control up arrow, to get to the, uh, the last row with data.
And then down one, but solves a problem that, that whole thing with the macro recorder never had to go look at VBA or, Hey, I want to thank you for stopping by. We'll see you. Next time another net cast from MrExcel.
