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.

 

[Fluff]

If you are simply converting formulae to values on the Formula sheet, why do you need to unprotect the other sheets?

 

[Kayslover]

Fluff,

Suppose, you are right. Could just unprotect the Formula sheet.

Thought it's best to unprotect all sheets then protect all in one go.

 

[Fluff]

How about

Sub Kayslover()
   With Sheets("Formula")
      .Unprotect
      With .Range("A2:D2")
         .Value = .Value
      End With
      With .Range("M2", .Range("M" & Rows.Count).End(xlUp))
         .Value = .Value
         .Replace "&", "*", xlPart, , , , False, False
      End With
      With .Range("O2", .Range("O" & Rows.Count).End(xlUp))
         .Value = .Value
         .Replace "&", "*", xlPart, , , , False, False
      End With
      .Protect
   End With
   Sheets("Banking").Select
   ActiveWorkbook.Save
End Sub

 

[Kayslover]

Fluff,

Working like a charm (and a bit quicker).

I have other macros which do copy special values. I will try and replace them with the code you have provided above.

Once again many thanks, much appreciated.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Kayslover]

Fluff,

I have one question, what if I have the following statements in my macro:-

Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5")).Select
    Range("B4:B32").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

How can I select the array of sheets and use your formula?

Do I substitute it as follows:-

With Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5")).Select
      With .Range("B4:B32")
         .Value = .Value
      End With

 

[Fluff]

You would probably need to loop through the sheets.

 

[Kayslover]

Could be my naive understanding. Isn't selecting an arrary of sheets just the same as grouping sheets? and therefore what ever you do then changes are replicated across all sheets.

The code I have in chat #7 is already working. I just wanted to make it more efficient.

If I have to loop, what do I need to do?

 

[Fluff]

Not everything works on grouped sheets & it's not something I've ever done, so not sure what works & what doesn't.
If your code works then I would suggest sticking with it.