Excel Macro Copy data to next sheet using <
Solved/Closed
Gino
-
Dec 21, 2010 at 07:27 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Dec 21, 2010 at 09:42 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Dec 21, 2010 at 09:42 AM
Related:
- Excel Macro Copy data to next sheet using <
- Mark sheet in excel - Guide
- How to open excel sheet in notepad++ - Guide
- Google sheet right to left - Guide
- Transfer data from one excel worksheet to another automatically - Guide
- How to screenshot excel sheet - Guide
1 response
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Dec 21, 2010 at 09:42 AM
Dec 21, 2010 at 09:42 AM
Hi Gino,
Let's start by looking at your conditions:
<10 to sheet "small"
<20 but >10 to sheet "medium"
<30 but >20 to sheet "large"
This way values 10 and 20 will be skipped, so I changed it into:
<10 to sheet "small"
<20 but >=10 to sheet "medium"
<30 but >=20 to sheet "large"
Find the conditions in the code and change into anything you would like.
Since you want the code to run automatically, I am wondering WHEN you want the code to run automatically?
Currently I have set the code to run when you deactivate the sheet "work".
Here is the code:
You implement the code by right-clicking the tab of sheet "work" and selecting "view code". Then paste the code in the big white empty field.
When you have done this, you will see on top of the field you pasted in two dropdown lists. Take a look at the one saying "deactivate". This list contains the options for running your code automatically.
Keep in mind that each time you run the code that all the values already being pasted will be pasted again.
Best regards,
Trowa
Let's start by looking at your conditions:
<10 to sheet "small"
<20 but >10 to sheet "medium"
<30 but >20 to sheet "large"
This way values 10 and 20 will be skipped, so I changed it into:
<10 to sheet "small"
<20 but >=10 to sheet "medium"
<30 but >=20 to sheet "large"
Find the conditions in the code and change into anything you would like.
Since you want the code to run automatically, I am wondering WHEN you want the code to run automatically?
Currently I have set the code to run when you deactivate the sheet "work".
Here is the code:
Private Sub Worksheet_Deactivate()
Set MR = Range("A1:A10")
For Each cell In MR
If cell.Value < 10 Then
cell.Offset(0, 1).Copy
Sheets("small").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
End If
If cell.Value >= 10 And cell.Value < 20 Then
cell.Offset(0, 1).Copy
Sheets("medium").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
End If
If cell.Value >= 20 And cell.Value < 30 Then
cell.Offset(0, 1).Copy
Sheets("large").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
End If
Next
Application.CutCopyMode = False
End Sub
You implement the code by right-clicking the tab of sheet "work" and selecting "view code". Then paste the code in the big white empty field.
When you have done this, you will see on top of the field you pasted in two dropdown lists. Take a look at the one saying "deactivate". This list contains the options for running your code automatically.
Keep in mind that each time you run the code that all the values already being pasted will be pasted again.
Best regards,
Trowa
