במדריך הבא נראה לכם כיצד ניתן להפוך באמצעות קוד VBA את כל הנוסחאות לערכים (“הדבקה כערכים”).

ניתן לבצע פעולה זו על:

• גיליון בודד
• כל הגיליונות בקובץ
• כל הגיליונות בכל הקבצים בתיקיה

(מי שלא יודע כיצד להריץ קוד VBA מוזמן להציץ במדריך הנ”ל)

שימו לב – לפני תחילת עבודה עם אחד מהקודים המצורפים, אנו ממליצים בחום לגבות את כל עבודתכם. לא ניתן לשחזר את הנוסחאות לאחר הרצת הקוד!

המרת הנוסחאות לערכים בגיליון בודד:

על מנת להמיר את כל הנוסחאות בגיליון מסוים לערכים, הכניסו את הקוד הבא לתוך Module חדש והריצו אותו:

Sub ConvertFormulasToValuesSinglesheet()
ThisWorkbook.ActiveSheet.UsedRange.Value = ThisWorkbook.ActiveSheet.UsedRange.Value
End Sub

המרת הנוסחאות לערכים בכל הגיליונות בקובץ מסוים:

על מנת להמיר את כל הנוסחאות בכל הגיליונות (כולל הגיליונות המוסתרים!) בקובץ מסוים לערכים, הכניסו את הקוד הבא לתוך Module חדש והריצו אותו:

Sub ConvertFormulasToValuesAllSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
ws.UsedRange.Value = ws.UsedRange.Value
Next

End Sub

המרת הנוסחאות לערכים בכל הגיליונות בכל הקבצים בתיקיה מסוימת:

אזהרה נוספת – גבו את הקבצים לפני הרצת הקוד, שכן בסיום הרצת הקוד כל הקבצים בתיקיה יהיו נטולי נוסחאות ולא ניתן יהיה לשחזרן! 

כעת, נניח שיש לכם בתיקיה מסוימת 20 קבצי אקסל, ואתם מעוניינים להמיר את כל הנוסחאות שמופיעות בקבצים הללו לערכים.

תוכלו לעשות זאת על ידי הרצת הקוד המצורף, שמבצע את הדברים הבאים:

1. פותח תיבת דו שיח המאפשרת לכם לבחור את התיקיה שבה נמצאים הקבצים שלכם
2. פותח קובץ אקסל מהתיקיה
3. עובר על כל הגיליונות בקובץ האקסל הזה ומחליף את כל התאים המכילים נוסחאות לתאים שמכילים ערכים
4. שומר את קובץ האקסל, סוגר אותו ועובר לקובץ הבא

להלן הקוד:

Sub LoopAllExcelFilesInFolderCancelFormulas()

‘Purpose: To loop through all Excel files in a user specified folder and convert all formulas to values

Dim wb As Workbook

Dim ws As Worksheet

Dim myPath As String

Dim myFile As String

Dim myExtension As String

Dim FldrPicker As FileDialog

‘Optimize Macro Speed

Application.EnableEvents = False

Application.Calculation = xlCalculationManual

‘Disable Excel Pop-up messages:

Application.DisplayAlerts = False

‘Retrieve Target Folder Path From User

Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

With FldrPicker

.Title = “Select A Target Folder”

.AllowMultiSelect = False

If .Show <> -1 Then GoTo NextCode

myPath = .SelectedItems(1) & “\”

End With

‘In Case of Cancel

NextCode:

myPath = myPath

If myPath = “” Then GoTo ResetSettings

‘Target File Extension (must include wildcard “*”)

myExtension = “*.xls*”

‘Target Path with Ending Extention

myFile = Dir(myPath & myExtension)

‘Loop through each Excel file in folder

Do While myFile <> “”

‘Set variable equal to opened workbook

Set wb = Workbooks.Open(Filename:=myPath & myFile)

‘Ensure Workbook has opened before moving on to next line of code

DoEvents

For Each ws In wb.Sheets

ws.UsedRange.Value = ws.UsedRange.Value

Next

‘Save and Close Workbook

wb.Close SaveChanges:=True

‘Ensure Workbook has closed before moving on to next line of code

DoEvents

‘Get next file name

myFile = Dir

Loop

‘Message Box when tasks are completed

MsgBox “Task Complete!”

ResetSettings:

‘Reset Macro Optimization Settings

Application.EnableEvents = True

Application.Calculation = xlCalculationAutomatic

Application.DisplayAlerts = True

End Sub

כעת כל הקבצים בתיקיה מכילים ערכים בלבד 🙂

הערה: הקוד שרץ על כל הקבצים בתיקיה ומבצע פעולה מסוימת מתבסס על הקוד המצורף מאתר thespreadsheetguru.