Splitting Sheets into multiple seperate sheets

[robberpan]

Hi ,

I am trying to split the sheets into multiple files and am facing two issues,
1) While splitting the sheets the formulas in the sheet disappear and only values are copies over.
2) I want to keep the formatting of the sheet same. example column width the same as the original file.
Any help will be appreciated.
Code as below

Sub Split_Data_in_workbooks()

Application.ScreenUpdating = False


Dim data_sh As Worksheet
Set data_sh = ThisWorkbook.Sheets("NS EXCLUDING ALT")

Dim setting_Sh As Worksheet
Set setting_Sh = ThisWorkbook.Sheets("Settings")

Dim nwb As Workbook
Dim nsh As Worksheet

''''' Get unique supervisors

setting_Sh.Range("A:A").Clear
data_sh.AutoFilterMode = False
data_sh.Range("I13:I9999").Copy setting_Sh.Range("A1")

setting_Sh.Range("A:A").RemoveDuplicates 1, xlYes

Dim i As Integer

For i = 2 To Application.CountA(setting_Sh.Range("A:A"))

data_sh.Range("A12:BL9999").AutoFilter 9, setting_Sh.Range("A" & i).Value


Set nwb = Workbooks.Add
Set nsh = nwb.Sheets(1)


data_sh.UsedRange.SpecialCells(xlCellTypeVisible).Copy nsh.Range("A1")
data_sh.Range("A1:BL11").Copy nsh.Range("A1")
data_sh.Range("A12:BL9999").Copy nsh.Range("A12")
nsh.UsedRange.EntireColumn.ColumnWidth = 30
ActiveWindow.Zoom = 60
Cells.Select
Cells.EntireColumn.AutoFit
Cells.EntireRow.AutoFit
Range("A1").Select
nwb.SaveAs setting_Sh.Range("K6").Value & "/" & setting_Sh.Range("A" & i).Value & ".xlsx"
nwb.Close False
data_sh.AutoFilterMode = False
Next i

setting_Sh.Range("A:A").Clear

MsgBox "Done"


End Sub

 

[JLGWhiz]

If you want the column widths the same as the source sheet then

For i = 1 to Range("BL1").Column
    nsh.Columns(i).ColumnWidth = data_sh.Columns(i).ColumnWidth
Next

I am not sure about the formula problem. Since you are filtering before copying, they probably would be no good because cell references would not automatically adjust. You will likely just have to redo all the formulas, but you should be able to copy the formulas and paste special into the appropriage ranges as a separate action.

 

[robberpan]

Hi Whiz,

Thanks for your response.

 

[JLGWhiz]

Hi Whiz,

Thanks for your response.

You're welcome,
regards, JLG