Copy data to multiple sheets when store # changes & Name sheet

[bcurrey]

Summary: I have a spreadsheet with multiple store performance metrics on it and need to break them out to separate tabs.

The table has data in columns A thru AC. The number of rows can vary.

I've sorted the table by column B (Store #).

These are the steps I can't figure out:

1. I want to copy each stores data to a new sheet (based on column B). I want the headers to be copied to each tab as well that are in row 1.
2. (this is my big hurdle) Once each sheet is created, I want the name of the sheet to become the store number

Would appreciate any guidance. I can't find any threads or instructions on doing exactly what I need. Thanks for the help!

 

[DanteAmor]

Try this, change "Data" for the name of your sheet.

Sub create_worksheets()
  Dim c As Range, sh As Worksheet, ky As Variant
  
  Application.ScreenUpdating = False
  Set sh = Sheets("Data")
  With CreateObject("scripting.dictionary")
    For Each c In sh.Range("B2", sh.Range("B" & Rows.Count).End(xlUp))
      If c.Value <> "" Then .Item(c.Value) = Empty
    Next c
    For Each ky In .Keys
      sh.Range("A1").AutoFilter 2, ky
      Sheets.Add(, Sheets(Sheets.Count)).Name = ky
      ActiveSheet.Name = ky
      sh.AutoFilter.Range.EntireRow.Copy Range("A1")
    Next ky
  End With
  sh.Select
  sh.ShowAllData
End Sub

 

[bcurrey]

That works! I appreciate it!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.