VBA code to filter, text2column and copy data

B

bombo

New Member
Joined
Nov 23, 2017
Messages
6
Hi everyone,

I need VBA code to filter, text2column, copy and paste processed data in a new sheet.

I need to return text in Question 1, question 2,... columns into number, then filter rows which in Group column is "ABC", and copy - paste these processed data in a new sheet. New sheet will include column Group, Role, Question 1, 2, 3,... (after split text). I also need a textbox to ask which group I want to filter.

My sample data is as below:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Group[/TD]
[TD]Role[/TD]
[TD]Question 1[/TD]
[TD]Open question 1[/TD]
[TD]Question 2[/TD]
[TD]Open question 2[/TD]
[TD]Question 3[/TD]
[TD]Open question 3[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]ABC[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[TD]3 - Neutral[/TD]
[TD]NA[/TD]
[TD]4 - Satisfied[/TD]
[TD]NA[/TD]
[TD]3 - Neutral[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD]XYZ[/TD]
[TD]2[/TD]
[TD]4 - Satisfied[/TD]
[TD]NA[/TD]
[TD]3 - Neutral[/TD]
[TD]NA[/TD]
[TD]4 - Satisfied[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]3[/TD]
[TD]5 - Very satisfied[/TD]
[TD]NA[/TD]
[TD]4 - Satisfied[/TD]
[TD]NA[/TD]
[TD]4 - Satisfied[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]1[/TD]
[TD]3 - Neutral[/TD]
[TD]NA[/TD]
[TD]3 - Neutral[/TD]
[TD]NA[/TD]
[TD]3 - Neutral[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD]GHI[/TD]
[TD]1[/TD]
[TD]5 - Very satisfied[/TD]
[TD]NA[/TD]
[TD]3 - Neutral[/TD]
[TD]NA[/TD]
[TD]3 - Neutral[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]2[/TD]
[TD]3 - Neutral[/TD]
[TD]NA[/TD]
[TD]3 - Neutral[/TD]
[TD]NA[/TD]
[TD]3 - Neutral[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD]XYI[/TD]
[TD]3[/TD]
[TD]5 - Very satisfied[/TD]
[TD]NA[/TD]
[TD]3 - Neutral[/TD]
[TD]NA[/TD]
[TD]3 - Neutral[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD]HIQ[/TD]
[TD]3[/TD]
[TD]4 - Satisfied[/TD]
[TD]NA[/TD]
[TD]3 - Neutral[/TD]
[TD]NA[/TD]
[TD]5 - Very satisfied[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]3[/TD]
[TD]4 - Satisfied[/TD]
[TD]NA[/TD]
[TD]3 - Neutral[/TD]
[TD]NA[/TD]
[TD]5 - Very satisfied[/TD]
[TD]NA[/TD]
[/TR]
</tbody>[/TABLE]

Please help me. Thank you!
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Hi, welcome to the board.
Does this do what you're after?
Code: 
Sub SplitColFltrCopy()
    
    Dim UsdRws As Long
    Dim Sht As Worksheet
    Dim Col As Long
    Dim Grp As String
    
    Grp = InputBox("Please enter a group")
    If Len(Grp) = 0 Then Exit Sub
    
    Set Sht = ActiveSheet
    UsdRws = Range("A" & Rows.Count).End(xlUp).Row
    
    For Col = 3 To 7 Step 2
        Columns(Col).TextToColumns Destination:=Cells(1, Col), DataType:=xlDelimited, _
           Other:=True, OtherChar:="-", FieldInfo:=Array(Array(1, 1), Array(2, 9))
    Next Col
    
    Sheets.Add(after:=Sheets(Sheets.Count)).Name = Grp
    
    If Sht.AutoFilterMode Then Sht.AutoFilterMode = False
    With Sht.Range("A1:H" & UsdRws)
        .AutoFilter field:=1, Criteria1:=Grp
        On Error Resume Next
        .SpecialCells(xlVisible).copy Sheets(Grp).Range("A1")
        On Error GoTo 0
        .AutoFilter
    End With
    
End Sub
 
Upvote 0
Hi Fluff,

Thank you for helping me!

This code works for my sample data, but it doesn't work in my real data and I don't know how to change it for suitability.

In my real data, the number or rows and columns is not only 9 rows and 8 columns like sample above. Rows and columns are different in each file while the structure is the same. (For example, there are many question and open question columns)

Could you please show me how can I change the code? I also need to convert choice "0 - Do not use services" in Question Columns into "NA" instead of "0".

While applying the code into my file, it said debug in those lines:

Columns(Col).TextToColumns Destination:=Cells(1, Col), DataType:=xlDelimited, _
 Other:=True, OtherChar:="-", FieldInfo:=Array(Array(1, 1), Array(2, 9))
Click to expand...

Thank you!
 
Upvote 0
bombo said:
This code works for my sample data, but it doesn't work in my real data and I don't know how to change it for suitability.
Click to expand...

A Generalized "Please Note"
--------------------------------------
For future questions you may ask, please do not simplify your question for us... doing so will get you a great answer to a question you do not actually have and which you do not actually care about AND it will almost always lead to you coming back for help when the solution we give you for the simplified question cannot be applied to your actual data and its layout. One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).
 
Upvote 0
@bombo
Could you please supply a sample of your real data showing both before & after results?
A varying number of rows & columns can be dealt with, but (as pointed out by Rick Rothstein) without a sample of your genuine data, there is not much we can do to help.
 
