'*********************************************************************
'************************ copyAndClean *******************************
'*********************************************************************

'   name            = 'copyAndClean'
'   version         = '2.0'
'   license         = Free for all
'   Summary         = "Cleans the mess"
'   Description     = "Script cleans formating and styles next copy
'                       all to new worksheet with the name CleanSheet1"
'   author          = Jacek Jarecki
'   email           = 'awariat@gmail.com'
'   homepage        = 'yoolek.com'
'   tested on       = 'vba 7.1,excell 2013'

'*********************************************************************
'*********************************************************************
'*********************************************************************


Sub copyAndClean()

    'Turn off screen updates to improve performance
    Application.ScreenUpdating = False
    
    'copy content of worksheet 1
    ActiveSheet.UsedRange.Select
    Selection.Copy
    
    'create new worksheet and paste content
    Worksheets.Add().Name = "CleanSheet1"
    Worksheets("CleanSheet1").Select
    ActiveSheet.Paste
    
    'Clear formatting in new worksheet
    With ActiveSheet.Cells
    
            .ClearFormats
            .FormatConditions.Delete
            .Interior.ColorIndex = xlColorIndexNone
            .Interior.ColorIndex = xlNone
            .Font.Name = Application.StandardFont
            .Font.Size = Application.StandardFontSize
            .EntireColumn.AutoFit
            
    End With
    
    'Clear styles in new worksheet
    ActiveSheet.ListObjects(1).TableStyle = ""
    
    'Restore screen updates to display changes
    Application.ScreenUpdating = True
        
End Sub