VBA - row insert in alphabetical order

Hey Guys,

I have need of some expert help now as I have been trying to modify code from others solutions and got absolutely no where for the last week.

Sheet names:
NEWSITE - this is where brand new entries are created (this is where I need the help)
UPDATE - thanks to davesexcel for previous coding
Data_Entry - this is where the data will be inserted

So the process on the NEWSITE sheet is that you fill out the sitecode/BGP AS and QOS fields - this then allows a button to pop-up (Add Record), click the add record.
the data is taken from the fields in NEWSITE and inserted into Data_Entry. At the moment this is selecting the next empty row.

I need to change this as further processing in the rest of the sheets (that arent included in this version) have issues with vlookups and the insertion at the bottom of the sheet breaks them all. I dont really want the user touching Data_Entry sheet as that's raw data, so asking the user to go in, create a new row and then copy/paste into the right row... or doing sort on column A isn't an option.

The VBA code is all done in each of the sheets so there are no modules used for reference.

TIA
Dribble

You don't necessarily have to recode.

If the addition of rows/columns is throwing formulas off, there are a few workarounds.

First, you could convert your Data_Entry to a table. Formulas that reference tables automatically adjust themselves as the tables grow in size.

Also, you could use INDIRECT to target a static range.

For example, Sum(a1:a10) throws an error if you delete rows A1:A10, but not if you used SUM(INDIRECT("A1:A10")) because the reference to A1:A10 within INDIRECT is read as text and then evaluated as a range, so it stands independantly from changes to the worksheet.

Similarly VLOOKUP(a1,INDIRECT("A1:A5000"),2,0) can define your static range. You can even take it one step further and use formulas to auto-detect the number of non-blank rows and have that be your range.