0

Ok so it's the first time i'm tinkering with VBA, and I'm trying to create a macro that when activated takes the last entry in another sheet and adds it at the end of the table on my current sheet.

However, when I run the code, what is entered in the A cell is : =@'Table Informative'!$AI:$AI And what is entered the B cells is : =@'Table Informative'!'G35' or =@'Table Informative'!'H35'

So for the B column it does add the right cell info but adds these ' ' symbols around the cell number which fudges everything. For the A column, I'm super confused by the $AI:$AI, my code, as seen below, is very basic, and I wonder what I missed that is causing these two issues.

Sub Update()
'
' Update Macro
'

'
    Dim wsSheet1 As Worksheet: Set wsSheet1 = Worksheets("Table Informative")
    
    Dim LastA As Long
    Dim LastB As Long
    Dim LastRow As Long
    

    LastA = Cells(Rows.Count, "A").End(xlUp).Row
    LastB = Cells(Rows.Count, "B").End(xlUp).Row
    LastRow = wsSheet1.Cells(Rows.Count, "C").End(xlUp).Row
        
    Range("A" & LastA + 1).Select
    ActiveCell.FormulaR1C1 = "='Table Informative'!" & "C" & LastRow
    Range("B" & LastB + 1).Select
    ActiveCell.FormulaR1C1 = "='Table Informative'!" & "G" & LastRow
    
    Range("A" & LastA + 2).Select
    ActiveCell.FormulaR1C1 = "='Table Informative'!C" & LastRow
    Range("B" & LastB + 2).Select
    ActiveCell.FormulaR1C1 = "='Table Informative'!H" & LastRow
End Sub

Thanks in advance !

BigBen
  • 46,229
  • 7
  • 24
  • 40
Hugo Rechatin
  • 11
  • 2
  • You should be using `.Formula2`, not `.FormulaR1C1`. Or maybe `.Formula` if your version of Excel does not support Dynamic Array formulas. – BigBen Oct 18 '22 at 14:08
  • Are both worksheets in the same workbook? Is it the workbook containing this code? Does the destination worksheet (`ActiveSheet`) have a name? – VBasic2008 Oct 18 '22 at 14:20
  • 1
    @BigBen That was it ! Thanks a bunch ! That's what i get for hastily copying code off stack overflow without stopping to understand it fully. – Hugo Rechatin Oct 18 '22 at 14:22

0 Answers0