This page shows how to create a macro that automatically removes the sheet names of the current sheet in formulas of an excel spreadsheet. Through using a macro, you can take out the current sheet from all of the formulas in that use the current sheet name of your workbook. The method used is to get the sheet name with a function and then use the instring function to replace the sheet name with a function. The VBA code for running this program is shown in this page.
Mechanics of Running the Macro if you Don’t Care About the VBA
If you refer to another sheet when typing in a formula in an excel sheet, the current sheet name will appear in formula. The current sheet name is of course not necessary for making the formula work. Further, the current sheet name can be irritating and make the formulas difficult to read and interpret. An example of the problem is shown on the screenshot below.
To remove the sheet links you can use the Generic Macros excel file. One of the buttons on the form that appears after pressing CNTL, ALT, C will scan your sheet and remove the names. The purple button that allows you to do this is shown in the screenshot below.
VBA Code for Removing Sheet Links
You could VBA code can be copied into your sheet or your personal workbook if you want to use it. If you try this be careful wth the functions that find rows and columns of the sheet. Note that instead of using the find function with worksheetfunction, the INSTR VBA function is used. This function is a lot more stable then using the find function when there is an error. The macro shown below works through cells in a sheet and replaces the sheet name with a blank. It allows for five sheet names in the formula.
.
Sub x_replace_sheet_name()
Dim sheet_name_lenght As Single
x_find_rows ' run programs to find the number of rows to reduce the time
x_find_cols
Application.StatusBar = " Searching to Replace in " & max_row & " Rows and " & max_col & " Columns "
calculation_state = Application.Calculation
Application.Calculation = xlCalculationManual
sheet_name_active = ActiveSheet.Name ' get the current sheet name so you can look for this.
test_start = Left(sheet_name_active, 1) ' test_start is the leftmost character in the current sheet name
space_test = False ' When there is a space in the sheet name, then ""
For i = 1 To Len(sheet_name_active)
sheet_name_char = Mid(sheet_name_active, i, 1)
If sheet_name_char = " " Then space_test = True
Next i
If space_test Then ' put quotes around the sheet name
sheet_name_test = "'" & sheet_name_active & "'!"
Else
sheet_name_test = sheet_name_active & "!"
End If
sheet_name_length = Len(sheet_name_test)
msgtest = MsgBox(" Looking for sheet name " & sheet_name_test & " Is this ok ", vbOKCancel, " Sheet Name Replace Program")
If msgtest = 2 Then Exit Sub
For row = 1 To max_row
For col = 1 To max_col
Cells(row, col).Select
cell_string = Cells(row, col).Formula ' Only replace formulas
left_character = Left(cell_string, 1)
If left_character <> "=" Then GoTo continue1:
If cell_string = "" Then GoTo continue1: ' Skip over blanks
' Replace the cell string with blank
start_mid = 1
For j = 1 To 5
If start_mid = 0 Then GoTo continue2:
start_mid = InStr(start_mid, cell_string, sheet_name_test) ' key item that replaces
If start_mid = 0 Then GoTo continue2:
MsgBox " Trying to Replace " & cell_string & " Starting in " & start_mid & " Row " & row & " Column " & col
cell_string = WorksheetFunction.Replace(cell_string, start_mid, sheet_name_length, "") ' key item that replaces
On Error Resume Next
Cells(row, col).Formula = cell_string
continue2:
Next j
continue1:
Next col
Next row
Application.Calculation = calculation_state
Application.StatusBar = False
Range("A1").Select
'
Application.Goto Range("A1")
End Sub
.