如何使用VBA在Excel中为不同的工作表设置不同的冻结窗格

I want to set different freeze panes for different sheets in an Excel. I tried using the below code but this is being assigned to all sheets. But I want to freeze first 6 rows in my first sheet and only top row in second sheet. I tried so many ways but no result. Any suggestions?

我想在Excel中为不同的工作表设置不同的冻结窗格。我尝试使用以下代码,但这被分配给所有工作表。但我想在我的第一张纸上冻结前6行,在第二张纸上只冻结前排。我尝试了很多方法但没有结果。有什么建议?

With xlApp.ActiveWindow
    .SplitColumn = 0
    .SplitRow = 6
End With

The full code (from comments) is:

完整代码(来自评论)是:

Dim xlApp As Application 
Dim xlWb As Workbook 
'Dim xlWs As Worksheet 
xlApp = New Application() 
xlWb = xlApp.Workbooks.Open(csFileName) 
With xlApp.ActiveWindow 
  .SplitColumn = 0 
  .SplitRow = 6 
End With

1 个解决方案

#1

Try this out, replacing the sheet names, rows & columns with the appropriate values:

试试这个,用适当的值替换工作表名称,行和列:

Worksheets("Sheet1").activate
ActiveWindow.SplitColumn = 0
Activewindow.SplitRow = 6

Worksheets("Sheet2").activate
ActiveWindow.SplitColumn = 5
Activewindow.SplitRow = 11

Worksheets("Sheet3").activate
ActiveWindow.SplitColumn = 10
Activewindow.SplitRow = 16

Based on the code you posted in comments, this should work:

根据您在评论中发布的代码,这应该有效:

Dim xlApp As Application 
Dim xlWb As Workbook 
xlApp = New Application() 
xlWb = xlApp.Workbooks.Open(csFileName) 
xlwb.worksheets("Sheet1").activate
With xlApp.ActiveWindow 
  .SplitColumn = 0 
  .SplitRow = 6 
End With
xlwb.worksheets("Sheet2").activate
With xlApp.ActiveWindow 
  .SplitColumn = 0 
  .SplitRow = 1
End With

I would recommend always explicitly specifying which sheet you want active for this, since it requires ActiveWindow in order to function, that way you won't ever accidentally be on the wrong window when you're setting your split rows.

我建议始终明确指定您想要激活哪个工作表,因为它需要ActiveWindow才能运行,这样您在设置拆分行时就不会意外地在错误的窗口上。

Dim xlApp As Application 
Dim xlWb As Workbook 
xlApp = New Application() 
xlWb = xlApp.Workbooks.Open(csFileName) 
xlwb.worksheets("Sheet1").activate
xlWb.worksheets("Sheet1").range("A6").select
xlApp.ActiveWindow.FreezePanes
xlwb.worksheets("Sheet2").activate
xlWb.worksheets("Sheet2").range("A2").select
xlApp.ActiveWindow.FreezePanes

#1