A question from YouTube asks how to write a macro to cut selected rows to Sheet2, adding a completion date. Episode 973 shows you how to write this short macro.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
Basically, we start out with massive amount of data.
We say how we're going to analyze this well.
Let’s fire up a pivot table.
Let’s see if we can solve this problem.
Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
Boy, a tough question today, sent in via YouTube.
So, one says they have 2 sheets sheet 1, sheet 2.
they want to be able to select some cells here.
Run a macro and have Excel take those rows.
Cut them from here.
Paste them to the next row on sheet 2.
Add a Date on sheet 2.
Alright, so let's take a look.
I started to write some code here.
First thing I do is, I declare a couple of variables, object variables WS1 and WS2 is worksheet.
And then WS1 is the active sheet whatever it happens to be.
WS2, I define as sheet 2.
Now, there's a property called selection and selection might be a single cell.
it might be a number of rows and so I'm going to set up a variable called FirstRow is equal to selection dot rows plural one dot row and what this is saying hey, how many rows we happen to have in this selection.
Tell me the first one.
Tell me what row number it is and then we'll figure out the RowCount.
That's another variable, is equal to selection dot rows dot count.
Alright!
Next row on sheet 2.
How do we do this.
We go down to the last cell in the sheet we press the End key Press the up arrow key.
See what row we're on.
So, next row is equal to WS2 dot cells rows dot count comma 1 dot and xlup dot row.
That's going to give me to the last row with data.
And I want to add 1 to that plus 1.
Alright, and then the copy here.
I already wrote this one.
We start on WS1.
Copy from the first row.
Resize to be the row count.
There were 8 columns and the destination that is WS2.
And we go to the next row.
That's a variable.
So, the first time we run it will probably go to row 2 then I'll go later.
Alright! then they wanted to add a date.
Alright, so how we're going to do this.
WS2 dot cells.
Alright!
Well, we know it's going to write to next row.
Next row...
There's 8 columns of data already.
So we want to put that in the ninth column.
And I'm going to resize it to be RowCount comma 1.
So, 1 column wide RowCount columns high dot value is equal to there's a variable called Date.
Thiis is a built-in variable in VBA.
I'll put the current date in there and then back on WS1.
Delete those rows and shift xlup.
Now, the reason I did a copy and then the delete, copy is nice in VBA.
and then I can be sitting on sheet 1 and copy to sheet 2 without ever going back to sheet 2.
I using cut, wasn't sure that that was going to work.
So that seems to be a good way to go.
Now, I wrote that Macro without using the Macro recorder.
I want to go into Macros.
I want to look at the CutRows macro and click options.
And this is where I can assign it to a shortcut key.
Normally, with the Macro recorder, we get to declare this upfront.
And so now, here's the big test.
will select a few rows here.
Looks like 4, 5 and 6.
If I look on sheet 2, there's currently nothing.
So, press [ CTRL+M ].
That rows disappeared from here 4 5 and 6.
If I look on sheet 2, they showed up in the correct place.
And we have a Date along the left-hand side.
Let's try something else.
I'll just dry single row.
Row 14 [ CTRL+M ] It disappears from here, and it shows up in Row 4 Looks like we have it.
So, there you have it.
You know 2,4, 6, 7 lines of code.
Plus the declaration to the top to solve that problem.
So, thanks for that question.
Thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
I am Bill Jelen.
Basically, we start out with massive amount of data.
We say how we're going to analyze this well.
Let’s fire up a pivot table.
Let’s see if we can solve this problem.
Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
Boy, a tough question today, sent in via YouTube.
So, one says they have 2 sheets sheet 1, sheet 2.
they want to be able to select some cells here.
Run a macro and have Excel take those rows.
Cut them from here.
Paste them to the next row on sheet 2.
Add a Date on sheet 2.
Alright, so let's take a look.
I started to write some code here.
First thing I do is, I declare a couple of variables, object variables WS1 and WS2 is worksheet.
And then WS1 is the active sheet whatever it happens to be.
WS2, I define as sheet 2.
Now, there's a property called selection and selection might be a single cell.
it might be a number of rows and so I'm going to set up a variable called FirstRow is equal to selection dot rows plural one dot row and what this is saying hey, how many rows we happen to have in this selection.
Tell me the first one.
Tell me what row number it is and then we'll figure out the RowCount.
That's another variable, is equal to selection dot rows dot count.
Alright!
Next row on sheet 2.
How do we do this.
We go down to the last cell in the sheet we press the End key Press the up arrow key.
See what row we're on.
So, next row is equal to WS2 dot cells rows dot count comma 1 dot and xlup dot row.
That's going to give me to the last row with data.
And I want to add 1 to that plus 1.
Alright, and then the copy here.
I already wrote this one.
We start on WS1.
Copy from the first row.
Resize to be the row count.
There were 8 columns and the destination that is WS2.
And we go to the next row.
That's a variable.
So, the first time we run it will probably go to row 2 then I'll go later.
Alright! then they wanted to add a date.
Alright, so how we're going to do this.
WS2 dot cells.
Alright!
Well, we know it's going to write to next row.
Next row...
There's 8 columns of data already.
So we want to put that in the ninth column.
And I'm going to resize it to be RowCount comma 1.
So, 1 column wide RowCount columns high dot value is equal to there's a variable called Date.
Thiis is a built-in variable in VBA.
I'll put the current date in there and then back on WS1.
Delete those rows and shift xlup.
Now, the reason I did a copy and then the delete, copy is nice in VBA.
and then I can be sitting on sheet 1 and copy to sheet 2 without ever going back to sheet 2.
I using cut, wasn't sure that that was going to work.
So that seems to be a good way to go.
Now, I wrote that Macro without using the Macro recorder.
I want to go into Macros.
I want to look at the CutRows macro and click options.
And this is where I can assign it to a shortcut key.
Normally, with the Macro recorder, we get to declare this upfront.
And so now, here's the big test.
will select a few rows here.
Looks like 4, 5 and 6.
If I look on sheet 2, there's currently nothing.
So, press [ CTRL+M ].
That rows disappeared from here 4 5 and 6.
If I look on sheet 2, they showed up in the correct place.
And we have a Date along the left-hand side.
Let's try something else.
I'll just dry single row.
Row 14 [ CTRL+M ] It disappears from here, and it shows up in Row 4 Looks like we have it.
So, there you have it.
You know 2,4, 6, 7 lines of code.
Plus the declaration to the top to solve that problem.
So, thanks for that question.
Thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
