Excel: Excel VBA looping - A VBA beginner


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. [I] 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[/I] 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


This question generated 20 answers. To proceed to the answers, click here.

This thread is current as of May 28, 2014.


For more resources for Microsoft Excel