Paste WIth Else If

[Vampirex]

Hi All,

Need a bit of help with my VBA formula.

Trying to copy and paste a cell from Sheet 2 or 3 depending on the value of a cell from sheet 1. Please see code below:

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("B6").Value = "Sheet2" Then

Sheets("Sheet2").Select

ActiveCell.SpecialCells(xlLastCell).Select

Selection.Copy

Worksheets("Sheet1").Select

Range("B2").Select

Selection.Paste

Sheets("IndiCater").Select

Application.CutCopyMode = False

ActiveSheet.Range("A1").Select

Sheets("Sheet1").Select

ElseIf Range("B6").Value = "Sheet3" Then

Sheets("Sheet3").Select

ActiveCell.SpecialCells(xlLastCell).SelectSelection.Copy

Worksheets("Sheet1").Select

Range("B2").Select

Selection.Paste

Sheets("IndiCater").Select

Application.CutCopyMode = False

ActiveSheet.Range("A1").Select

Sheets("Sheet1").Select

End If

End Sub

So depending on what I select, either Sheet 2 or Sheet 3, I need it to go to the relevant sheet and copy the very last number of that sheet and copy into Sheet 1 Cell B2. I can get it to do everything else apart from the pasting. When I have tried with this, it crashes and continuously loops. How do I break the loop and make it do it just the one time until I select the other sheet?

Please could someone help. I have tried loads of different ways but it always crashes and is continuously looping itself.

Thanks For your Help.

 

[My Aswer Is This]

This is a auto sheet change script.
Which runs when you enter a certain value in a certain cell.
I see no cell that you have designated as the cell which would activate the script.
That means the script runs when ever you change any cell value in the sheet.

You said:
copy the very last number of that sheet and copy into Sheet 1 Cell B2
Copy what last number in what column
So are you saying when you activate any sheet you want the cell value in the last cell of colmn A copied to sheet1 Range B2?

 

[Vampirex]

Hi,

Thank you or your reply.

The Cell that would activate the script would be Cell B6 on Sheet 1. This has a drop down list which you are able to select either Sheet 2 or Sheet 3.
Once you have selected the sheet you want, I want the script to go to that sheet and copy and paste the last cell which has a value and then go back to Sheet 1 and paste in Cell B2.

Thanks

 

[My Aswer Is This]

You said:
copy and paste the last cell which has a value last cell with value in what column

Last cell in column A with value or column B or column C

 

[Vampirex]

Sorry. I want the last Cell in Column A with value.

Thanks

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window
Put this script in the sheet where you will enter a value in Range B6

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  2/21/2020  9:22:21 AM  EST
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
On Error GoTo M
    If Target.Address = Range("B6").Address Then
        Dim Lastrow As Long
        Lastrow = Sheets(Target.Value).Cells(Rows.Count, "A").End(xlUp).Row
        Target.Offset(-4).Value = Sheets(Target.Value).Cells(Lastrow, 1).Value
    End If
    Exit Sub
M:
    
    MsgBox "You do not have a sheet named  " & Target.Value
End Sub