Help with VBS to rename column headings and put them it a different order and save as new sheet

[radams]

I have an Excel 2010 database with names and addresses that need different and additional headings to be exported to MS Access 2010 (more on that later) As the new file is updated I would also like to autofill some additional information. I need a VBA script to open the first excel sheet with 400 +- names with this content The list of residents looks like this Address City / State / Zip First Name Last Name Email Cell Phone Number <tbody> </tbody> 123 Apartment 3 MYTOWN, PA, 00000 Tom LSmith t.email at email (555) 555-1212 The sheet to export to Access 2010 needs to look like this and it needs to be in this exact order. UserID CHSetIndex FirstName LastName MiddleName Street City Zip State HomePhone WorkPhone LastEventLog ExpirationDate NeverExpiers Active Deleted GotTransmitters GotCards GotEntryCodes GotPhoneEntryNumbers CustomType1 CustomType2 CustomType3 CustomType4 <tbody> </tbody> UserID needs to auto number (with a starting number I can put in at the beginning of the VBA script) CHSetindex needs to be the same number IE: 3 FirstName needs to be the same as first name in the first list Lastname needs to be the same as the last name in the first list Middlename needs to be blank Street needs to be the same as Address Etc until I get to: Lasteventlog which needs to always be a numeric value of 0 NeverExpires needs to say “TRUE” for every entry. Active needs to say “TRUE” for every entry. Customtype1 needs to be the email address from the first list. I know I am asking a lot. I think I can do the rest if someone can help me get me started. This is for a door entry system and the manufacturer has not updated their packaged software for about 5, years. And it seems they are no longer supporting this system. If for some reason This is not posted correctly I apologize in advance Thank you,

<tbody>
</tbody>

 

[oldbrewer]

Address

City / State / Zip

First Name

Last Name

Email

Cell Phone Number

123 Apartment 3

MYTOWN , PA, 00000

Tom

Smith

t.email at email

(555) 555-1212

The sheet to export to Access 2010 needs to look like this and it needs to be in this exact order.

UserID

CHSetIndex

FirstName

LastName

MiddleName

Street

City

Zip

State

HomePhone

WorkPhone

LastEventLog

ExpirationDate

NeverExpiers

Active

Deleted

GotTransmitters

GotCards

GotEntryCodes

GotPhoneEntryNumbers

CustomType1

CustomType2

CustomType3

CustomType4

6

6

Tom

Smith

123 Apartment 3

MYTOWN

00000

PA

(555) 555-1212

0

TRUE

TRUE

t.email at email

UserID needs to auto number (with a starting number I can put in at the beginning of the VBA script)

CHSetindex needs to be the same number IE: 3

FirstName needs to be the same as first name in the first list

Lastname needs to be the same as the last name in the first list

Middlename needs to be blank

Street needs to be the same as Address

Etc until I get to:

Lasteventlog which needs to always be a numeric value of 0

NeverExpires needs to say “TRUE” for every entry.

Active needs to say “TRUE” for every entry.

Customtype1 needs to be the email address from the first list.

l

I used rows 5 and 6

in reality they would be in cells say K1 and onwards

town zip and state pulled automatically from B2

auto number is row number based

<colgroup><col><col><col><col><col><col><col><col><col><col><col span="2"><col><col><col span="6"><col><col><col span="4"></colgroup><tbody>
</tbody>

 

[radams]

Oldbrewer, Thank you for responding.

Was there supposed to be a VBA attached?

Thank you

 

[oldbrewer]

no - all formula driven

did you scroll to the right ?