我试图仅在一个名为VPL的工作表上隐藏/取消隐藏特定列,然后在工作簿的其余工作表上隐藏/取消隐藏一组不同的特定列。
这是我到目前为止的代码,但是它仅在名为VPL的工作表上起作用,并且在工作簿中的其余工作表之间循环时不会隐藏任何其他工作表上的列。
Sub HideAndUnHideProduct2()
'Are you sure you want to run this macro, when you run this a box will popup and ask yes or no
Dim varResponse As Variant
varResponse = MsgBox("This will Hide/UnHide Product 2 on All Sheets, Do You Want To Continue", vbYesNo, "Selection")
If varResponse <> vbYes Then Exit Sub
Application.ScreenUpdating = False
'Hides/UnHides Product columns on all sheets
If VPL.Columns("L:N").Hidden = True Then
'UnHides Specified columns On Specified Sheet
VPL.Columns("L:N").EntireColumn.Hidden = False 'Unhides Selected Colunms
'UnHides columns On All Sheets Except The Ones Specified Below
Dim wsU As Worksheet
For Each wsU In Sheets
If wsU.Name <> "VPL" Then '<Sheets To Be Skiped From Loop
'Code To Be Looped below
Columns("L:M").EntireColumn.Hidden = False 'UnHides Selected Colunms
'End of Code To Be Looped
End If
Next wsU
Else
'Hides Specified columns On Specified Sheet
VPL.Columns("L:N").EntireColumn.Hidden = True
'Hides columns On All Sheets Except The Ones Specified Below
Dim wsH As Worksheet
For Each wsH In Sheets
If wsH.Name <> "VPL" Then '<Sheets To Be Skiped From Loop
'Code To Be Looped below
Columns("L:M").EntireColumn.Hidden = True 'Hides Selected columns
'End of Code To Be Looped
End If
Next wsH
End If
Application.ScreenUpdating = True
End Sub
任何帮助都将不胜感激,因为我在vba中不是很好
没有工作表参考,您的代码
Columns("L:M").EnterColumn.Hidden = False
将仅引用活动工作表。由于要遍历多个工作表,因此需要在for循环中引用工作表变量,如下所示
wsU.Colunns("L:M").EnterColumn.Hidden = False
类似地
wsH.Columns("L:M").EntireColumn.Hidden = True
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句