This may be an ask too far.

What I currently have:-
A range (on sheet 1) A1:K2572,

What I would like to know/how to:-
Automatically select from each of the 59 names listed in column A (as in Filter), copy all of the information (rows) from each name and create a tab to copy the information onto. I would then end up with an additional 59 tabs containing the information taken from filtering, copying the information, creating a new tab and pasting the information onto the new tab for each of the 59 names.

Is this at all possible and if so how would I go about creating it?

Thanks you
Matt

Hi, Matt,

the following code from Hans W. Herber sorts the data on Column A and passes the data on to the sheets (all I did was change the name after copying):

Sub Matt()
Dim rng As Range, rngCur As Range
Dim lngRow As Long
Application.ScreenUpdating = False
Set rngCur = Range("A1").CurrentRegion
rngCur.Sort _
key1:=Range("A2"), _
order1:=xlAscending, _
header:=xlYes
lngRow = 2
Do Until IsEmpty(rngCur.Cells(lngRow, 1))
If rngCur.Cells(lngRow, 1) <> rngCur.Cells(lngRow - 1, 1) Then
rngCur.AutoFilter _
field:=1, _
Criteria1:=rngCur.Cells(lngRow, 1)
Set rng = rngCur.SpecialCells(xlCellTypeVisible)
Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = rngCur.Cells(lngRow, 1)
rng.Copy Range("A1")
End If
lngRow = lngRow + 1
Loop
Worksheets(1).Select
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = False
End Sub
A bit more sophisticated would be to generate a list of unique names, filter the data and copy the visible range to the sheets.

Ciao,
Holger

Thank You Holger

I have copied and pasted the code into Sheet1.

I guess its not going to be as easy as copying and pasting.

I have read the code to try and understand what and how to apply it so that it works. Unfortunately I am hard of thinking and cannot work out, where, how or what I did to do with the code to make this work.

Would you mind enlighting another mind and tell me what I need to do with the code.

Many thanks, Matt

Hi, Matt,

code goes into a module by what I know about it. Here´s a version which I put in comments (it´s not only Column A which is sorted, it should read the range sorted with Criteria in Column A):

Sub Matt()
Dim rng As Range, rngCur As Range
Dim lngRow As Long
'turn off Flicker aka ScreenUpdating
Application.ScreenUpdating = False
'Set the range to work with, here it´s the used range within the active sheet
Set rngCur = Range("A1").CurrentRegion
'sort the range in Column A
rngCur.Sort _
key1:=Range("A2"), _
order1:=xlAscending, _
header:=xlYes
'starting row is 2
lngRow = 2
'work down as long as there is data in Column A
Do Until IsEmpty(rngCur.Cells(lngRow, 1))
'compare the value in the actual line to the one above, if they differ
If rngCur.Cells(lngRow, 1) <> rngCur.Cells(lngRow - 1, 1) Then
'set the autofilter giving the criteria of the actual cell in Column A
rngCur.AutoFilter _
field:=1, _
Criteria1:=rngCur.Cells(lngRow, 1)
'set a range on all visible cells including the headers
Set rng = rngCur.SpecialCells(xlCellTypeVisible)
'add a new sheet after the one(s) already in the workbook
Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
'name the worksheet with the value of the active cell in the first sheet
ActiveSheet.Name = rngCur.Cells(lngRow, 1)
'copy the range of visible cells into the new active sheet
rng.Copy Range("A1")
End If
'add 1 to our line counter variable
lngRow = lngRow + 1
'advance in the loop
Loop
'get to the first worksheet in the workbook
Worksheets(1).Select
'get rid of the autofilter, it´s work is done by now
ActiveSheet.AutoFilterMode = False
'turn back on ScreenUpdating
Application.ScreenUpdating = True
End Sub
Ciao,
Holger

Brilliant, perfect

Thank you Holger.

If I knew how to make that an add-in I would, its a very handy code. My only other problem is that I have to now use Excel 2007 and cannot find the usual icon's on the toolbar for creating ComandButton, ComboBox etc or even how to edit one all ready created. They seem to have hidden all Macro/VBA tool box devices on Excel 2007.

Anyway, what you sent over worked perfectly.

Many Thanks, Matt

Its a long time since I did it but I remember it being simple so here is the text i used, very old book :D.

Brian

Brian

Thank you for your help, but my initial problem is now that I have to use Excel 2007 I cannot find how to get hold of the VB Editor or any of the controls that I once had in earlier edditions of Excel. Once I have that I will try the process you have uploaded.

Thank you, Matt

Hi, Matt,

ALT+F11 still gets you straight to the VBE (as well as right-clicking on a Workbook Tab/Show Code). ;)

Office, Excel Options, Popular, place a check-mark to Show Developer Tab in the Ribbon.

Ciao,
Holger

Done

I now have the means to control, just need the ability now. One step at a time.

Thank you Guys

Matt