route217
asked on
amend macro to make first row a set colour index
Hi Expert's
how would I amend the current macro so when each sheet is copied it retain the olour index in row 1...
range a1:t1. And colour index 33 background colour with white font txt..
how would I amend the current macro so when each sheet is copied it retain the olour index in row 1...
range a1:t1. And colour index 33 background colour with white font txt..
Sub SplitWorkbook() Dim sht As Worksheet For Each sht In ThisWorkbook.Sheets If sht.Name <> "main" Then sht.Copy With ActiveSheet.Cells .Copy .PasteSpecial Paste:=xlPasteValues .PasteSpecial Paste:=xlPasteFormats End With ActiveWorkbook.SaveAs Filename:="C:\my documents\team\mi\new\" & sht.Name & ".xls" ActiveWorkbook.Close savechanges:=False End If Next sht End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Glad to help!
BTW: I'm not sure if line 5 (sht.Copy) does anything, so you might want to try deleting it and see if the macro still works.
BTW: I'm not sure if line 5 (sht.Copy) does anything, so you might want to try deleting it and see if the macro still works.
ASKER
thanks for the feedback. ..works fine