Upvote 0
@Rick Rothstein: Noted with many thanks.
@Fluff: My actual data is like table below. I have many files which have the same structure like this, the difference is the number of column and row.

I need to:

1. Convert text from Question columns into number, expect choice "0 - Do not use services" into "NA" instead of "0"
2. Filter rows which in Group column is "ABC" (open text box to ask which group needed to be filtered)
3. Copy - paste these processed data in a new sheet

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]RESPONSE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]SUBMITTED[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]USERNAME[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]GROUP[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]ROLE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Question 1[/TD]
[TD]Open question 1[/TD]
[TD]Question 2[/TD]
[TD]Open question 2[/TD]
[TD]Question n[/TD]
[TD]Open question n[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]11/10/2017[/TD]
[TD]fdgwg[/TD]
[TD]ABC[/TD]
[TD]1[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]3 - Neither Agree nor Disagree[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]NA[/TD]
[TD]5 - Strongly agree[/TD]
[TD]NA[/TD]
[TD]5 - Strongly agree[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]11/10/2017[/TD]
[TD]rgwr[/TD]
[TD]XYZ[/TD]
[TD]2[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]4 - Agree[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]NA[/TD]
[TD]3 - Neither Agree nor Disagree[/TD]
[TD]NA[/TD]
[TD]3 - Neither Agree nor Disagree[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]11/10/2017[/TD]
[TD]dgrwf[/TD]
[TD]ABC[/TD]
[TD]3[/TD]
[TD]0 - Do not use services[/TD]
[TD]NA[/TD]
[TD]3 - Neither Agree nor Disagree[/TD]
[TD]NA[/TD]
[TD]0 - Do not use services[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]11/10/2017[/TD]
[TD]dgwrg[/TD]
[TD]JIE[/TD]
[TD]3[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]5 - Strongly agree[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]NA[/TD]
[TD]0 - Do not use services[/TD]
[TD]NA[/TD]
[TD]4 - Agree[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]11/10/2017[/TD]
[TD]gwrfe[/TD]
[TD]ABC[/TD]
[TD]1[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]5 - Strongly agree[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]NA[/TD]
[TD]5 - Strongly agree[/TD]
[TD]NA[/TD]
[TD]3 - Neither Agree nor Disagree[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]11/10/2017[/TD]
[TD]wrfew[/TD]
[TD]ELO[/TD]
[TD]3[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]5 - Strongly agree[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]NA[/TD]
[TD]4 - Agree[/TD]
[TD]NA[/TD]
[TD]4 - Agree[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]11/10/2017[/TD]
[TD]wrfewd[/TD]
[TD]ABC[/TD]
[TD]2[/TD]
[TD]5 - Strongly agree[/TD]
[TD]NA[/TD]
[TD]4 - Agree[/TD]
[TD]NA[/TD]
[TD]4 - Agree[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]11/10/2017[/TD]
[TD]gwrgww[/TD]
[TD]GEW[/TD]
[TD]1[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]5 - Strongly agree[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]NA[/TD]
[TD]4 - Agree[/TD]
[TD]NA[/TD]
[TD]4 - Agree[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD]n[/TD]
[TD]11/10/2017[/TD]
[TD]rgwwds[/TD]
[TD]ABC[/TD]
[TD]1[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]5 - Strongly agree[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]NA[/TD]
[TD]4 - Agree[/TD]
[TD]NA[/TD]
[TD]4 - Agree[/TD]
[TD]NA[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Assuming that the first 5 columns are always consistent, try
Code: 
Sub SplitColFltrCopy()
    
    Dim Usdrws As Long
    Dim UsdCols As Long
    Dim Sht As Worksheet
    Dim col As Long
    Dim Grp As String
    
    Grp = InputBox("Please enter a group")
    If Len(Grp) = 0 Then Exit Sub
    
    Set Sht = ActiveSheet
    Usdrws = Range("A" & Rows.Count).End(xlUp).Row
    UsdCols = Cells(1, Columns.Count).End(xlToLeft).column

    For col = 6 To UsdCols Step 2
        Columns(col).TextToColumns Destination:=Cells(1, col), DataType:=xlDelimited, _
           Other:=True, OtherChar:="-", FieldInfo:=Array(Array(1, 1), Array(2, 9))
        Columns(col).Replace 0, "NA", xlWhole, , , False, False
    Next col
    
    Sheets.Add(after:=Sheets(Sheets.Count)).Name = Grp
    
    If Sht.AutoFilterMode Then Sht.AutoFilterMode = False
    With Sht.Range("A1", Sht.Cells(Usdrws, UsdCols))
        .AutoFilter field:=4, Criteria1:=Grp
        On Error Resume Next
        .SpecialCells(xlVisible).copy Sheets(Grp).Range("A1")
        On Error GoTo 0
        .AutoFilter
    End With
    
End Sub
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

k3yn0t3
  • Question Question
Formula to Sum Based on Criteria
2
Replies
19
Views
1K
k3yn0t3
k3yn0t3
N
  • Solved
Excel 2016 VBA - Pull data from multiple sheets to One with specificities
Replies
3
Views
427
mumps
mumps
k3yn0t3
  • Solved
INDEX(MATCH()) pulling wrong row data & Macro to duplicate sheets with formulas
Replies
4
Views
574
Alex Blakenburg
A
k3yn0t3
  • Question Question
IF() formula that checks if member data meets filtering criteria
Replies
2
Views
495
k3yn0t3
k3yn0t3
W
  • Question Question
Macro/VBA code code for creating new excel file for each pivot table from a column filter
Replies
1
Views
490
Anthony47
A

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top