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

Question

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

vcoolio · Answer

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
Sheet2.UsedRange.Offset(1).ClearContents

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
        
Sheet2.Columns.AutoFit
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "Data transfer completed.", vbExclamation, "STATUS"

End Sub

Following is the link to my test work book:-

https://www.dropbox.com/s/7cldr8jz6o0sx3f/TallPoppy%28Transfer%20data%2C%202%20criteria%2C%20autofilter%29.xlsm?dl=0

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.

Cheerio,
vcoolio.

vcoolio · Answer

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:- =IF(Sheet1!$B2<>"",Sheet1!B2,"") then drag it down as far as needed. Column B:- =IF(Sheet1!$B2<>"",Sheet1!C2,"") dragged down again. Column C:- =IF(Sheet1!$B2<>"",Sheet1!D2,"") dragged down. Column D:- =IF(Sheet1!$B2<>"",Sheet1!E2,"") dragged down. Column E:_ =IF(Sheet1!$B2<>"",Sheet1!J2,"") dragged down Column F:- =IF(Sheet1!$B2<>"",Sheet1!K2,"") dragged down Column G:- =IF(Sheet1!$B2<>"",Sheet1!L2,"") dragged down. Column H:- =IF(Sheet1!$B2<>"",Sheet1!AA2,"") dragged down. Column I:- =IF(Sheet1!$B2<>"",Sheet1!AB2,"") 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. Cheerio, vcoolio.

vcoolio · Answer

Hello Sarah,

You're welcome. Glad that I could help. 

Good luck!

Cheerio,
vcoolio.

Help! Need to copy only some columns in rows that have X condtn

3 responses

Similar discussions

Subscribe To Our Newsletter!