Last active
March 14, 2024 06:04
-
-
Save 136s/4336c96f6bda9194b27fcb95c1386030 to your computer and use it in GitHub Desktop.
add filters to all sheets in a Excel book
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sub MakeFilter() | |
' ウィンドウ枠の固定 | |
If ActiveWindow.FreezePanes = True Then | |
ActiveWindow.FreezePanes = False | |
End If | |
If Selection(1) = Range("A1") Then | |
Range("B2").Select | |
End If | |
If Selection.Count > 1 Then | |
Selection(1).Select | |
End If | |
ActiveWindow.FreezePanes = True | |
' フィルターの設定 | |
If ActiveSheet.AutoFilterMode = True Then | |
Selection.AutoFilter | |
End If | |
Selection.AutoFilter | |
' フォントの設定 | |
ActiveWorkbook.Theme.ThemeFontScheme.Load (Environ("USERPROFILE") & "\AppData\Roaming\Microsoft\Templates\Document Themes\Theme Fonts\UDEV Gothic JPDOC.xml") | |
Cells.Font.ThemeFont = xlThemeFontMinor | |
End Sub | |
' すべてのシートに対して MakeFilter() をする | |
Sub MakeFilters() | |
Dim start As String | |
start = ActiveSheet.Name | |
For Each s In ActiveWorkbook.Sheets | |
s.Select | |
Call MakeFilter | |
Next s | |
Sheets(start).Select | |
End Sub | |
' セル中の "\n" を改行文字に置換して行の高さを調整する | |
Sub ReplaceNewLine() | |
Cells.Replace What:="\n", Replacement:=vbLf | |
Cells.EntireRow.AutoFit | |
End Sub |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment