Help! Need to copy only some columns in rows that have X condtn [Solved/Closed]

Ask a question TallPoppy 3Posts Friday June 10, 2016Registration date June 11, 2016 Last seen - Last answered on Jun 11, 2016 at 06:34 AM by vcoolio
OK, Full disclosure, I am a Novice Excel user at best.....I have a Google Sheet Workbook that contains Google Form Responses (the Sheet name that these go to is called Form Responses)....the Form itself is a many sectioned the information needs to be filtered. I have created separate Sheets within the Workbook to make it easier for people to get what they need for reporting. They are even less novice than me!

I have a column in my Response Form that contains a few different result options......I would like to extract, into a different sheet, information from my Response Form that contains either the words "Phone Call" and/or "Digital Interaction". For the rows that contain this result, I would like to copy over only certain Columns: They are B, C, D, E and J,K, L and AA, AB....NOT the entire Row and I do not want to have any blank rows in the new Sheet. (eg I don't want FALSE results to be included).

Whew, I am totally out of my league with this. Is there anyone out there that can help? What formula do I need to insert to make this all magically happen? Thank you good citizens of the world.......My Name is Sarah
plus moins
Hello Sarah,

I'm not sure if I follow fully, but the following code may work for you:-

Sub TransferData()

Dim lr As Long

Application.ScreenUpdating = False

        ActiveSheet.Columns("A").EntireColumn.Hidden = True
        ActiveSheet.Columns("F:I").EntireColumn.Hidden = True
        ActiveSheet.Columns("M:Z").EntireColumn.Hidden = True
lr = Range("A" & Rows.Count).End(xlUp).Row

With ActiveSheet
    .AutoFilterMode = False
With Range("B1", Range("B" & Rows.Count).End(xlUp))
        .AutoFilter 1, "Phone Call", xlOr, "Digital Interaction"
        On Error Resume Next
        Range("B2:AB" & lr).Copy
        Sheet2.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
End With
    .AutoFilterMode = False
End With

        ActiveSheet.Columns("A").EntireColumn.Hidden = False
        ActiveSheet.Columns("F:I").EntireColumn.Hidden = False
        ActiveSheet.Columns("M:Z").EntireColumn.Hidden = False
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "Data transfer completed.", vbExclamation, "STATUS"

End Sub

Following is the link to my test work book:-

Click on the "RUN" button to see it work.

The code checks Column B for the criteria ("Phone Call" or "Digital Interaction") in the Active Sheet (your main sheet) and on finding the criteria, copies the relevant data from the columns that you specified to sheet2.

Please note that the test work book is an Excel file and may not work in Google Sheets as Google Sheets do not support macros. You can convert Google Sheets to an Excel file.

I do not have any experience with Google Sheets but I hope that this points you in the right direction at least.

TallPoppy 3Posts Friday June 10, 2016Registration date June 11, 2016 Last seen - Jun 10, 2016 at 03:49 PM
Thank you vcoolio for taking the time to do this for me. This is exactly what I needed the worksheet to do. I just wish I knew how to make this happen with Google sheets. For the time being though, it will be just fine. I will still put it out to the forum to see if anyone can provide something that isn't a macro but a simple Formula I can add to the Google Sheet. Have a great day and thanks again!
plus moins
Hello Sarah,

A simple IF statement should work in Google Sheets. The following formulae are based on the sample work book in my first post. Play with the formulae in the test work book to see if its what you are after. Just don't click on the button!

In Sheet 2, starting in row 2, place the following formulae:-

Column A:-
then drag it down as far as needed.

Column B:-
dragged down again.

Column C:-
dragged down.

Column D:-
dragged down.

Column E:_

dragged down

Column F:-
dragged down

Column G:-

dragged down.

Column H:-
dragged down.

Column I:-

dragged down.

The formulae above will bring across to sheet 2 only the data from the columns that you specified in your original post.

Using formulae, you will have blank rows in sheet2 if there isn't a value in the criteria column (Column B, sheet1). Do you wish to have a temporary value in the blank rows (such as N/A) until such time as a criteria is placed in the relevant cell in Column B, sheet1?

You should get the drift of what the formulae are doing, so you should be able to change things around to suit your actual work book.

Let us know what you think.

TallPoppy 3Posts Friday June 10, 2016Registration date June 11, 2016 Last seen - Jun 11, 2016 at 06:04 AM
Excellent thank you! I will definitely use this IF formula. It does exactly what I need (apart from not including the blank results in the row)....but I can live with this. I am really grateful to you, thank you very much for all of your help!
plus moins
Hello Sarah,

You're welcome. Glad that I could help.

Good luck!


Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!