Looking to paste various cells into relevant table

[Falcons88]

Hi,

I managed to get some help with a code a few weeks back from someone who was very helpful indeed and the VBA he gave me worked a treat, my work book has since progressed and I'm needing the below code to do exactly what its been doing but pasting the highlighted cells in the order shown, I thought by changing what was originally in the code ("A6:D6") to what's in now would work but it hasn't:

Dim ary As Variant, n As Variant

'Names of accounts from 1 to 19
ary = Array("Fixed Assets", "Long Term & Current Assets", "Liabilities", "Income", "Trading Expenses", "Selling & Distribution Expenses", _
"Financial Expenses", "Premises Expenses", "Administration Expenses", "General Expenses")

n = Application.Match(Sheets("Input Sheet").Range("E6").Value, ary, 0)
If Not IsError Then
With Sheets("Chart of Accounts").ListObjects("Table" & n).ListRows.Add.Range 'add a new row and get its Range
.Cells(1).Resize(1, 4).Value = Sheets("Input Sheet").Range("A6,B6,F6,D6,J6").Value
End With

 

[NoSparks]

I have Excel 2010, which doesn't have the TEXTJOIN function, so I can't actually test this but maybe
build a string using the worksheet TEXTJOIN function
then SPLIT the string into an array for the new table row.

Something like this...

If Not IsError(n) Then
    
    With Sheets("Input Sheet")
        Str = Application.textjoin(",", True, A6, B6, F6, D6, J6)
    End With
    
    With Sheets("Chart of Accounts").ListObjects("Table" & n).ListRows.Add 'add a new row
        .Cells(1).Resize(1, 5).Value = Split(Str, ",")
    End With
    
End If

 

[Falcons88]

I have Excel 2010, which doesn't have the TEXTJOIN function, so I can't actually test this but maybe
build a string using the worksheet TEXTJOIN function
then SPLIT the string into an array for the new table row.

Something like this...

If Not IsError(n) Then
   
    With Sheets("Input Sheet")
        Str = Application.textjoin(",", True, A6, B6, F6, D6, J6)
    End With
   
    With Sheets("Chart of Accounts").ListObjects("Table" & n).ListRows.Add 'add a new row
        .Cells(1).Resize(1, 5).Value = Split(Str, ",")
    End With
   
End If

Thanks for replying @NoSparks , unfortunately it isn't working, as to why is beyond my understanding, I did read the textjoin article and I understand how it works but don't see why it doesn't like the code. Below is a screen shot of what it's saying. I understand how certain code works but its how to combine it all together I struggle to grasp.

 

[NoSparks]

In sorting this out I've discovered str is a VBA key word so shouldn't be used as a variable name.
I haven't had any issues with that before, likely because I use Option Explicit and declare all variables.
Try

Sub testing()

Dim ary As Variant, n As Variant
Dim sStr As String

'Names of accounts from 1 to 19
ary = Array("What", "Ever", "Your", "Headers", "Are")   '<--- change to actual headers

n = Application.Match(Sheets("Input Sheet").Range("E6").Value, ary, 0)
If Not IsError(n) Then
    
    With Sheets("Input Sheet")
        sStr = Application.textjoin(",", True, A6, B6, F6, D6, J6)
    End With
    
    With Sheets("Chart of Accounts").ListObjects("Table" & n).ListRows.Add 'add a new row
        .Cells(1).Resize(1, 5).Value = Split(sStr, ",")
    End With
    
End If
End Sub

 

[Fluff]

Another option

With Sheets("Chart of Accounts").ListObjects("Table" & n).ListRows.Add.Range 'add a new row and get its Range
.Cells(1).Resize(1, 5).Value = Application.Index(Sheets("Input Sheet").Range("A6:J6").Value, 1, Array(1, 2, 6, 4, 10))
End With

 

[Falcons88]

Thank you again for taking your time to reply @NoSparks unfortunately I'm getting a different error this time:

 

[Falcons88]

Another option

With Sheets("Chart of Accounts").ListObjects("Table" & n).ListRows.Add.Range 'add a new row and get its Range
.Cells(1).Resize(1, 5).Value = Application.Index(Sheets("Input Sheet").Range("A6:J6").Value, 1, Array(1, 2, 6, 4, 10))
End With

Works an absolute treat, thank you very much for this @Fluff , you've put me in good spirits to see Lions take apart Japan tomorrow?

 

[Fluff]

Glad we could help & thanks for the feedback.