工作表冻结前两行_冻结所有工作表宏

工作表冻结前两行

冻结所有工作表宏 (Freeze All Worksheets Macro)

If you're working with a large worksheet in Excel, it usually helps if you freeze the cells at the top and/or the left side of the sheet. That way, your headings are always visible, along with other key information that you've put at the top of the sheet. You can freeze each sheet individually, or use this macro to freeze all worksheets at once.

如果您在Excel中使用大型工作表，则冻结表顶部和/或左侧的单元格通常会有所帮助。 这样，您的标题以及放在表顶部的其他关键信息将始终可见。 您可以单独冻结每个工作表，也可以使用此宏一次冻结所有工作表。

什么被冻结？ (What Gets Frozen?)

When I hear "Frozen", I think of the Disney movie, and the song, "Let It Go!" pops into my head. If you're not familiar with that song, lucky you! My granddaughter loved that movie, and had a birthday cake with Elsa on it, a couple of years ago (see the picture at the end of this post). Anyway, in that movie, everything got frozen.

当我听到《冰雪奇缘》时，我想起了迪斯尼电影和歌曲《 Let It Go！》。 突然涌入我的脑海。 如果您对那首歌不熟悉，那就很幸运！ 我的孙女很喜欢那部电影，并在几年前在上面放了一个生日蛋糕，上面印着艾尔莎(Elsa)(请参阅本文结尾处的图片)。 无论如何，在那部电影中， 一切都冻结了。

In an Excel worksheet, the cells(s) that you have selected will control what gets frozen. Here's a chart, with all the options that I can think of. If I missed something, please let me know.

在Excel工作表中，所选单元格将控制冻结的单元格。 这是一张图表，其中包含所有我能想到的选项。 如果我错过了什么，请告诉我。

 

Selection	Frozen Area
Cell A1	All cells above and to the left of center
Other cell in column A	All rows above that cell
Other cell in row 1	All columns to the left of that cell
Other cell	All cells above and to the left of the selected cell
Row 1	All cells above and to the left of center
Any other row	All rows above that row
Column A	All cells above and to the left of center
Any other column	All columns to the left of that column

选拔	冰冻地区
单元格A1	中心上方和左侧的所有单元格
A列中的其他单元格	该单元格上方的所有行
第1行中的其他单元格	该单元格左侧的所有列
其他细胞	所选单元格上方和左侧的所有单元格
第1行	中心上方和左侧的所有单元格
任何其他行	该行上方的所有行
A栏	中心上方和左侧的所有单元格
任何其他栏	该列左侧的所有列

手动冻结工作表 (Freeze Worksheets Manually)

If you just want to freeze one sheet, you can do it manually.

如果您只想冻结一张纸，则可以手动进行。

• First, select a cell, row or column, below and to the right of the area that you want frozen. See details in the chart above.
  首先，选择要冻结的区域下方和右侧的单元格，行或列。 请参阅上表中的详细信息。
• On the Excel Ribbon, click the View tab
  在Excel功能区上，单击“视图”选项卡
• Click the Freeze Panes command
  单击“冻结窗格”命令
• Click Freeze Panes, to freeze at the selected location – OR, choose a command to freeze the first row or first column
  单击“冻结窗格”，以冻结在选定的位置–或者，选择命令以冻结第一行或第一列

冻结所有工作表宏 (Freeze All Worksheets Macro)

If you have lots of sheets in a workbook, and want to freeze all of them at the same spot, you can use this Freeze All Worksheets macro.

如果工作簿中有很多工作表，并且想将所有工作表冻结在同一位置，则可以使用“冻结所有工作表”宏。

• Before you run the macro, select the cell(s) to use as the freeze location (se the chart above).
  在运行宏之前，请选择要用作冻结位置的单元格(请参见上面的图表)。
• When the macro runs, you'll see a confirmation message, asking if you want to freeze at the current selection.
  宏运行时，您会看到一条确认消息，询问您是否要冻结当前的选择。
• Click Yes, to go ahead, and click No to cancel the macro.
  单击是，继续，然后单击否以取消该宏。

冻结所有工作表代码 (The Freeze All Worksheets Code)

Copy this macro into a regular code module, then select cell(s) on any worksheet, and run the macro to freeze all the sheets in the active workbook.

将此宏复制到常规代码模块中 ，然后在任何工作表上选择单元格，然后运行宏以冻结活动工作簿中的所有工作表。

Sub FreezeAllSheets()
'www.contextures.com
Dim wsA As Worksheet
Dim ws As Worksheet
Dim wbA As Workbook
Dim strSel As String
Dim lRsp As Long
On Error GoTo errHandler

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
strSel = Selection.Address

lRsp = MsgBox("Freeze all sheets at current selection?", _
  vbQuestion + vbYesNo + vbDefaultButton1, "Freeze Sheets?")

If lRsp = vbYes Then
  Application.ScreenUpdating = False
  For Each ws In wbA.Worksheets
    ws.Activate
    Range(strSel).Select
    ActiveWindow.FreezePanes = True
  Next ws
  wsA.Activate
Else
  'do nothing
End If

exitHandler:
  Application.ScreenUpdating = True
  Exit Sub
errHandler:
  MsgBox "Could not freeze all sheets"
  Resume exitHandler

End Sub

获取样本工作簿 (Get the Sample Workbook)

To get the sample workbook with the Freeze All Worksheets macro, go to the Excel Worksheet Freeze and Zoom Macros page on my Contextures website.

若要获取带有“冻结所有工作表”宏的示例工作簿，请转到Contextures网站上的“ Excel工作表冻结和缩放宏”页面 。

You can test the macro in that workbook, before adding it to your own files. Remember to enable macros when you open the workbook.

您可以先在该工作簿中测试该宏，然后再将其添加到自己的文件中。 请记住在打开工作簿时启用宏。

The sample workbook also has two other macros:

该示例工作簿还具有其他两个宏：

• Unfreeze All Worksheets
  取消冻结所有工作表
• Zoom All Worksheets (enter the zoom level that you want(
  缩放所有工作表(输入所需的缩放级别(

冷冻生日蛋糕 (The Frozen Birthday Cake)

Here's my granddaughter's Frozen birthday cake. Today is her birthday, and she chose a plain chocolate cake for this year's celebration. She must be growing up!

这是我孙女的冰冻生日蛋糕。 今天是她的生日，她为今年的庆祝活动选择了一个纯巧克力蛋糕。 她一定长大了！

翻译自: https://contexturesblog.com/archives/2017/03/23/excel-freeze-worksheets-macro/

工作表冻结前两行