I am trying to Loop though all ws in a workbook from Start tab through end tab and unhide columns Q through R if they are already hidden and then copy and
paste values for a range of cells from "Q6:R21" to "L6:M21"for all the worksheets. The range to copy from and paste to are the same in all the worksheets. I also want to clear contents of column "O6:O21" in all these ws. I have written the below code to loop and copy and paste, but it is only working in the active sheet and not in all the worksheets. Can anyone please correct the code / give me a code similar to this one? The bolded code is doing its job, but the italicized one is not.
Sub Worksheets_unhide() ' this is not working- the loops and unhiding columns for all ws except1st
Dim MyRange As Range
Dim ws As Worksheet
Dim StartIndex As Integer
Dim EndIndex As Integer
Dim LoopIndex As Integer
StartIndex = Sheets("BLANK").Index + 1
EndIndex = Sheets("end").Index - 1
For LoopIndex = StartIndex To EndIndex
Columns("Q:R").Select
If Selection.EntireColumn.Hidden = True Then _
Selection.EntireColumn.Hidden = False
With Range("Q6", "R21")
.Select
.Copy
End With
Cells(6, 5).PasteSpecial xlPasteAll
Exit For
Next LoopIndex
End Sub
Sub Worksheets_Hide() ' to add another button to just hide the columns
Dim ws As Worksheet
Dim StartIndex As Integer
Dim EndIndex As Integer
Dim LoopIndex As Integer
StartIndex = Sheets("BLANK").Index + 1
EndIndex = Sheets("end").Index - 1
For LoopIndex = StartIndex To EndIndex
Columns("Q:R").Select
If Selection.EntireColumn.Hidden = False Then _
Selection.EntireColumn.Hidden = True
Exit For
Next LoopIndex
End Sub
paste values for a range of cells from "Q6:R21" to "L6:M21"for all the worksheets. The range to copy from and paste to are the same in all the worksheets. I also want to clear contents of column "O6:O21" in all these ws. I have written the below code to loop and copy and paste, but it is only working in the active sheet and not in all the worksheets. Can anyone please correct the code / give me a code similar to this one? The bolded code is doing its job, but the italicized one is not.
Sub Worksheets_unhide() ' this is not working- the loops and unhiding columns for all ws except1st
Dim MyRange As Range
Dim ws As Worksheet
Dim StartIndex As Integer
Dim EndIndex As Integer
Dim LoopIndex As Integer
StartIndex = Sheets("BLANK").Index + 1
EndIndex = Sheets("end").Index - 1
For LoopIndex = StartIndex To EndIndex
Columns("Q:R").Select
If Selection.EntireColumn.Hidden = True Then _
Selection.EntireColumn.Hidden = False
With Range("Q6", "R21")
.Select
.Copy
End With
Cells(6, 5).PasteSpecial xlPasteAll
Exit For
Next LoopIndex
End Sub
Sub Worksheets_Hide() ' to add another button to just hide the columns
Dim ws As Worksheet
Dim StartIndex As Integer
Dim EndIndex As Integer
Dim LoopIndex As Integer
StartIndex = Sheets("BLANK").Index + 1
EndIndex = Sheets("end").Index - 1
For LoopIndex = StartIndex To EndIndex
Columns("Q:R").Select
If Selection.EntireColumn.Hidden = False Then _
Selection.EntireColumn.Hidden = True
Exit For
Next LoopIndex
End Sub