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

  • 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

D

don lincoln

New Member
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
 
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)
 
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
 
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.
 
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?
 
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.
 
Code: 
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
 
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.
 
Code: 
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:
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.
 

Attachments

  • column drop sample data.xls
    15.5 KB · Views: 2
Give this a try
Code: 
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
 
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.
 

Attachments

  • column drop sample data.xls
    33 KB · Views: 2
You must log in or register to reply here.
Back
Top