尝试在所有Excel工作表上运行代码 [英] Trying to run a code on all Excel sheets

问题描述

我正在尝试在我在一个excel文件中的所有工作表上运行此代码，但这不工作。它仅在第一张表上合并单元格。这是我的代码：

  Sub MergeColumns（）
 Dim ws As Worksheet 
对于ActiveWorkbook中的每个ws .Worksheets 
选择案例ws.Name 
案例Sheet1，Sheet2，Sheet3，Sheet4
 ws.Range（H1：S1）。选择
 Case Else 
 Selection.Merge 
带有选择
 .Horizo​​ntalAlignment = xlCenter 
 .VerticalAlignment = xlBottom 
结束
结束选择
下一个ws 
 End Sub 
  

我会感谢您的时间相同。

解决方案

这是你正在尝试的吗？

  Option Explicit 
 
 Sub MergeColumns（）
 Dim ws As Worksheet 
 
对于每个ws在ActiveWorkbook.Worksheets 
选择案例ws .Name 
案例Sheet1，Sheet2，Sheet3，Sheet4
带有ws.Range（H1：S1）
 .Merge 
 .Horizo​​ntalAlignment = xlCenter 
 .VerticalAlignment = xlBottom 
结束
结束选择
下一步ws 
 End Sub 
  

或者如果你想忽略Sheet1到Sheet4，那么尝试这个

  Option Explicit 
 
 Sub MergeColumns（）
 Dim ws As Worksheet 
 
对于每个ws在ActiveWorkbook.Worksheets 
选择案例ws.Name 
案例 Sheet1，Sheet2，Sheet3，Sheet4
 Case Else 
带有ws.Range（H1：S1）
 .Merge 
 .Horizo​​ntalAlignment = xlCenter 
 .VerticalAlignment = xlBottom 
结束
结束选择
下一个ws 
 End Sub 
  

I am trying to run this code on all worksheets that I have in one excel file but this is not working. It only merges cells on first sheet. Here is my code:

Sub MergeColumns()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
    Select Case ws.Name
    Case "Sheet1", "Sheet2", "Sheet3", "Sheet4"
   ws.Range("H1:S1").Select
            Case Else
    Selection.Merge
        With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
    End With
    End Select
    Next ws
End Sub

I would appreciate your time regarding the same.

解决方案

Is this what you are trying?

Option Explicit

Sub MergeColumns()
    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        Select Case ws.Name
        Case "Sheet1", "Sheet2", "Sheet3", "Sheet4"
            With ws.Range("H1:S1")
                .Merge
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlBottom
            End With
        End Select
    Next ws
End Sub

or if you want to ignore Sheet1 to Sheet4 then try this

Option Explicit

Sub MergeColumns()
    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        Select Case ws.Name
        Case "Sheet1", "Sheet2", "Sheet3", "Sheet4"
        Case Else
            With ws.Range("H1:S1")
                .Merge
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlBottom
            End With
        End Select
    Next ws
End Sub

这篇关于尝试在所有Excel工作表上运行代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！