Help Renaming Columns then Rearranging order and adding additional columns

[rickadams]

This is a Great site, Thank you! To start with I am working with Microsoft Excel 2010 Pro

I am new at this, however I have used a few VBA scripts in excel 2010

We have multiple door entry systems that I have been graced with setting up by May.
The data is required needs to be in a different order and requires additional columns, so I am attempting to start this project now.

I would like to have a script to change what I am calling the column title data saved in A1,B1,Etc all the way up to F1 without goofing up the rest of the data in those columns.

A1 has the value of "Lease Agreement - Unit - Address" However it needs to only show "Street"
B1 has the value of "Unit City / State / Zip" It needs to only show "City"
C1 has the value of "Student First Name" It needs to only show "First Name"
D1 has the value of "Student - Last Name" It needs to only show " Last Name"
E1 has the value of "Email" It needs to show as "Custom Type 1"
F1 has the value of "Cell Phone Number" It needs to show "HomePhone"

I am looking for a script to easily rename all of the titles across the top.

Then I need to Move the order around and Add New Columns with additional Titles up to X1
So If there is a way to Add Columns at the same time as the rename and move the columns too that would be great...

I think if someone could get me started in the right direction, I might be able to continue the rest on my own.

Then the new A1 would be come"UserID" And the data that was in A1 needs to go to C1, along with additional changes.

I am looking for a script because the data in these 8 entry systems has to be changed all the time and there can be up to 400 tenants per system.
Also the spreadsheet that I download from the web page only allows .CSV downloads, can it be renamed and saved as .XLSX using the sane script?
To top it all off I will need to export these modified sheets as Ms Access 2003 .MDB format because that is what the entry systems understand. Uggh

Thank you In Advance.

 

[MARK858]

I'll get you started as it is the easy bit (and the part where I understand what you are asking). Re-naming the titles is just...

Range("A1:F1") = Array("Street", "City", "First Name", "Last Name", "Custom Type 1", "HomePhone")

Please note that I have removed the space in front of Last Name so put it back if needed.

 

[rickadams]

Mark858 Thank you so much for a fast response and a great starting Solution for me!!
I changed it a bit because the MSAccess table i have to export to can't have spaces in some of the names.

Thank you again!

 

[Fluff]

If you need to insert new columns, but keep the existing columns in the same order, try

Sub RearrangeData()

   Dim Hdr1 As Variant
   Dim Hdr2 As Variant
   Dim Cnt As Long
   Dim NewC As Long
   Dim Col As Long
   
   Hdr1 = Array("Street", "City", "First Name", "Last Name", "Custom Type 1", "HomePhone")
   Hdr2 = Array("GridRef", "District", "Street", "County", "City", "First Name", "Something", "Last Name", "Custom Type 1", "Anything", "HomePhone")
   Range("A1:F1") = Hdr1
   Col = 1
   For Cnt = 0 To UBound(Hdr1)
      NewC = Application.Match(Hdr1(Cnt), Hdr2, 0)
      If NewC > Col Then
         Columns(Col).Resize(, NewC - Col).Insert
         Col = NewC + 1
      Else
         Col = Col + 1
      End If
   Next Cnt
   Range("A1:X1") = Hdr2
End Sub

Where Hdr1 is the A1:F1 headers & Hdr2 is the new headers for A1:X1

 

[Rick Rothstein]

Do you already have data in Columns A:E or are we talking only about the headers with not underlying data?

What is going on with the added columns... are you just adding their headers for them in Row 1 or do they have data under them?

 

[rickadams]

Fluff, Thank you for responding so quickly...

