Help to make VBA code more efficient.

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
153
Office Version
  1. 2013
Platform
  1. Windows
I have the following code that has been generated by recording a macro.

VBA Code:
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

Board Regular
Joined
Sep 22, 2020
Messages
153
Office Version
  1. 2013
Platform
  1. Windows
Fluff,

Once again thanks for your valuable input.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
My pleasure.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,172
Messages
5,640,584
Members
417,152
Latest member
DayTimeSeby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top