Data entry starts not below Table header (Excel 2007)

[Mukhtar]

Dear Forum members,

I have a Payment sheet with Input Column and submit button, from where I submit data on the same sheet into table. The Input column are from H3 to H9 with submit button.
The data should start getting entered into Table from Row C14 onwards, but it start getting entered from somewhere C35 or C36 onwards. It does not start from below the table header. The VBA code is in Module 1. I am not so good at VBA. Need Help in correction with the VBA code.

File Attached "Payments"

Thanks in advance

Regards,
Mukhtar (India),

 

[Marc L]

Hi,​

​

your bad 'cause you have the bad idea to create an Excel table with empty rows ! Actual range : C13:I36 …​

​

Do you really need an Excel table instead of a classic range ?​

​

 

[Mukhtar]

Dear Sir,
I did not understand your answer,
I mean where have I gone wrong.
Please could you explain in detail.

Regards,
Mukhtar

 

[Marc L]

As explained it was not such a great idea to create so many rows within an Excel table.​

​

And I need an answer to my previous post …​

​

 

[Mukhtar]

Dear Marc L,

I am just a beginner in Excel, I did not have knowledge whether to use Table or Range of cells.
I just deleted the Table and created a Cell Range and the VBA code worked correctly with it, but not with Table, I did not understand why?
I had no previous knowledge about Table vs Range. I just wanted the entries to go correctly, which it did with Cell Range without altering the code. Actually I thought I had committed mistake with the VBA code. Thank you for Helping me. I still have lot to learn regarding Excel.
Thanks a lot,

Regards,
Mukhtar

 

[Marc L]

I did not understand why?

'Cause an Excel table does not need to create so many blank rows before to copy data.​

With an Excel table only a single VBA codeline is necessary to add a row filled with data …​

​

 

[Mukhtar]

Dear Marc L,

Thank you very much for solving my problem.

Regards,
Mukhtar

 

[Marc L]

Even for a range a single codeline is necessary to copy the data :​

Sub CopyPayments()
    Cells(Rows.Count, 3).End(xlUp)(2).Resize(, 7) = [TRANSPOSE(H3:H9)]
    ActiveWorkbook.Save
End Sub