I think that will work however I do need to rearrange some of the original columns to the following format.
[TABLE="width: 2354"]
<tbody>[TR]
[TD="class: xl63, width: 98"]UserID[/TD]
[TD="class: xl63, width: 98"]CHSetIndex[/TD]
[TD="class: xl63, width: 98"]FirstName[/TD]
[TD="class: xl63, width: 98"]LastName[/TD]
[TD="class: xl63, width: 98"]MiddleName[/TD]
[TD="class: xl63, width: 98"]Street[/TD]
[TD="class: xl63, width: 79"]City[/TD]
[TD="class: xl63, width: 98"]Zip[/TD]
[TD="class: xl63, width: 98"]State[/TD]
[TD="class: xl63, width: 98"]HomePhone[/TD]
[TD="class: xl63, width: 98"]WorkPhone[/TD]
[TD="class: xl63, width: 98"]LastEventLog[/TD]
[TD="class: xl63, width: 119"]ExpirationDate[/TD]
[TD="class: xl63, width: 98"]NeverExpiers[/TD]
[TD="class: xl63, width: 98"]Active[/TD]
[TD="class: xl63, width: 98"]Deleted[/TD]
[TD="class: xl63, width: 98"]GotTransmitters[/TD]
[TD="class: xl63, width: 98"]GotCards[/TD]
[TD="class: xl63, width: 98"]GotEntryCodes[/TD]
[TD="class: xl63, width: 98"]GotPhoneEntryNumbers[/TD]
[TD="class: xl63, width: 98"]CustomType1[/TD]
[TD="class: xl63, width: 98"]CustomType2[/TD]
[TD="class: xl63, width: 98"]CustomType3[/TD]
[TD="class: xl63, width: 98"]CustomType4[/TD]
[/TR]
</tbody>[/TABLE]


Thank you again

 

[rickadams]

Rick, Thank you for responding.. There is Underlying data in the original A:E Columns. The rest will be either manually added or added to the columns with a VBA (I did post the correct new order I need for exporting) The data required in most of the new columns will always be the same. IE: LastEventLog will always be a 0 for every entry.
NeverExpires will always be "TRUE"
The data in Columns O Thru T Will always be "FALSE"
How do I add that data to the Rows that had a Tenant name in the original database?
I think I need to "Dim" something based on the lastrow name Column being occupied in the D Column without going past the end.

I hope I am making sense

 

[Rick Rothstein]

Fluff, Thank you for responding so quickly...

I think that will work however I do need to rearrange some of the original columns to the following format.
[TABLE="width: 2354"]
<tbody>[TR]
[TD="class: xl63, width: 98"]UserID[/TD]
[TD="class: xl63, width: 98"]CHSetIndex[/TD]
[TD="class: xl63, width: 98"]FirstName[/TD]
[TD="class: xl63, width: 98"]LastName[/TD]
[TD="class: xl63, width: 98"]MiddleName[/TD]
[TD="class: xl63, width: 98"]Street[/TD]
[TD="class: xl63, width: 79"]City[/TD]
[TD="class: xl63, width: 98"]Zip[/TD]
[TD="class: xl63, width: 98"]State[/TD]
[TD="class: xl63, width: 98"]HomePhone[/TD]
[TD="class: xl63, width: 98"]WorkPhone[/TD]
[TD="class: xl63, width: 98"]LastEventLog[/TD]
[TD="class: xl63, width: 119"]ExpirationDate[/TD]
[TD="class: xl63, width: 98"]NeverExpiers[/TD]
[TD="class: xl63, width: 98"]Active[/TD]
[TD="class: xl63, width: 98"]Deleted[/TD]
[TD="class: xl63, width: 98"]GotTransmitters[/TD]
[TD="class: xl63, width: 98"]GotCards[/TD]
[TD="class: xl63, width: 98"]GotEntryCodes[/TD]
[TD="class: xl63, width: 98"]GotPhoneEntryNumbers[/TD]
[TD="class: xl63, width: 98"]CustomType1[/TD]
[TD="class: xl63, width: 98"]CustomType2[/TD]
[TD="class: xl63, width: 98"]CustomType3[/TD]
[TD="class: xl63, width: 98"]CustomType4[/TD]
[/TR]
</tbody>[/TABLE]

You did not answer my questions, so I'll assume the following...

1) You have data in at least the first six columns

2) Your current layout, in this exact order, is Street, City, FirstName, LastName, CustomType1, HomePhone, UserID, CHSetIndex, MiddleName, Zip, State, WorkPhone, LastEventLog, ExpirationDate, NeverExspires, Active, Deleted GotTransmitters, GotCards, GotEntryCodes, GotPhoneEntryNumbers, CustomType2, CustomType3, CustomType4.

