Column widths !

[Tarqs]

Can anyone add column widths to this code for me please ?

I need to eithter auto re-size or carry over the original cell width? I have 4 columns of data in A:D

Sub Explode()
    ActiveSheet.PivotTables("PivotTable1").ShowPages PageField:="Supplier"
End Sub

help much appreciated

Thanks

Tarqs

 

[AlphaFrog]

Sub Explode()
    ActiveSheet.PivotTables("PivotTable1").ShowPages PageField:="Supplier"
    Application.ScreenUpdating = False
    ActiveSheet.Columns("A").ColumnWidth = Sheets("Sheet1").Columns("A").ColumnWidth
    ActiveSheet.Columns("B").ColumnWidth = Sheets("Sheet1").Columns("B").ColumnWidth
    ActiveSheet.Columns("C").ColumnWidth = Sheets("Sheet1").Columns("C").ColumnWidth
    ActiveSheet.Columns("D").ColumnWidth = Sheets("Sheet1").Columns("D").ColumnWidth
    Application.ScreenUpdating = True
End Sub

 

[Tarqs]

AlphaFrog, thanks for this but it works on the first newly created sheet?

Tarqs

 

[AlphaFrog]

AlphaFrog, thanks for this but it works on the first newly created sheet?

I don't understand what you are asking here.

 

[Tarqs]

Sorry, typing with thumbs today!

The code only applies the column widths to the first of newly created sheets and not all sheets that the show pages function creates.

eg column widths applied to 1 out of the 10 "pages" created.

Tarqs

 

[Tarqs]

Thought i'd provide an update as i got this answered via another thread (Can I lock every sheet) thanks to MrKowz! Slightly different solution as it is autofit and not actual widths but it works for my needs.

Sub ExplodeTotal()
Dim shtcnt  As Long, _
    i       As Long
 
shtcnt = Sheets.Count
Application.ScreenUpdating = False
ActiveSheet.PivotTables("PivotTable1").ShowPages PageField:="Supplier"
For i = shtcnt To Sheets.Count
    Sheets(i).Cells.EntireColumn.AutoFit
    Sheets(i).Protect Password:="abc123"
Next i
Application.ScreenUpdating = True
End Sub

Tarqs