Excel VBA looping - A VBA beginner

D

DianaDSN

New Member
Joined
May 28, 2014
Messages
16
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


 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
You are declaring a worksheet variable but not using it in your code. I did not try the changes I made for you, but I think they are headed in the right direction. Test in on a copy and then make whatever other changes you need to. Maybe someone else will respond.

You probably need code when you select ranges also, either selecting the sheet first before telling it the range or a with statement specifying with ws.range etc etc.

Code: 
Sub Worksheets_unhide() ' this is not working- the loops and unhiding columns for all ws except1st
Dim MyRange As Range

Dim ws As Worksheet  'YOU HAVE DECLARED A WORKSHEET VARIABLE, BUT DID NOT USE IT LATER IN CODE.

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

Set ws = Worksheets(LoopIndex) 'THIS MIGHT WORK.

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

 
Last edited:
Upvote 0
Welcome to the board. Try these two bits of code, the first one is to unhide columns, copy Q6:R21 to L6:M21 and clear contents of O6:O21:
Code: 
Sub Worksheets_Unhide_1()

Dim ws As Worksheet

Application.ScreenUpdating = False

For Each ws In ActiveWorkbook.Worksheets
    With ws
        Select Case .Name
            Case "Start", "End"
                'Do Nothing
            Case Else
                If .Range("Q:R").EntireColumn.Hidden Then .Range("Q:R").EntireColumn.Hidden = False
                .Range("L6:M21").Value = .Range("Q6:R21").Value
                .Range("O6:O21").ClearContents
        End Select
    End With
Next ws

Application.ScreenUpdating = True

End Sub
And this code is to hide columns Q:R in any sheet that doesn't have the name Start or End.
Code: 
Sub Worksheets_Hide_1()

Dim ws As Worksheet

Application.ScreenUpdating = False

For Each ws In ActiveWorkbook.Worksheets
    With ws
        Select Case .Name
            Case "Start", "End"
                'Do Nothing
            Case Else
                On Error Resume Next
                .Range("Q:R").EntireColumn.Hidden = True
                On Error GoTo 0
        End Select
    End With
Next ws

Application.ScreenUpdating = True

End Sub
 
Upvote 0
Thanks JackDanIce. It is working, however it is executing the code on all the worksheets, how can I omit any worksheets that are outside the start and end tabs. For example I will have couple tabs before "start" tab and also couple other worksheets after the "end" tab that I do not want the code to execute.
 
Upvote 0
The SELECT CASE statement should do nothing for worksheets named "Start" and "End" and do the ELSE parts on all other worksheets; works on a test copy of mine. Check the case of the worksheet names, I used "Start" but yours may say "start" - if so adjust my code.
 
Upvote 0
Thanks. It does work, but I want to also make sure that these changes are NOT executed on not on the sheets that say Start and End but also anything outside of those tabs. I have 70 sheets between "Start" and "End" and have may be 3-4 before "Start" and 4-5 after "end" and the numbers outside the start and end may vary and want to exclude them from this exercise.
 
Upvote 0
Not tested but another suggestion perhaps:

Code: 
Sub Worksheets_unhide()
    Dim i As Integer
    For i = Sheets("Start").Index + 1 To Sheets("End").Index - 1
        With Worksheets(i)
            .Columns("Q:R").EntireColumn.Hidden = False
            .Range("Q6", "R21").Copy .Cells(6, 5)
            .Range("O6:O21").ClearContents
        End With
    Next
End Sub

and to hide:

Code: 
Sub Worksheets_hide()
    Dim i As Integer
    For i = Sheets("Start").Index + 1 To Sheets("End").Index - 1
    Worksheets(i).Columns("Q:R").EntireColumn.Hidden = True
    Next
End Sub

Dave
 
Last edited:
Upvote 0
How do I go about copying and pasting a dynamic range? For example say I want to copy from Q6 and R6 through the last cell of data, how would I incorporate that into the code above? I know that I cannot have WITH Lastcell,copy - but what should I have instead? Attached below is dmt32's code modifiedSub Worksheets_setup()
Application.ScreenUpdating = False
Dim Lastcell As Range
Set Lastcell = Range("Q6", "R6").End(xlDown)
Dim i As Integer
For i = Sheets("Start").Index + 1 To Sheets("End").Index - 1
With Worksheets(i)
.Columns("Q:R").EntireColumn.Hidden = False
Lastcell.Copy .Range("L6", "M21")
.Range("O6:O21").ClearContents
End With
Next
Application.ScreenUpdating = True
End Sub
 
Upvote 0
I've modified my previous code. For the last row in Q, try:
Code: 
Sub Worksheets_Unhide_2()

Dim i As Long, j As Long

Application.ScreenUpdating = False

For i = Sheets("Start").Index + 1 To Sheets("End").Index - 1
    With Sheets(i)
        If .Range("Q:R").EntireColumn.Hidden Then .Range("Q:R").EntireColumn.Hidden = False
        j = .Range("Q" & .Rows.Count).End(xlUp).Row
        .Range("L6:M" & j).Value = .Range("Q6:R" & j).Value
        .Range("O6:O21").ClearContents
    End With
Next i

Application.ScreenUpdating = True

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

Nuke_It_Newport
  • Solved
Looping through sheets, Searching for duplicate in column using ".Find"
2
Replies
14
Views
1K
smozgur
smozgur
A
  • Solved
Excel Macro to get Email Subject of mails received today from outlook
Replies
9
Views
3K
atuljadhavnetafim
A
Z
  • Solved
VBA Code to Hide columns in Specific Intervals
2
Replies
11
Views
364
jolivanes
jolivanes
H
  • Solved
Copy File Names in Folder starting from 001_ to 010_
Replies
1
Views
175
howard
H
B
  • Solved
VBA vlookup from another sheet?
Replies
5
Views
120
Joe4
Joe4

Forum statistics

Threads
1,214,375
Messages
6,119,164
Members
448,870
Latest member
max_pedreira

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top