1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find a particular column and cut it and paste in a new sheet

Discussion in 'VBA Macros' started by don lincoln, Jun 16, 2017.

  1. don lincoln

    don lincoln New Member

    Messages:
    9
    Hello

    I have a spread sheet with bunch of column almost 40 or so and some time more, and some where in that spread sheet the following columns are spread out, the heading always appears in row 1, i'm looking for a macro that will the search for following items, example LIST PRICE or EXPIRED DATE and when it find it anywhere in the sheet then cut the entire column and paste it in a new sheet. below are the 6 columns.

    Thanks.


    List Date List Price Days On Market Expired Date Status Date
    5/12/2017 2890000 33 6/14/2017 6/14/2017
    4/13/2014 2200 0 6/14/2017 6/14/2017
    5/8/2017 649900 37 6/14/2017 6/14/2017
  2. AlanSidman

    AlanSidman Active Member

    Messages:
    164
  3. don lincoln

    don lincoln New Member

    Messages:
    9
    well when i posted on excel forum NO ONE REPLIED. so i search internet and found this web site and posted here, seems like no know how to write this macro either they are incompetent or just looking here is the post status. on excel web site.

    so if it not a very long lengthy complicated macro for you then you can help me with them if not then oh well you will be the

    19 th view.

    • 0 replies | 18 view(s)
  4. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,579
    Hi ,

    When you post a question in any forum , please allow at least 24 hours to elapse before you conclude that no one is going to answer your question.

    In general , questions which require formula-based solutions will be answered faster , while questions which require VBA will take longer to be answered.

    Questions which are not accompanied by a complete workbook , or at the least a sample workbook , will take longest to get a response.

    Narayan
  5. AlanSidman

    AlanSidman Active Member

    Messages:
    164
    As Narayank991 suggested, upload a sample workbook. You have indicated that you have 40 columns of data and have shown only 6 in your example. Further confusion of this issue. Really need to see a sample workbook that is representative of your actual workbook in order to provide a viable response. Help us to help you.
  6. don lincoln

    don lincoln New Member

    Messages:
    9
    Hello

    Attached is the sample spread sheet.

    Thanks.

    Attached Files:

  7. AlanSidman

    AlanSidman Active Member

    Messages:
    164
    If I am understanding you correctly, based upon your attached spreadsheet, you want to cut the columns AJ and AK from the existing sheet and post those two columns on a new sheet. Am I understanding you correctly? And you want this done with VBA? Am I missing something here?
  8. don lincoln

    don lincoln New Member

    Messages:
    9
    When i download a new sheet every day, the LOCATION of those 2 column LIST PRICE or EXPIRED DATE changes, so they are not fixed in column AJ and AK, but one this is for sure, the column heading appears in Row1.

    So the marco should search ROW1 for LIST PRICE and EXPIRED DATE and when found in any columns that cut the entire column and paste in a new sheet side by side.

    Thanks.
  9. AlanSidman

    AlanSidman Active Member

    Messages:
    164
    Ok. Got it. Give me a few minutes.

    Alan
  10. don lincoln

    don lincoln New Member

    Messages:
    9
    Okay Great,

    Thanks.
  11. AlanSidman

    AlanSidman Active Member

    Messages:
    164
    Code (vb):

    Option Explicit
    Option Compare Text

    Sub DonLincoln()
        Dim lc As Long, i As Long
        lc = Cells(1, Columns.Count).End(xlToLeft).Column
        For i = lc To 1 Step -1
            If Cells(1, i) = "List Price" Then
                Cells(1, i).EntireColumn.Cut Sheets("Sheet2").Range("A1")
            ElseIf Cells(1, i) = "Expired Date" Then
                Cells(1, i).EntireColumn.Cut Sheets("Sheet2").Range("B1")
            End If
        Next i
    End Sub
    Thomas Kuriakose likes this.
  12. don lincoln

    don lincoln New Member

    Messages:
    9
    Hello Alan

    The macro worked great. Thanks very much. !!

    After i ran the macro i noticed in the phone number i wanted to have 1 in front of them, can you help me with it, and include 1 in front of them.

    Thanks.
  13. AlanSidman

    AlanSidman Active Member

    Messages:
    164
    Code (vb):
    Option Explicit

    Sub PlusOne()
        Dim lc As Long, i As Long, lr As Long, j As Long
        lc = Cells(1, Columns.Count).End(xlToLeft).Column
        lr = Range("A" & Rows.Count).End(xlUp).Row
        Application.ScreenUpdating = False
        For i = 1 To lc
            If InStr(1, Cells(1, i), "Phone") > 0 Then
                For j = 2 To lr
                    If Cells(j, i) <> "" Then
                        Cells(j, i) = 1 & "-" & Cells(j, i)
                    End If
                Next j
            End If
        Next i
        Application.ScreenUpdating = True
        MsgBox "completed"
    End Sub
    Last edited: Jun 17, 2017
  14. don lincoln

    don lincoln New Member

    Messages:
    9
    Hello Alan

    That worked great.

    Thanks.


    One last thing, when i dialed these numbers i have to pick up the number from my dialer program and paste it into excel and do a search on that number in order to find the record, and its just too much manual work.

    As you can see in the attached excel, column H-Q contains the phone numbers, so each record can have several number and the dialer program only recognizes numbers if they are in STACKED in ONE column not as they spread out. ( i had to bring those columns down manually one by one )

    Is there any way to bring the numbers down into each record as the RECORD DATA is in column A through G and the numbers are in H-Q.

    Attached is the spread sheet.

    Thanks.

    Attached Files:

  15. AlanSidman

    AlanSidman Active Member

    Messages:
    164
    Give this a try
    Code (vb):

    Option Explicit

    Sub transfone()
        Dim s1 As Worksheet, s3 As Worksheet
        Set s1 = Sheets("Sheet1")
        Set s3 = Sheets("Sheet3")
        Dim lr As Long, lr3 As Long
        Dim lc As Long, i As Long
        lr = s1.Range("A" & Rows.Count).End(xlUp).Row
        With s1
            .Range("A1:H1").Copy s3.Range("A1")
            For i = 2 To lr
                lr3 = s3.Range("H" & Rows.Count).End(xlUp).Row
                lc = s1.Cells(i, Columns.Count).End(xlToLeft).Column
                .Range("A" & i & ":H" & i).Copy s3.Range("A" & lr3 + 1)
                .Range(Cells(i, 9), Cells(i, lc)).Copy
                s3.Range("H" & lr3 + 2).PasteSpecial xlPasteAll, , , True
            Next i
            Application.CutCopyMode = False
        End With
        lr3 = s3.Range("H" & Rows.Count).End(xlUp).Row
        For i = 3 To lr3
            If IsNull(s3.Range("A" & i)) Then
                s3.Range("A" & i & ":G" & i) = s3.Range("A" & i - 1 & ":G" & i - 1)
            End If
        Next i
    End Sub
    NARAYANK991 likes this.
  16. don lincoln

    don lincoln New Member

    Messages:
    9
    Hello Alan

    Very nice.

    So far so good, When i ran the macro it pulled all the phone numbers into one column.

    Also if the macro can pull the corresponding record information down with it, then when i make calls i will be able to see the phone number and the information attached to each phone number like Column A to G which is

    Name Property Address Property City MLS/FSBO ID List Date List Price Days On Market

    Sheet1 Shows how the data is when i get it
    Sheet3 Shows how it should be after the numbers are dropped down into a single column.

    Thanks, very much.

    Attached Files:

  17. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,579
    Hi ,

    See if this is OK.

    Narayan

    Attached Files:

    Thomas Kuriakose likes this.
  18. AlanSidman

    AlanSidman Active Member

    Messages:
    164
    Looks like Narayank991 has fixed the code to meet your needs. Thanks Narayank991.

    Alan
  19. don lincoln

    don lincoln New Member

    Messages:
    9
    Hello Alan and Narayan

    Thanks very much, for helping me with this macro. :)

Share This Page