Copy and Paste range into another tab

[Animalised]

Good Evening,

I'm trying to find a cell range, copy the information from that range and paste it into another tab. This is my code so far:

Private Sub CommandButton1_Click()
    Sheets("Summary").Select
    ' Find "C4" cell of data
        If ThisValue = "PB" Then
            Range(Cells(4, 2), Cells(4, 8)).Copy
            Sheets("Peter Beardsall").Select
            Cells(4, 2).Select
            ActiveCell.EntireRow.Insert
            Cells(NextRow, 1).Select
            ActiveSheet.Paste
            Sheets("Summary").Select
        ElseIf ThisValue = "ND" Then
            Range(Cells(4, 2), Cells(4, 8)).Copy
            Sheets("Nick Dyson").Select
            Cells(4, 2).Select
            ActiveCell.EntireRow.Insert
            Cells(NextRow, 1).Select
            ActiveSheet.Paste
            Sheets("Summary").Select
        End If
End Sub

I'm not sure what I could do to make it work as nothing seems to happen.


Any help would be greatly appreciated!

 

[George J]

Hopefully this gives you a start - I need to call it a night.

Private Sub CommandButton1_Click()
Dim ThisValue as string
    Sheets("Summary").Select
    ' Find "C4" cell of data
set ThisValue = Range("C4").value 'Tell the computer what value ThisValue should have
        If ThisValue = "PB" Then 'The computer wouldn't know what ThisValue is if variable not set at the top
            Range(Cells(4, 2), Cells(4, 8)).Copy
            Sheets("Peter Beardsall").Select

 

[MARK858]

You aren't assigning ThisValue as a range or constant and you aren't searching through any range for it.
Please provide more details of what range you are trying to search in and what ThisValue actually is.

 

[Animalised]

Hi George,

I shall hastily write this, thank you for the advice and small tips.

I will definitely give this a go, thank you again very much for helping this newbie!


Good night!

 

[Animalised]

Hi Mark,

I'm aiming to assign "C4" as TheValue that needs to be checked. I'm trying to check this cell to ensure it has a specific value in it (i.e. PB, ND).

(I have also attached an image, below, to demonstrate the cell (the cell is circled in red) im trying to assign ThisValue to)

Again, thank you for any and all help!

 

[MARK858]

Not sure what the cell you are pasting in supposed to be i.e. what is NextRow so I have commented it out so you will have to amend that part

Sub CommandButton1_Click()
    Dim ThisValue As String
    Sheets("Summary").Select
    ' Find "C4" cell of data
    ThisValue = Cells(4, "C").Value
    If ThisValue = "PB" Then
        Range(Cells(4, 2), Cells(4, 8)).Copy
        Sheets("Peter Beardsall").Cells(4, 2).EntireRow.Insert
        '            Cells(NextRow, 1).Select
        '            ActiveSheet.Paste
        Sheets("Summary").Select
    ElseIf ThisValue = "ND" Then
        Range(Cells(4, 2), Cells(4, 8)).Copy
        Sheets("Nick Dyson").Cells(4, 2).EntireRow.Insert
        '            Cells(NextRow, 1).Select
        '            ActiveSheet.Paste
        Sheets("Summary").Select
    End If
    Application.CutCopyMode = False
End Sub

 

[MARK858]

Looking at your other post on the subject

Private Sub CommandButton1_Click()
    Dim ThisValue As String
    Sheet3.Name = "Peter Beardsall"
    Sheet1.Name = "Summary"
    Sheets("Summary").Select
    ' Find "C4" cell of data
    ThisValue = Cells(4, "C").Value
    If ThisValue = "PB" Then
        Range(Cells(4, 2), Cells(4, 8)).Copy Sheets("Peter Beardsall").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        Sheets("Summary").Select
    ElseIf ThisValue = "ND" Then
        Range(Cells(4, 2), Cells(4, 8)).Copy Sheets("Nick Dyson").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        Sheets("Summary").Select
    End If
End Sub

 

[MARK858]

Or better written (and with the 2 lines I had in for testing removed)

Private Sub CommandButton1_Click()
    Dim ThisValue As String
    With Sheets("Summary")
        ' Find "C4" cell of data
        ThisValue = .Cells(4, "C").Value

        If ThisValue = "PB" Then
            .Range(.Cells(4, 2), .Cells(4, 8)).Copy Sheets("Peter Beardsall").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        ElseIf ThisValue = "ND" Then
            .Range(.Cells(4, 2), .Cells(4, 8)).Copy Sheets("Nick Dyson").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        End If

    End With
    Sheets("Summary").Select
End Sub

 

[Animalised]

Hi Mark,

This runs perfectly, I have made an edit that moves the cell in which the information is being pasted into.

Thank you very much for the help and both for explaining how to make it work and why it was not working. It is beginning to make some more sense.

Good night!

 

[MARK858]

I have made an edit that moves the cell in which the information is being pasted into.

Please elaborate as the code I posted "pastes" into the next empty row every time the code runs with no edits.