Excel VBA looping - A VBA beginner

[DianaDSN]

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

 

[DianaDSN]

Thanks this is wonderful. Can you please tell me why did you use I and j as long instead of integer? And what does this code mean in simple sentences?
j = .Range("Q" & .Rows.Count).End(xlUp).Row
.Range("L6:M" & j).Value = .Range("Q6:R" & j).Value

 

[JackDanIce]

i is the variable I'm using to count through the number of worksheets in your workbook. So in your code, you have

For i = Sheets("Start").Index + 1 to Sheets("End").Index - 1

If the index number (not necessarily the order the sheets are in) for the "Start" sheet, is say 1, then the loop starts at 2. When you use Sheets(i) and i = 2, it will start at the sheet with index number 2.

In loops, unless you specify otherwise, i increments by 1 each time it loops, i.e. it increases sequentially, so Sheets(i) will cycle through each sheet one by one, until i reaches the end number which is Sheets("End").Index - 1.

I'm using j to find the row number of the last used row on Sheets(i), column Q; i maintains the count of the worksheets, but j stores the row number of the last used row within each sheet it loops through. Otherwise Sheets(j) may be a very different sheet to Sheets(i)!

Does that help?

 

[DianaDSN]

Great! Thanks for the explanation.

 

[dmt32]

Thanks Dave it is working now with the code only selecting the sheets that I want.

Your welcome -glad idea worked ok - I see JackDanIce has developed the solution further for you.

Dave

 

[JackDanIce]

Missed the part of long vs integer. Excel 2003(? can't remember which version), converts integer data types into long data types, in effect, integer data types are redundant with modern Excel versions so I use long for any variable that needs to hold a whole number. See: The Integer, Long, and Byte Data Types for further information on this.

 

[DianaDSN]

Thanks and I am back with yet another question. So why did you use xlUp instead of xlDown. I was thinking that if I know that I am going to select from Q6, then I have to go xlDown and not UP- so what was the thought behind that?. Also If I want to copy and paste another range along with the code that you gave me, do I just declare k as another range and write a code for selection? Also in the clear contents section , what if I want to clear all contents that are NOT formulas, and if they are formulas keep the formulas. Also the range that I want to declare for k has to be copied and pasted first before I clear contents. Is it a matter of placing the code to copy and paste above the code to clear contents or do I have to create another sub procedure to clear contents after the sub to unhide? I hope this was not too confusing. I greatly appreciate your help while I am learning.

 

[DianaDSN]

I understood that End(xlUP) is used commonly to determine the last used row or column with data while End(xlDown) is used to get the last cell before blank in a column. Does that mean that in the code above I can use End(xlDown) instead of Up as well, or because two columns are being selected, we have to select the last row?

 

[DianaDSN]

Thanks and I am back with yet another question. So why did you use xlUp instead of xlDown. I was thinking that if I know that I am going to select from Q6, then I have to go xlDown and not UP- so what was the thought behind that?. Also If I want to copy and paste another range along with the code that you gave me, do I just declare k as another range and write a code for selection? Also in the clear contents section , what if I want to clear all contents that are NOT formulas, and if they are formulas keep the formulas. Also the range that I want to declare for k has to be copied and pasted first before I clear contents. Is it a matter of placing the code to copy and paste above the code to clear contents or do I have to create another sub procedure to clear contents after the sub to unhide? I hope this was not too confusing. I greatly appreciate your help while I am learning.

Option Explicit

Sub Worksheets_Setup()

Dim i As Long, j As Long, k As Long

Application.ScreenUpdating = False 'this avoids showing the update in the macro

For i = Sheets("Start").Index + 1 To Sheets("End").Index - 1 'looping through sheet after start and ending at sheet before end
With Sheets(i)
If .Range("L:M").EntireColumn.Hidden Then .Range("L:M").EntireColumn.Hidden = False
j = .Range("H" & .Rows.Count).End(xlUp).Row
.Range("L13:M" & j).Value = .Range("H13:I" & j).Value 'copying cum amtfrom H13:I to L13:M
k = .Range("F" & .Rows.Count).End(xlUp).Row
.Range("D13:E" & k).Value = .Range("F13:G" & k).Value ' copying from curr per to prior per
If .Range("F13", "G41").HasFormula = False Then .ClearContents 'clearing contents from Curr per if there are no formulas
End With
Next i

Application.ScreenUpdating = True

End Sub

 

[DianaDSN]

The above code has been modified to fit the real world situation that I am dealing with and it is giving me Run time error '9' where I start "With" - does anyone know why?

 

[skywriter]

The above code has been modified to fit the real world situation that I am dealing with and it is giving me Run time error '9' where I start "With" - does anyone know why?

If you are interested I've done a little research and I have a couple of things you can try that may be the problem.

From what I've found this error is usually occurs when you tell Excel to select or activate something that doesn't exist.

First possible problem would be if the name of one of your sheets has an extra space in it which would not be all that noticeable by looking at the tab.

So I did a test. I named a sheet Start just like you have, but I put a space at the end and hit enter.
I then wrote code to activate Worksheets("Start") and I got the error you got. I took the space out of the sheet name and it worked fine. So I would start by double checking the sheet names and also the code to make sure one of the other isn't exactly as the other, spelling and spaces.

The other thing I found out and thanks to your problem I learned something I didn't know.

In your code you use the words Sheets(i).

When you in code refer to Worksheets(i) you are referring to worksheets only.

When you in code refer to Sheets(i) you are referring to all worksheets, all chart sheets and I guess there are some other types of sheets, but I'm not familiar with them.

Here's the significance:
I did some testing. I have a workbook with 17 worksheets in it.
I added a chart sheet.

When I did worksheets.count the number returned was 17.
When I did sheets.count the number returned was 18.

When you refer to worksheets in your code by index number, example Worksheets(1).Activate the index number is decided by the order of the tabs in your spreadsheet. Far left is 1 far right is the highest number. Move a tab from the last position to the first position it becomes index #1.

What I thought I might find I did find.

I looped throught the worksheets one at a time by index number using worksheets(index) and it skipped right over the chart sheet.

I looped through the sheets in the same manner using Sheets(index) and this time it activated the chart sheet.

I selected the chart sheets again by using the index number in my case 5, using Sheets(5).Activate then

Range("A1").Select and there you go again the same error you got because you can't select a range on a chart sheet.

If you have something other than a worksheet between you Start and End sheet this may be your problem. Change Sheets(i) to Worksheets(i) and your problem should go away.

My suggestion and something I'm going to do in the future is make sure when I want to loop through worksheets only and count worksheets only I use Worksheets.Count and Worksheets(Index Number) only.

I sure hope one of these is your problem. If not I'm at a loss to help you any further. Good luck.