creating new sheets and moving data dependant upon cell value

Hi im new here and hope someone could help me. I am looking to move rows of data to difeerernt sheets dependant on data held in column B, but i want it to create the sheets at the same time

IE
column B may have values of a,b,c,d,e,f,h etc and i want it to group all the same values together and then create a sheet with that value and move the entire rows with that data in to that sheet
the rows with A in column b goto a sheet called A........ so on so on


i hope someone can help me with this


Thanks

Steve

Hey there,

Attached is a workbook I believe accomplishes what you are looking for.

To add the code to your workbook press Alt+F8 on your keyboard and in the macro name spot provided clear the contents and type CopyData. Then Select the create option. Paste the below code between the Sub CopyData and End Sub

'declare variables
Dim Blr As String, Llr As String
Dim c As Range, d As Range

With Sheets("Sheet1")   'with worksheets Sheet1
    Blr = .Range("B6555").End(xlUp).Row 'set the variable Blr equal to the last
                                        'row that contains data in column B
    .Columns("B:B").Select  'select column B
    .Range("B1:B" & Blr).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range( _
        "L1"), Unique:=True 'autofilter column B (unique values only) to column L
                            '!!!You may need to change the L to another letter if your current data
                            '!!!expands to column L'''''
        
        Llr = .Range("L6555").End(xlUp).Row 'set the variable Llr equal to the last cell
                                            'in column L that contains data
                                            'column L is now the unique values from column B
                                            'you made need to change this if your data includes column L
            
            For Each c In .Range("L2:L" & Llr).Cells    'for each cell in column L , again you may need to change the L to your column
                Sheets.Add after:=Sheets(Sheets.Count) 'add a worksheet after the last worksheet in this workbook
                On Error Resume Next
                ActiveSheet.Name = c.Value  'name the worksheet the cell's value
                
                Sheets("Sheet1").UsedRange.AutoFilter Field:=2, Criteria1:=c    'autofilter the used range in your worksheet by the value in the cell
                
                'copy the filtered rows
                Sheets("Sheet1").UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy
                
                Sheets(c.Value).Select  'select the sheet you just added whose name is the same as the value in the cell
                Range("A1").EntireRow.Select    'select A1's entire row
                Selection.PasteSpecial  'paste the values
              
            Next c  'move to next c/cell in column L

End With
'clear the filter from the worksheet sheet1
Sheets("sheet1").Select
    ActiveSheet.ShowAllData
        ActiveSheet.UsedRange.AutoFilter

Please read through the code so that you understand what it is doing. Anything that appears in green are comments that I placed to help you understand. Some comments ask you to change some information in the code. For example, right now it unique value filters column B into column L. You'll want to change column L to another column letter if you currently have data in column L so you don't replace your data and so the code works properly. You'll need to change the column L several times in the code but the comments tell you where.

Let me know if this works for you and if you have any questions.

Thanks!

Rvasquez