Help to make VBA code more efficient.

[Kayslover]

I have the following code that has been generated by recording a macro.

Option Explicit

Public Sub Banking_Copy_Special_Values()

' This macro selects sheet named formula and Then Selects Columns M and O. Once selected they are individually copied then Paste Special Values to copy the real values.

Dim ws As Worksheet

' Sheets are protected in Banking Template. They need to be UnProtected as certain cells will be Paste Special Values (to remove formulas) and therefore the sheets are Unprotected.

Sheets("Formula").Select

For Each ws In Worksheets

ws.Unprotect Password:=""

Next

Range("A2:D2").Select

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False

ActiveWorkbook.Save

'The following paste special VALUES is required to turn the reference to file names. This is done for Columns M and O

Columns("M:M").Select

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False

Columns("O:O").Select

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

ActiveWindow.SmallScroll ToRight:=-2

Range("M2").Select

Application.CutCopyMode = False

'The following replaces & to * in cell 2 to 53 for columns M and O

Range("M2").Select

Range(Selection, Selection.End(xlDown)).Select

ActiveWindow.SmallScroll Down:=-54

Selection.Replace What:="&", Replacement:="*", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False

Range("O2").Select

Range(Selection, Selection.End(xlDown)).Select

ActiveWindow.SmallScroll Down:=-54

Selection.Replace What:="&", Replacement:="*", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False

' Sheets are protected in the workbook

For Each ws In Worksheets

ws.Protect Password:=""

Next

ActiveSheet.Range("A2").Select

'Select Sheet Banking

Sheets("Banking").Select

ActiveWorkbook.Save

End Sub

The code does the following:-

• Sheet called Formula is selected;
• Unprotect sheets in the workbook;
• Copy A2 to D2 and then paste special values back to the same cell in sheet Formula;
• Saves the workbook;
• Selects column M, and then paste special values back to the same cells in sheet Formula; (I really only need to select M2 to M53)
• Selects column O, and then paste special values back to the same cells in sheet Formula; (I really only need to select O2 to O53)
• Selects M2 to O53 and replaces all instances of & to *;
• Selects O2 to O53 and replaces all instances of & to *;
• Protect sheets in the workbook;
• Saves the workbook.

Can the macro be changed to run more efficiently, as it is taking a bit long compared to other macros that are doing other things.

Any assistance offered would be welcomed.

 

[Kayslover]

Fluff,

Once again thanks for your valuable input.

 

[Fluff]

My pleasure.