Hi Guys
*
I have a recorded macro that actually works quite well however I would like to clean the code up and make it run faster / Smoother / more efficiently..
*
It's based around the Paste special function and copies from some listed cells to None listed cells throughout the Workbook..
*
the code is below and any help is appreciated!!
*
Thanks
*
James
*
Sub Add_data()
'
' Add_data Macro
' Macro recorded 16/01/2013 by James '
*
'
*** Sheets("Input Sheet").Select
*** Range("E6").Select
*** Application.CutCopyMode = False
*** ActiveCell.FormulaR1C1 = ""
*** Sheets("Input Sheet").Select
*** Range("F3").Select
*** ActiveCell.FormulaR1C1 = "=TODAY()"
*** Range("F4").Select
*** ActiveCell.FormulaR1C1 = "=NOW()"
*** Range("F5").Select
*** Range("B8:C8").Select
*** Selection.Copy
*** ActiveWindow.SmallScroll Down:=-3
*** Sheets("Cover Sheet").Select
*** Range("C12:D12").Select
*** Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
******* xlNone, SkipBlanks:=False, Transpose:=False
*** Sheets("Contents Sheet").Select
*** Range("C8:D8").Select
*** Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
******* xlNone, SkipBlanks:=False, Transpose:=False
*** Sheets("Possession Staff Detail").Select
*** Range("C6:D6").Select
*** Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
******* xlNone, SkipBlanks:=False, Transpose:=False
*** Application.CutCopyMode = False
*** With Selection.Validation
******* .Delete
******* .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
******* :=xlBetween
******* .IgnoreBlank = True
******* .InCellDropdown = True
******* .InputTitle = ""
******* .ErrorTitle = ""
******* .InputMessage = ""
******* .ErrorMessage = ""
******* .ShowInput = True
******* .ShowError = True
*** End With
*** Sheets("Input Sheet").Select
*** Range("B10:C13").Select
*** Selection.Copy
*** Sheets("Cover Sheet").Select
*** Range("D28:E28").Select
*** Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
******* xlNone, SkipBlanks:=False, Transpose:=False
*** Sheets("Contents Sheet").Select
*** Range("F12:G12").Select
*** Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
******* xlNone, SkipBlanks:=False, Transpose:=False
*** Sheets("Possession Staff Detail").Select
*** Range("I7:J7").Select
*** Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
******* xlNone, SkipBlanks:=False, Transpose:=False
*** Sheets("Input Sheet").Select
*** Range("E10:F13").Select
* **Application.CutCopyMode = False
*** Selection.Copy
*** Sheets("Cover Sheet").Select
*** Range("D20:E20").Select
*** Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
******* xlNone, SkipBlanks:=False, Transpose:=False
*** Sheets("Contents Sheet").Select
*** Range("B12:C12").Select
*** Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
******* xlNone, SkipBlanks:=False, Transpose:=False
*** Sheets("Possession Staff Detail").Select
*** Range("F7:G7").Select
*** Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
******* xlNone, SkipBlanks:=False, Transpose:=False
*** Sheets("Input Sheet").Select
*** Range("H10:H13").Select
*** Application.CutCopyMode = False
*** Selection.Copy
*** Sheets("Cover Sheet").Select
*** Range("H20").Select
*** Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
******* xlNone, SkipBlanks:=False, Transpose:=False
*** Sheets("Contents Sheet").Select
*** Range("H12").Select
*** Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
******* xlNone, SkipBlanks:=False, Transpose:=False
*** Sheets("Possession Staff Detail").Select
*** Range("K7").Select
*** Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
******* xlNone, SkipBlanks:=False, Transpose:=False
*** Sheets("Contents Sheet").Select
*** ActiveWindow.SmallScroll Down:=3
*** Sheets("Cover Sheet").Select
*** ActiveWindow.SmallScroll Down:=18
*** Sheets("Input Sheet").Select
*** Range("C3").Select
*** Application.CutCopyMode = False
*** Selection.Copy
*** Sheets("Cover Sheet").Select
*** Range("E46").Select
*** Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
******* xlNone, SkipBlanks:=False, Transpose:=False
*** Sheets("Input Sheet").Select
*** Range("D8").Select
*** Range("F3:F4").Select
*** Application.CutCopyMode = False
*** Selection.Copy
*** Sheets("Cover Sheet").Select
*** Range("H46").Select
*** Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
******* xlNone, SkipBlanks:=False, Transpose:=False
*** Sheets("Cover Sheet").Select
*** ActiveWindow.SmallScroll Down:=-21
*** Sheets("Input Sheet").Select
*** Range("K9").Select
*** Application.CutCopyMode = False
*** Selection.Copy
*** Sheets("Cover Sheet").Select
*** Range("H12").Select
*** Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
******* xlNone, SkipBlanks:=False, Transpose:=False
*** Sheets("Input Sheet").Select
*** Range("B16:K32").Select
*** Application.CutCopyMode = False
*** Selection.Copy
*** Sheets("Possession Staff Detail").Select
*** Range("B13:C13").Select
*** Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
******* xlNone, SkipBlanks:=False, Transpose:=False
*** Sheets("Input Sheet").Select
*** Range("H4:K7").Select
*** Application.CutCopyMode = False
*** Selection.Copy
*** Sheets("Possession Staff Detail").Select
*** Range("B32:D32").Select
*** Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
******* xlNone, SkipBlanks:=False, Transpose:=False
*** Sheets("Input Sheet").Select
*** Range("D11").Select
*** Application.CutCopyMode = False
*** ActiveCell.FormulaR1C1 = ""
*** Range("D12").Select
*** Sheets("Input Sheet").Select
*** Range("E8:F8").Select
*** Selection.Copy
*** Sheets("Cover Sheet").Select
*** Range("D39:E39").Select
*** Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:= _
*** xlNone, SkipBlanks:=False, Transpose:=False
*** Sheets("Input Sheet").Select
*** Range("B4").Select
*** ActiveCell.FormulaR1C1 = ""
*** Range("B2").Select
*** Application.CutCopyMode = False
End Sub