3

I use this code to copy my code from another workbook to another:

Dim PayMR_Data_Columns As Long, sourcePayMR As Range

PayMR_Data_Columns = ThisWorkbook.Worksheets("Pay-MR").Cells(1, Columns.Count).End(xlToLeft).Column
Set sourcePayMR = ThisWorkbook.Worksheets("Pay-MR").Range("A2:" & Col_Letter(PayMR_Data_Columns) & Total_rows_PayMR)

'Copies value from source to Payroll Data
With Workbooks("Payroll Data.xlsm").Worksheets("Pay-MR Compiled").Range("A" & Total_rows_PayMRCompiled + 1 & ":" & Col_Letter(PayMR_Data_Columns) & Total_rows_PayMRCompiled + Total_rows_PayMR - 1)
    .Value = sourcePayMR.Value
End With

Please refer to the image below:

enter image description here

The white rows indicate that the table did not expand. On my other tables, the exact same code works and expands the table, but I do not understand why it does not expand in this case, but when I type a letter it does as shown below:

enter image description here

braX
  • 11,506
  • 5
  • 20
  • 33
Pherdindy
  • 1,168
  • 7
  • 23
  • 52

1 Answers1

3

VBA does not extend the table automatically.

You need to do add a row to the table before you write data into it:

ActiveSheet.ListObjects("Table1").ListRows.Add

Also have a look at my answer here and The VBA Guide To ListObject Excel Tables for more information how to handle tables in Excel with VBA.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • Thanks. Although i'm wondering why my other worksheets extend using the same VBA code? Prior to the code running, there are no blank table rows but they create their own rows – Pherdindy Oct 10 '18 at 09:10
  • @Pherdindy I had similar strange behaviors with automatic (not) extending tables in Excel. I won't rely on that. Adding a new row before using it would be a good idea anyway and a stable process you can rely on. – Pᴇʜ Oct 10 '18 at 09:18
  • So I actually deleted the entire table and created another one with the same headers and table name, and the code now expands it automatically like the others. Completely confused, but nice to know about table manipulation in the reference – Pherdindy Oct 10 '18 at 09:30
  • @Pherdindy I did this too. But then it stopped working again later (never found out why, guess it's just a bug). So adding a row yourself makes it secure, and you will never have to think about it again. – Pᴇʜ Oct 10 '18 at 09:33
  • @Pᴇʜ I haven't had the same problem but your advice for "Adding a new row before using it" is an example of coding for expected or possible errors. I'll be sure to use it when adding data below tables. – Mark Fitzgerald Oct 10 '18 at 10:36