Option Explicit
Sub Main()
Strip_formatting
Splits_Media
Paste_as_value
Format_all
Delete_Some
End Sub
Sub Strip_formatting()
'
' Strip_formatting Macro
' strips all formatting
'
' Keyboard Shortcut: Ctrl+Shift+A
'
With Worksheets("Paste Data").Cells.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub
Sub Splits_Media()
'
' Splits_Media Macro
' Splits Media into other cells for ease of counting
'
' Keyboard Shortcut: Ctrl+Shift+B
'
Worksheets("Breakdown").Columns("B:B").TextToColumns _
Destination:=Range("B1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=True, Comma:=True, Space:=False, Other:=False, FieldInfo:= _
Array(1, 1), TrailingMinusNumbers:=True
End Sub
Sub Paste_as_value()
'
' Paste_as_value Macro
'
' Keyboard Shortcut: Ctrl+Shift+C
'
With Worksheets("Paste Data")
.Columns("B:B").Copy
.Range("D1").PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Range("D1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With
End Sub
Sub Format_all()
'
' Format_all Macro
' formats to readable
'
' Keyboard Shortcut: Ctrl+Shift+E
'
With Worksheets("Paste Data")
With .Cells
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
.Columns("A:A").EntireColumn.AutoFit
.Columns("B:B").EntireColumn.AutoFit
End With
End Sub
Sub Delete_Some()
'
' Delete_BBC Macro
' delete bbc
'
' Keyboard Shortcut: Ctrl+Shift+G
'
'don't replace with spaces
With Worksheets("Breakdown")
.Columns("B:B").Replace What:="BBC", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Cells.Replace What:="ITV", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Cells.Replace What:="STV", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Cells.Replace What:="GMB", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With
End Sub