Automatically filter, select, create tabs and paste data

[MGumbrell]

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

 

[HaHoBe]

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

 

[MGumbrell]

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

 

[HaHoBe]

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

 

[MGumbrell]

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

 

[Brianwarnock]

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

Brian

 

[MGumbrell]

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

 

[HaHoBe]

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

 

[MGumbrell]

Done

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

Thank you Guys

Matt