Pull data from several column's to a new page if column a matches set criteria

[benmoore89]

Hi there,

I have got a table with over 20 columns and hundreds of rows of data. hear is a small sample

TMName	DatePosted	User	User ID	PCROle	DTDestination	SKDestination	BSDestination	RepRole	RepName	CI1	CI2	CI3	CI4	CI5	CI6	CI7	CBCI1	CBCI2	CBCSQ3	CBComplaint4	CBSales5	Percentage %
Marta	08/01/2015 17:03	sz.marta87	172	Head Rep		DomRep&JamaicaPUJ		Rep	Katarzyna	5	5	5	5	5	5	5	5	5	10	30	15	100%
Hamid	08/02/2015 10:53	hamidwin	#N/A	TM		KOSKGS		Rep	Andre	5	5	5	5	5	5	5	5	5	10	30	15	100%
Stefan	08/02/2015 15:36	stefan.minimayr	164	TM	LanzaroteACE			Rep	Sander	5	5	5	5	5	5	5	5	5	0	30	15	90%
Felicity	08/03/2015 12:26	felicitysparkle	40	TM	Ibiza			Rep	Kelsey	5	5	5	5	5	5	5	5	5	10	30	15	100%
Felicity	08/03/2015 12:29	felicitysparkle	40	TM	Ibiza			Rep	Natalie	5	5	5	5	5	0	5	5	5	10	30	15	95%
Joanne	08/03/2015 16:22	joandjar	67	TM		MexicoCUN		Rep	Gillian	5	5	5	5	0	0	5	5	5	10	30	15	90%
Joanne	08/03/2015 16:25	joandjar	67	TM		MexicoCUN		Rep	Emily	5	5	5	5	5	5	5	5	5	10	30	15	100%
Jackie	08/04/2015 12:36	wroey76	195	TM	FuerteventuraFUE			Rep	Chloe	5	5	5	5	5	0	0	5	5	10	30	15	90%
Jackie	08/04/2015 12:36	wroey76	195	TM	FuerteventuraFUE			Rep	Chloe	5	5	5	5	5	5	5	5	5	10	30	15	100%

<colgroup><col><col><col><col><col><col><col><col><col><col><col span="12"><col></colgroup><tbody>
</tbody>

Column A consists of the users name, this is what i want to use as my criteria. So if column a had the name 'Felicity' i want to pull all the rows matching that name in column 1 into a separate sheet called 'felicity' along with the data in the other 20 or so columns

If you need me to elaborate more then i can

Thanks in advance

 

[excelginge]

Hi benmoore89,

Have done a little code for you.

Put the following code in the sheet that contains the original table

Then right click any value in column A and a new sheet will be created as requested.

Enjoy

ExcelGringe




Option Explicit


Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim strName As String
Dim lngMaxRow As Long
Dim lngMaxCol As Long
Dim i As Long
Dim a As Long
Dim ii As Long
Dim wsT As Excel.Worksheet
Dim wsC As Excel.Worksheet

If Target.Column = "1" And Target.Row > 1 Then
strName = Target.Value
lngMaxRow = ActiveSheet.Range("A65536").End(xlUp).Row
lngMaxCol = ActiveSheet.Range("IX1").End(xlToLeft).Column


Application.DisplayAlerts = False
On Error Resume Next
Worksheets(strName).Delete
On Error GoTo 0
Application.DisplayAlerts = True
Set wsC = ActiveWorkbook.ActiveSheet
Set wsT = Worksheets.Add(After:=ActiveSheet)
wsT.Name = strName
ii = 1

For i = 1 To lngMaxRow
If i = 1 Or strName = wsC.Range("A" & i).Value Then
For a = 1 To lngMaxCol
wsT.Cells(ii, a).Value = wsC.Cells(i, a).Value
Next a
ii = ii + 1
End If
Next i

wsT.Cells.EntireColumn.AutoFit

Cancel = True
End If
End Sub

 

[benmoore89]

wow that's some formula thanks, only prob i have now is when i copy and paste it into excel it just displays as text on the page and does not hide as a formula and when i right click there is no option to send data to another page. im not very excel literate (knowledge limited to sum's countif and subtotal) is there a spesific way im supposed to enter the formula above ???

Thanks again

 

[excelginge]

Hi benmoore89,

The code I gave you is vba and should be placed in the sheets VBA code section

First of all make sure you save your file as .xlsm. (To keep macros)

Then with the worksheet open right click the sheet name (at the bottom) then click (View Code).

Now insert the code (make sure you only have one option explicit at the beginning)

Now press save and then the X in the top right hand corner.

Now right click in column A (Other than Row 1) and you should see the result you want. Another sheet with data will be created with a name of the Person and the data for that person.

THIS IS VBA CODE NOT A FORMULA.

If you need further help just ask.

Regards

ExcelGringe

 

[SpillerBD]

I would suggest using Pivot Table using the Page (Filter) field and the Pivot Table Option: Show Report Filter Pages.
Since the Pivot Table is a report based on (and linked to) the original data, it is not duplicated data.
The Pivot Table Option: Show Report Filter Pages, creates an additional tab for each filter value with that filter values name if useable as a sheet name.
This accomplishes the same goal as the above VBA without VBA.
The Pivot Table would be with all fields, no subtotals or grand totals and in Tabular form.

I would also examine the TABLE method with Slicers to avoid the multiple sheets all together. (Version 2013, see https://support.office.com/en-us/article/Filter-data-in-an-Excel-table-7D8E9739-2898-4BFE-9D0F-C6204E6E5C8A