VBA to cut the last populated value from one worksheet (based on value) to paste into another worksheet

[DebbieEdwards]

I have a workbook with Multiple worksheets
The names are:</SPAN>
10</SPAN>
20</SPAN>
Main
</SPAN>
In 10,20 Is a Column (Column C) which contains Codes. For example</SPAN>
RT102422</SPAN>
RT104646</SPAN>
RT105678
</SPAN>
In Main I have a column Called Spent. This is a drop down of the above 10,20</SPAN>
When a user chooses for example 10 I want the macro to start up and:</SPAN>

1. Find the Active Cell in Main.</SPAN>
2. If its 10 I then go to worksheet 10</SPAN>
   1. Find the last cell with data in it</SPAN>
   2. Cut the data</SPAN>
   3. Go back to Main</SPAN>
   4. Paste the data into the column before the active cell</SPAN>
3. If its 20 I then go to worksheet 20</SPAN>
   1. Find the last cell with data in it</SPAN>
   2. Cut the data</SPAN>
   3. Go back to Main</SPAN>
   4. Paste the data into the column before the active cell</SPAN>

I have some VBA that will check for a change in a column and if it changes I then go to the macro.</SPAN>
I have some code that cuts and pastes the data but obviously this needs changing quite a lot

'In Main you are in the Active cell
    Range("C2").Select

    'If the Active Cell is 10 Go to
    Sheets("TEN").Select

    'Find the Last cell and cut it 
    Range("C3").Select
    Selection.Cut

    'Go back to main and find the cell before the active cell and paste in the data
    Sheets("Main").Select
    Range("B2").Select
    ActiveSheet.Paste

</SPAN>
</SPAN>

Apologies in advance. I dont use VBA that much but it anyone can give me any pointers that would be great

Debbie

 

[Snakehips]

Debbie,

Try....right click the Main sheet tab >> View Code and paste the below into the code pane.

If the sheets are named "10" & "20" then use...

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column <> 3 Then Exit Sub
If Not Target = "" Then
Set ws = Sheets(Target.Text)
ws.Select
lr = ws.Cells(Rows.Count, 3).End(xlUp).Row
ws.Cells(ws.Cells(Rows.Count, 3).End(xlUp).Row, 3).Cut
Sheets("Main").Select
Target.Offset(0, -1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End If
End Sub

or if the sheets are named 'TEN" & "TWENTY" then use...

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column <> 3 Then Exit Sub
If Not Target = "" Then
If Target = 10 then Sname = "TEN"
If Target = 20 then Sname = "TWENTY"
Set ws = Sheets(Sname)
ws.Select
lr = ws.Cells(Rows.Count, 3).End(xlUp).Row
ws.Cells(ws.Cells(Rows.Count, 3).End(xlUp).Row, 3).Cut
Sheets("Main").Select
Target.Offset(0, -1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End If
End Sub

Hope that helps.

 

[DebbieEdwards]

Thankyou,

I will see what I can do with that

Debbie