Esta macro crea una hoja por cada centro que tengas, cuenta todos los registros que ese centro tenga y los copia a su respectiva hoja, crea una tabla temporal que se borra una vez que termina de copiar los datos, esto para evitar que la macro muestre error por que la hoja con el nombre del centro ya existe, igual que la macro anterior no importa cuantos registros tenga 1 0 1 millon o si quitas o agregas filas igual la macro trabaja con lo que este en la hoja.
Sub separar_en_hojas()
Set centros = Range("a1").CurrentRegion
With centros
filas = .Rows.Count
col = .Columns.Count
.Sort key1:=Range(.Columns(2).Address), order1:=xlAscending, Header:=xlYes
Set centros2 = .Columns(col + 3).Resize(filas, 3)
End With
With centros2
centros.Columns(2).Copy: .Columns(1).PasteSpecial
.RemoveDuplicates Columns:=1
Set centros2 = .CurrentRegion
filas = .Rows.Count - 1
rango = centros.Columns(2).Address
buscar = .Cells(2, 1).Address(0, 0)
.Cells(2, 2).Resize(filas, 1).Formula = "=countif(" & rango & "," & buscar & ")"
.Cells(2, 3).Resize(filas, 1).Formula = "=match(" & buscar & "," & rango & ",0)"
For i = 2 To filas + 1
centro = Trim(.Cells(i, 1))
veces = .Cells(i, 2)
indice = .Cells(i, 3)
Set datos = centros.Rows(indice).Resize(veces, col)
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = centro
datos.Copy: Range("a2").Resize(veces, col).PasteSpecial
centros.Rows(1).Copy: Range("a1").Resize(1, col).PasteSpecial xlPasteValues
Next i
centros2.CurrentRegion.Clear
Set datos = Nothing: Set centros = Nothing: Set centros2 = Nothing
End With
End Sub