macro to split excel work book

[message2sagar]

Hi,
Am pretty new to macros and i would need a macro to split workbook into different tabs based on unique field/column, however my problem is i have first few rows which are headers in the main tab.

my request is
1. the first few rows to be copied in all the tabs while splitting the main tab as those headers will be same for all the workbooks
2. Macro should not filter on row1 instead it should filter on row 8 and take unique based on column F

is this possible to do so. Please help

 

[DanteAmor]

Hi and welcome to MrExcel

Try this:

Sub create_worksheets()
  Dim c As Range, sh As Worksheet
  Dim ky As Variant, lr As Long
  
  Set sh = Sheets("Master")
  lr = sh.Range("F" & Rows.Count).End(3).Row
  
  With CreateObject("scripting.dictionary")
    For Each c In sh.Range("F9:F" & lr)
      .Item(c.Value) = Empty
    Next c
    For Each ky In .Keys
      sh.Range("F8").AutoFilter 1, ky
      Sheets.Add(, Sheets(Sheets.Count)).Name = ky
      sh.Rows("1:" & lr).Copy Range("A1")
    Next ky
  End With
  
  sh.Select
  sh.ShowAllData
End Sub

 

[message2sagar]

Hey, Thanks for looking into it, i just copied and pasted into new module and ran the macro.

It says Run-time error '9':
Subscript out of range

anything i should be adding to this macro ? Please adivse.

 

[DanteAmor]

If you press the "debug" button on which line of the macro it stopped.
Also if you can tell me the values that each variable contains at the time of the error?