The generic macros file has a few added short-cut keys that I use a lot including getting back the CNTL TAB and being able to cut and transpose cells. In addition I have included some other hand-made short-cuts. Please do not go to the styles menu and think you are good. The generic macros file and a workbook named fm.xls includes a lot of different functions and macros. Some of the more important macros that create table of contents and remove the current sheet name from formulas and finds the links are shown below. As explained above, none of these macros have any irritating passwords and you can copy the macros into your files. The fm.xlsm file below has some of the older macros that I made a few years ago. The example calendar shows how you can create a macro that puts a password in a file and uses MATCH and INDEX over and over again with conditional formatting to make a calendar in excel (I doubt very much that you will use this).
Cutting and Pasting with Transpose
Sometimes you need to transpose with the cut. The copy and paste special does not work. I have written a program to do this that you can copy below.
Sub Cut_and_Transpose() 'debug2 = False
start_cell = InputBox(" Enter the Start Cell to Cut", , "A3") end_cell = InputBox(" Enter the End Cell to Cut", , "M15")
start_row = Range(start_cell).row
start_col = Range(start_cell).Column end_row = Range(end_cell).row end_col = Range(end_cell).Column
Tot_Rows = end_row - start_row + 0
Tot_cols = end_col - start_col + 0
If debug2 Then MsgBox " Total Rows " & Tot_Rows
Target_cell = InputBox(" Enter the Target Cell ", , "C24") target_row = Range(Target_cell).row
Target_col = Range(Target_cell).Column
If debug2 Then MsgBox " Target Col " & Target_col
For row = start_row To Tot_Rows + start_row
to_col = row + col * 0 + Target_col - start_row
For col = start_col To Tot_cols + start_col
to_row = col + row * 0 + target_row - start_col
If debug2 = True Then MsgBox " Row From " & row & " Row To " & to_row & Chr(10) & " Col From " & col & " Col To " & to_col
Cells(row, col).Select
Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Selection.Cut
Cells(to_row, to_col).Select
value_in_cells = Selection.Value
If IsEmpty(value_in_cells) Then
ActiveSheet.Paste
Else MsgBox " Cell is not empty "
End If
Next col
Next row
End Sub
Miscellaneous Other Subroutines in Generic Macros
The generic macros file and a workbook named fm.xls includes a lot of different functions and macros. Some of the more important macros that create table of contents and remove the current sheet name from formulas and finds the links are shown below. As explained above, none of these macros have any irritating passwords and you can copy the macros into your files. The fm.xlsm file below has some of the older macros that I made a few years ago. The example calendar shows how you can create a macro that puts a password in a file and uses MATCH and INDEX over and over again with conditional formatting to make a calendar in excel (I doubt very much that you will use this).
Sub x_replace_sheet_name() Takes away current name of sheet in formulas
Sub x_Delete_cols() Deletes columns that are blank
Sub x_Delete_rows() Deletes rows that are blank
Sub x_fix_decimal() Fixes Problems with auto formatting
Sub x_find_rows() Finds number of rows in sheet
Sub x_find_cols() Finds number of cols in sheet
Sub x_colourTitles() Colours titles for the first column
Sub x_Create_Table_of_Contents_From_Sheet_Names() Creates Contents with links
Sub x_find_externl_links() Find External Links
Sub x_hide_sheets_after() Hide Sheets after Given Name
Functions in Generic Macros
In addition to macros, I put a bunch of user defined functions in the generic macros file. These macros do things like find the sheet name or the file name. They can also show who saved the file last and make a payback function. Unlinke the subroutine macros, you cannot just have the file open the functions. Instead, you must find the functions and then copy them into your file.
Function sheet_name(cell)
Function sum_labels(series)
Function File_name() As Variant
Function MyUDF(LastSaved1 As Boolean) As Double
Function Last_save_by() As Variant
Function LastSaved() As String
Function lookup_NA(lookup_value, test_array, result_array)
Function match_adj(lookup_value, lookup_array)
Function payback(series)
Function dpayback(d_rate, series)
Function period_of_year(period, timing)
Function show_formula(cell)
Function show_formulaR(cell)
Function show_formulaL(cell)