Selecting specific cells in a table using vba

[ttratl]

Selecting specific cells in a table row using vba, where user has manually selected the row.

User will select a particular row in a table, and I need to take the data from 8 cells in that row, and put them on a different sheet.

I need to use structured formatting (which I believe is using the column labels) within the vba code,
as the Table may have additional columns added in the future, and I don't want to re-adjust the cell references each time.

I have cobbled together this code from various sources, which actually works, but I think it is possibly overkill?
Is there simpler code that I should be using?

Sub Populate_JI_Sheet()

 Dim csd As Range 'Course Start Date
 Dim v As Range 'Venue
 Dim st As Range 'Start Time
 Dim d As Range 'Duration
 Dim a As Range 'Activity
 Dim e As Range 'Email
 Dim fn As Range 'First name
 Dim ln As Range 'Last name
 
    
    Set csd = Evaluate("Table1[[#This Row], [Course Start Date]]")
        Sheets("JI_Sheet").Range("C8").Value = csd
    
    Set v = Evaluate("Table1[[#This Row], [Venue]]")
        Sheets("JI_Sheet").Range("C9").Value = v
        
    Set st = Evaluate("Table1[[#This Row], [Start Time]]")
        Sheets("JI_Sheet").Range("C10").Value = st
    
    Set d = Evaluate("Table1[[#This Row], [Duration]]")
        Sheets("JI_Sheet").Range("C11").Value = d
        
    Set a = Evaluate("Table1[[#This Row], [Activity]]")
        Sheets("JI_Sheet").Range("C7").Value = a
        
    Set e = Evaluate("Table1[[#This Row], [Contact Details]]")
        Sheets("JI_Sheet").Range("J3").Value = e
        
    Set fn = Evaluate("Table1[[#This Row], [First Name]]")
    Set ln = Evaluate("Table1[[#This Row], [Last Name]]")
    
    Sheets("JI_Sheet").Range("J4").Value = fn & " " & ln

End Sub

Any pointers would be appreciated...

 

[ttratl]

Ok - so through trial and error I've found

Sheets("JI_Sheet").Range("C8").Value = Evaluate("Table1[[#This Row], [Course Start Date]]")

seems to get around the need for DIMs. Makes it simpler.

 

[strooman]

Perhaps you will be bether of with the Cells(RowIndex, ColumnIndex) property

Dim rngRow As Integer, rngColumn as integer

For rngRow = 1 To 6
    for rngColumn = 1 to 6
        Cells(rngRow, rngColumn).Value = 100
    next rngColumn
Next rngRow

 

[ttratl]

Perhaps you will be bether of with the Cells(RowIndex, ColumnIndex) property

Dim rngRow As Integer, rngColumn as integer

For rngRow = 1 To 6
    for rngColumn = 1 to 6
        Cells(rngRow, rngColumn).Value = 100
    next rngColumn
Next rngRow

Thanks Strooman. I can't quite understand how your code would help me (I'm not great on VBA).
I have to use the table column labels - but not to worry - I've got it to work for me now. Thanks again.