Create new column with named dynamic range in VBA

Hi all,
I have read many posts here in the past and nearly always found an answer but now need some help please.
I 'm very new to VBA so please bear with me.....I will try and be as concise as I can with my request.
I am in the process of creating a 12 page userform to store information in worksheets to manage UAV's together with a dashboard.
I guess MS Access would probably be easier but want to be able to share this in the future with some people that don't use Access.

I have been trying for some time to work out how to use a userform to add a new column in a worksheet (Batteries) with a named dynamic range, the range name is from a textbox (Aircraft Name) in the userform and will require the spaces removed if the aircraft name has been entered with spaces, the information is to be entered with a command button.

At the same time I want to add the same Aircraft Name information from the textbox on the userform to the next blank row in another worksheet (Aircraft) with a dynamic range in Column A which already exists (Aircraft List).
I have figured out how to enter the second part with a new aircraft entered in the next blank row but cannot seem to figure out the add new column with the dynamic range bit.

Any assistance greatly appreciated.
Michael.