When you mark a record as Paid, can you automatically copy that record to another worksheet? Today's episode shows an event handler macro that will move records after they have been marked as Paid.
Transcript of the video:
The MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1903.
Move Paid Records to Another Worksheet.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question: we have this data here on the original sheet and when something gets marked as Paid, so we changed from N to Y, we want to move that data automatically over to the data sheet.
I'm presuming, just to the next blank row.
And I'm going to do this with VBA.
You might be able to write an array formula, that would extract all of the Y values to that other sheet, I'm going to do it with VBA.
And I notice that my data… my workbook is stored as xlsx, that's the one type of file that is not allowed to have macro, so I have to do File, Save As and when I go to save this, I will change the type to xlsm or xlsb, any of them, just not xlsx.
And we'll save it there, alright, so now we're allowed to have macros.
If you've never used macros before, we want to do Alt, T, M, S and make sure that we're not set to that highest setting, which is the default.
You want to come down to “Disable all macros with notification”.
All right, and I'm currently here on the original sheet, we're looking for paid in column G, which is the one, two, three, four, five, six, seventh column.
So I'm going to press Alt+F11 and in the Project Explorer, if you can't see the Project Explorer, go to View, Project Explorer or Ctrl+R, where I look for our workbook.
There's 1903, might be collapsed, you might need to expand it.
Finally, the original sheet and double click.
Alright now, from here we're going to choose from the top left drop-down, choose Worksheet, they automatically assume we want SelectionChange, which is not right.
I will change that to Change.
So I'm going to get rid of this tiny macro here.
So this is a little macro, that's going to run every time that we change any cell on the worksheet.
So the first thing we have to do, we have to figure out, what we just change.
The variable Target holds what we just change, so: If Target.Column = 7.
Now, in my case, it's 7 because that is the seventh column.
If your data is somewhere else, then you need to use whichever column number it is.
Then: if Target.Value = “Y” (yes) and let's see.
Do we want to allow them to type lowercase yes, or it has to be uppercase yes?
I guess if we want to do either, we would say: if the lowercase (lcase) of what we just typed is equal to… a lowercase (“y”), yes, that way if they put in either a lowercase “y” or an uppercase “Y”, and we're good to go.
Then.
We want to ‘ Move this record.
End If, End If.
So, that's the start of our macro there.
Now, the ‘ Move this record part, we need to figure out where to go.
All right, so the first thing we have to figure out, is where's the next available record on the data worksheet.
And I'm going to switch back to Excel here and go look at the data worksheet.
And we have to choose a column, it's going to be filled in all the time.
Now, like in this case: Region is perfect, that's always filled in.
But you might have a column that has a sanitation or apartment number, that's not always filled in and if that was in column A, we'd want to move from column 1 over to column 2.
So back to VBA, NR is my variable to hold next record, NR = Worksheets(“Data”).Cells - let's go - Rows.Count, 1).End(xlup) - that's the letter L there - .Row + 1.
All right, so that's going to find the last row in the data worksheet that has data, and add 1 to it.
And then FR = Target.Row, and then we're just going to do a copy so from the: Worksheets(“Original”).Cells(FR, 1).Resize(1, -and then we have to figure out, how many columns we have.
And I'll go back and look here.
So seven columns, if your data went out past column G, you might have more columns, so you have to get the right number of columns- 7).Copy _.
And I'm going to put a continuation character, the underscore (_), a space-underscore is a continuation character.
And the Destination:=Worksheet(“Data”.cells(NR, 1).
Alright, so just a closing parenthesis there.
That should be it.
So just those short little lines of code, put back here on the Worksheet code pane, let's just give it a try here.
So we'll choose AT&T, this is the 2401, I'll change that from N to Y, I'll press Enter.
See, it didn't look like anything happened, you actually might have seen the cursor change to the spinning, blue weight symbol for just less than a second.
And when we come back here to the data tab, that record has been added to the correct place.
Back here, which is another record, yes (Y), Enter, doesn't look like anything happening, but the data is automatically being added back there to the data worksheet.
Short little macro, I know a lot of people have never used macros before, but this should not be that intimidating, you know, just a few lines of code that will solve this problem.
Hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode 1903.
Move Paid Records to Another Worksheet.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question: we have this data here on the original sheet and when something gets marked as Paid, so we changed from N to Y, we want to move that data automatically over to the data sheet.
I'm presuming, just to the next blank row.
And I'm going to do this with VBA.
You might be able to write an array formula, that would extract all of the Y values to that other sheet, I'm going to do it with VBA.
And I notice that my data… my workbook is stored as xlsx, that's the one type of file that is not allowed to have macro, so I have to do File, Save As and when I go to save this, I will change the type to xlsm or xlsb, any of them, just not xlsx.
And we'll save it there, alright, so now we're allowed to have macros.
If you've never used macros before, we want to do Alt, T, M, S and make sure that we're not set to that highest setting, which is the default.
You want to come down to “Disable all macros with notification”.
All right, and I'm currently here on the original sheet, we're looking for paid in column G, which is the one, two, three, four, five, six, seventh column.
So I'm going to press Alt+F11 and in the Project Explorer, if you can't see the Project Explorer, go to View, Project Explorer or Ctrl+R, where I look for our workbook.
There's 1903, might be collapsed, you might need to expand it.
Finally, the original sheet and double click.
Alright now, from here we're going to choose from the top left drop-down, choose Worksheet, they automatically assume we want SelectionChange, which is not right.
I will change that to Change.
So I'm going to get rid of this tiny macro here.
So this is a little macro, that's going to run every time that we change any cell on the worksheet.
So the first thing we have to do, we have to figure out, what we just change.
The variable Target holds what we just change, so: If Target.Column = 7.
Now, in my case, it's 7 because that is the seventh column.
If your data is somewhere else, then you need to use whichever column number it is.
Then: if Target.Value = “Y” (yes) and let's see.
Do we want to allow them to type lowercase yes, or it has to be uppercase yes?
I guess if we want to do either, we would say: if the lowercase (lcase) of what we just typed is equal to… a lowercase (“y”), yes, that way if they put in either a lowercase “y” or an uppercase “Y”, and we're good to go.
Then.
We want to ‘ Move this record.
End If, End If.
So, that's the start of our macro there.
Now, the ‘ Move this record part, we need to figure out where to go.
All right, so the first thing we have to figure out, is where's the next available record on the data worksheet.
And I'm going to switch back to Excel here and go look at the data worksheet.
And we have to choose a column, it's going to be filled in all the time.
Now, like in this case: Region is perfect, that's always filled in.
But you might have a column that has a sanitation or apartment number, that's not always filled in and if that was in column A, we'd want to move from column 1 over to column 2.
So back to VBA, NR is my variable to hold next record, NR = Worksheets(“Data”).Cells - let's go - Rows.Count, 1).End(xlup) - that's the letter L there - .Row + 1.
All right, so that's going to find the last row in the data worksheet that has data, and add 1 to it.
And then FR = Target.Row, and then we're just going to do a copy so from the: Worksheets(“Original”).Cells(FR, 1).Resize(1, -and then we have to figure out, how many columns we have.
And I'll go back and look here.
So seven columns, if your data went out past column G, you might have more columns, so you have to get the right number of columns- 7).Copy _.
And I'm going to put a continuation character, the underscore (_), a space-underscore is a continuation character.
And the Destination:=Worksheet(“Data”.cells(NR, 1).
Alright, so just a closing parenthesis there.
That should be it.
So just those short little lines of code, put back here on the Worksheet code pane, let's just give it a try here.
So we'll choose AT&T, this is the 2401, I'll change that from N to Y, I'll press Enter.
See, it didn't look like anything happened, you actually might have seen the cursor change to the spinning, blue weight symbol for just less than a second.
And when we come back here to the data tab, that record has been added to the correct place.
Back here, which is another record, yes (Y), Enter, doesn't look like anything happening, but the data is automatically being added back there to the data worksheet.
Short little macro, I know a lot of people have never used macros before, but this should not be that intimidating, you know, just a few lines of code that will solve this problem.
Hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
