VBA: Copy > Look > Paste

[mhroberts]

As a bit of a background, let me tell you I'm a Google-programmer. That is not to say I work for Google, but instead that I use Google as my manual for programming. So, in other words, please be gentle!

Now, here's my problem, and I feel like I've done a LOT of searching.

I have 3 values on a sheet I want to copy and paste into a table on another sheet, but the row is a variable based off a row-header, which can be identified based on yet another cell.

Let me walk you through what I'm trying to do:

1. On Sheet "Post", copy cell H11 as 'gNew', copy cell H12 as 'rNew', and copy cell H13 as 'nNew'
2. On Sheet "Post", look at cell C3
3. On Sheet "Roster", find the row that has a match in Column "A" to "Post!C3"
4. On Sheet "Roster", paste 'gNew' into that found row, Column "I"
5. On Sheet "Roster", paste 'rNew' into that found row, Column "J"
6. On Sheet "Roster", paste 'nNew' into that found row, Column "K"

End.

Thanks ahead of time for your awesome time and help!
[/LIST]

 

[Joe4]

I think this will do what you want:

Sub MyCopyMacro()
'
    Dim myValue As String
    
'   Copy value from Post sheet
    myValue = Sheets("Post").Range("C3")
    
'   Find value on Roster sheet
    Sheets("Roster").Activate
    On Error GoTo err_Check
    Cells.Find(What:=myValue, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    On Error GoTo 0
 
'   Paste values
    Cells(ActiveCell.Row, "I").Value = Sheets("Post").Range("H11")
    Cells(ActiveCell.Row, "J").Value = Sheets("Post").Range("H12")
    Cells(ActiveCell.Row, "K").Value = Sheets("Post").Range("H13")
    Exit Sub
    
'   Error checking to handle if value is not found
err_Check:
    If Err.Number = 91 Then
        MsgBox "The value of " & Chr(34) & myValue & Chr(34) & " is not found on the Roster sheet"
    Else
        MsgBox Err.Number & ":" & Err.Description
    End If
 
End Sub

 

[mhroberts]

This seems like it should work, but I'm getting an error that says it can't find the value on the Roster sheet, even though that's where the original value is pulling from.

 

[Joe4]

Are the values EXACTLY the same, no extra spaces at the end of the word or anything like that?

 

[mhroberts]

Yes, exactly the same. A Validation List is built using a Dynamic Named Range, that list is available to pick from, built from the Roster sheet, placed on the sheet "Import" and then Cell C3 on "Post" is an IFERROR statement which looks at that cell on "Import."

I changed your code to refer directly to the cell on "Import", thinking the IFERROR statement may be tripping it up, but I get the exact same error.

 

[Joe4]

Can you post the code, exactly as you have it now, with the modifications you made?

 

[mhroberts]

'
    Dim myValue As String
    
'   Copy value from Post sheet
    myValue = Sheets("Import").Range("B3")
    
'   Find value on Roster sheet
    Sheets("Roster").Activate
    On Error GoTo err_Check
    Cells.Find(What:=myValue, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    On Error GoTo 0
 
'   Paste values
    Cells(ActiveCell.Row, "I").Value = Sheets("Post").Range("H11")
    Cells(ActiveCell.Row, "J").Value = Sheets("Post").Range("H12")
    Cells(ActiveCell.Row, "K").Value = Sheets("Post").Range("H13")
    Exit Sub
    
'   Error checking to handle if value is not found
err_Check:
    If Err.Number = 91 Then
        MsgBox "The value of " & Chr(34) & myValue & Chr(34) & " is not found on the Roster sheet"
    Else
        MsgBox Err.Number & ":" & Err.Description
    End If

Pretty much exactly the code as you had it.

 

[Joe4]

Note that you code now has references to three different sheets: "Post", "Roster", and "Import". Are you sure all the references are correct for the task at hand?

Right now, it is capturing the value of cell B3 on the "Import" sheet, and then looking for it on the "Roster" sheet.

If it finds it, it is copying the values from cells H11, H12, and H13 from the "Post" sheet into columns I,J, and K on the "Roster" sheet.

Is that your intention?

 

[mhroberts]

Yes. That's exactly correct.

If it helps, the cell it should be looking FOR is within a named range called "Table_Members" but keep in mind it's a Dynamically named range. My previous attempts at this were fruitless partly because VBA didn't like named ranges with formulas or something.

 

[Joe4]

OK, let's check a simple example here.

What value is currently showing in cell B3 on the "Import" sheet?
What cell does that currently match on the "Roster" sheet?