3) The final order is to be as posted in Message #6 .

[table="width: 500"]
[tr]
	[td]Sub RearrangeColumns()
  Dim X As Long, LastRow As Long, Letters As Variant, NewLetters As Variant
  Const NewOrder As String = "G,H,C,D,I,A,B,J,K,F,L,M,N,O,P,Q,R,S,T,U,E,V,W,X"
  LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
  Letters = Split(NewOrder, ",")
  ReDim NewLetters(1 To UBound(Letters) + 1)
  For X = 0 To UBound(Letters)
    NewLetters(X + 1) = Columns(Letters(X)).Column
  Next
  Range("A1").Resize(LastRow, UBound(Letters) + 1) = Application.Index(Cells, Evaluate("ROW(1:" & LastRow & ")"), NewLetters)
End Sub[/td]
[/tr]
[/table]

Edit Note: I see you responded while I was developing my post. No matter, the key then is my assumption #2 ... the order of the added columns must be know in advance and they are as I assumed above. If that assumption is incorrect, then tell me the correct current order and I'll modify my code to match it.

 

[Fluff]

How about

Sub RearrangeData()

   Dim Hdr1 As Variant
   Dim Hdr2 As Variant
   Dim Cnt As Long
   Dim NewC As Long
   
   Hdr1 = Array("Street", "City", "FirstName", "LastName", "CustomType1", "HomePhone")
   Hdr2 = Array("UserID", "CHSetIndex", "FirstName", "LastName", "MiddleName", "Street", "City", "Zip", "State", "HomePhone", "WorkPhone", ",LastEventLog", "ExpirationDate", "NeverExpiers", "Active", "Deleted", "GotTransmitters", "GotCards", "GotEntryCodes", "GotPhoneEntryNumbers", "CustomType1", "CustomType2", "CustomType3", "CustomType4")
   Range("A1:F1") = Hdr1
   
   For Cnt = UBound(Hdr1) To 0 Step -1
      NewC = Application.Match(Hdr1(Cnt), Hdr2, 0)
      If NewC <> Cnt + 1 Then
         Columns(Cnt + 1).Copy Columns(NewC)
         Columns(Cnt + 1).Clear
         Application.CutCopyMode = False
      End If
   Next Cnt
   
   Range("A1:X1") = Hdr2
End Sub

 

[rickadams]

How about

Sub RearrangeData()

   Dim Hdr1 As Variant
   Dim Hdr2 As Variant
   Dim Cnt As Long
   Dim NewC As Long
   
   Hdr1 = Array("Street", "City", "FirstName", "LastName", "CustomType1", "HomePhone")
   Hdr2 = Array("UserID", "CHSetIndex", "FirstName", "LastName", "MiddleName", "Street", "City", "Zip", "State", "HomePhone", "WorkPhone", ",LastEventLog", "ExpirationDate", "NeverExpiers", "Active", "Deleted", "GotTransmitters", "GotCards", "GotEntryCodes", "GotPhoneEntryNumbers", "CustomType1", "CustomType2", "CustomType3", "CustomType4")
   Range("A1:F1") = Hdr1
   
   For Cnt = UBound(Hdr1) To 0 Step -1
      NewC = Application.Match(Hdr1(Cnt), Hdr2, 0)
      If NewC <> Cnt + 1 Then
         Columns(Cnt + 1).Copy Columns(NewC)
         Columns(Cnt + 1).Clear
         Application.CutCopyMode = False
      End If
   Next Cnt
   
   Range("A1:X1") = Hdr2
End Sub

The original imported data is in my original post.

With the help of you and Fluff I was able to rename those original headings (Perfect) Thank you both.

Now the only other thing is to add the headers as UserID, Then ChSetIndex, Etc as shown in the quote you copied from Fluff above.

I ran the code from Post 8 and That seemed to move my original data around perfectly leaving the required empty Columns.
So after my data has been placed in the correct order from the code you sent in Post 8 then I need a script to add the new added blank Headings to the top.


I feel bad that I do not know how else to answer.