I have the following code that has been generated by recording a macro.
The code does the following:-
Any assistance offered would be welcomed.
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.
Any assistance offered would be welcomed.