Help Renaming Columns then Rearranging order and adding additional columns
Help Renaming Columns then Rearranging order and adding additional columns
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Help Renaming Columns then Rearranging order and adding additional columns

  1. #1
    New Member
    Join Date
    Feb 2018
    Location
    PA
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Help Renaming Columns then Rearranging order and adding additional columns

     
    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.

  2. #2
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    9,069
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help Renaming Columns then Rearranging order and adding additional columns

    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...

    Code:
    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.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, Excel jeanie, RoryA addin (Win & Mac) or Borders-Copy-Paste

  3. #3
    New Member
    Join Date
    Feb 2018
    Location
    PA
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help Renaming Columns then Rearranging order and adding additional columns

    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!

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    6,161
    Post Thanks / Like
    Mentioned
    127 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Help Renaming Columns then Rearranging order and adding additional columns

    If you need to insert new columns, but keep the existing columns in the same order, try
    Code:
    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
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    31,211
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Help Renaming Columns then Rearranging order and adding additional columns

    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?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  6. #6
    New Member
    Join Date
    Feb 2018
    Location
    PA
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help Renaming Columns then Rearranging order and adding additional columns

    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.
    UserID CHSetIndex FirstName LastName MiddleName Street City Zip State HomePhone WorkPhone LastEventLog ExpirationDate NeverExpiers Active Deleted GotTransmitters GotCards GotEntryCodes GotPhoneEntryNumbers CustomType1 CustomType2 CustomType3 CustomType4


    Thank you again

  7. #7
    New Member
    Join Date
    Feb 2018
    Location
    PA
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help Renaming Columns then Rearranging order and adding additional columns

    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

  8. #8
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    31,211
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Help Renaming Columns then Rearranging order and adding additional columns

    Quote Originally Posted by rickadams View Post
    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.
    UserID CHSetIndex FirstName LastName MiddleName Street City Zip State HomePhone WorkPhone LastEventLog ExpirationDate NeverExpiers Active Deleted GotTransmitters GotCards GotEntryCodes GotPhoneEntryNumbers CustomType1 CustomType2 CustomType3 CustomType4
    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 .

    Code:
    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
    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.
    Last edited by Rick Rothstein; Feb 11th, 2018 at 04:21 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    6,161
    Post Thanks / Like
    Mentioned
    127 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Help Renaming Columns then Rearranging order and adding additional columns

    How about
    Code:
    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
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  10. #10
    New Member
    Join Date
    Feb 2018
    Location
    PA
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help Renaming Columns then Rearranging order and adding additional columns

      
    Quote Originally Posted by Fluff View Post
    How about
    Code:
    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.

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com