hardcoding cell values based target cells

[drfumbles]

Hey,
I've been coming to this forum for help, so I finally made an account to ask some questions.

I'm writing a vba that will change a column in a set of tables to the values based on the last row of data for each table. The number of tables isn't constant and is generated by different macro.

So far I have:

Private Sub Hardcode_Tables(ByVal Target As Range)

If Intersect(Target, Range("G:G")) Is Nothing Then 'I don't think "G:G" is allowed
Exit Sub
else

'=============pseudo code ============
'if first column = "last string name (this value is only appears once for each table)" & cell to 2 columns to the right is a numeric nonzero value
'copy from top of continuous data down to where logical is true.
'paste values in the same spot
'advance search starting point to stop looking at cells that have been changed to values

end sub

Thanks

 

[drfumbles]

I have a group of tables with string references. I want to hard code a portion of the table, if the last row (138) has a number in column G and the string in column E matches. At that point, I need to change a block of the table to their values. On the first table range would be B5:G138.

 

[drfumbles]

   Private Sub Hardcode_search() 
        Const csLockText As String = "String Name"
        Dim rCell As Range
        Dim rLookIn As Range


        With Worksheets("Sheet Name")
            Set rLookIn = Intersect(.Range("E:E"), .UsedRange)
            If Not rLookIn Is Nothing Then
                For Each rCell In rLookIn
                    With rCell
                        If .Text = csLockText And IsNumeric(rCell.Offset(0, 2).Value) Then hardcode (rCell)
                        
                        
                                                                 
                    End With
                Next rCell
            End If
        End With
     End Sub


Private Sub hardcode(reference)
Set myWB = ActiveWorkbook


Range(".Index(reference, -134, -2): .Index(reference, 0, 1)").Select
Range(".Index(reference, -134, -2): .Index(reference, 0, 1)").Copy
Selection.PasteSpecial.xlPasteValues

End Sub

How can I properly resize the or reselect a range to copy and paste values in this case?

Thanks