Need to run a Do While loop across multiple worksheets

1
votes

I have been trying to run the same Do While loop function across multiple worksheets in a workbook and compile the data in another worksheet. The code works for the one worksheet that is specified but how do I get it to work across the others that are in the workbook at the same time? Also worth mentioning that I only want it to run on some of the worksheets not all that are in the workbook (sheets are named as years - 2014, 2015 etc).

This is the code

Sub Total_Button1_Click()

Dim i As Integer

Dim strSheetFrom As String

Dim j As Integer
Dim strSheetTo As String


i = 3
j = 2

strSheetFrom = "2014"
strSheetTo = "Total"

Do While Trim(Sheets(strSheetTo).Range("B" & CStr(j)).Text) <> ""
    j = j + 2
Loop

Do While Trim(Sheets(strSheetFrom).Range("B" & CStr(i)).Text) <> ""
    If UCase(Trim(Sheets(strSheetFrom).Range("A" & CStr(i)).Text)) = "Y" Then
        Sheets(strSheetTo).Range("B" & j & ":G" & j).Value = Sheets(strSheetFrom).Range("B" & i & ":G" & i).Value
        Sheets(strSheetTo).Range("H" & j & ":I" & j).Value = Sheets(strSheetFrom).Range("I" & i & ":J" & i).Value
        Sheets(strSheetTo).Range("J" & j & ":J" & j).Value = Sheets(strSheetFrom).Range("L" & i & ":L" & i).Value
        Sheets(strSheetTo).Range("K" & j & ":K" & j).Value = Sheets(strSheetFrom).Range("Q" & i & ":Q" & i).Value
        Sheets(strSheetTo).Range("L" & j & ":AH" & j).Value = Sheets(strSheetFrom).Range("s" & i & ":AO" & i).Value
        j = j + 1
    End If
    i = i + 1
Loop

MsgBox "Total book created"

End Sub

2
excelvba
What are the conditions that need to be met by the WorkSheet names in order for the code to know that it should run on that worksheet? Between a start and end year? any worksheet with 4 numbers for a name? – user2140261

2 Answers

1
votes

Try making your strSheetFrom variable an array something like this:

strSheetFrom = new strSheetFrom[3] 
strSheetFrom[2] = "2012"
strSheetFrom[1] = "2013" 
strSheetFrom[0] = "2014"

Then put your code into another loop like so:

dim w as integer
for w = 0 To 3

Do While Trim(Sheets(strSheetTo).Range("B" & CStr(j)).Text) <> ""
j = j + 2
Loop

Do While Trim(Sheets(strSheetFrom[w]).Range("B" & CStr(i)).Text) <> ""
If UCase(Trim(Sheets(strSheetFrom[w]).Range("A" & CStr(i)).Text)) = "Y" Then
    Sheets(strSheetTo).Range("B" & j & ":G" & j).Value = Sheets(strSheetFrom[w]).Range("B" & i & ":G" & i).Value
    Sheets(strSheetTo).Range("H" & j & ":I" & j).Value = Sheets(strSheetFrom[w]).Range("I" & i & ":J" & i).Value
    Sheets(strSheetTo).Range("J" & j & ":J" & j).Value = Sheets(strSheetFrom[w]).Range("L" & i & ":L" & i).Value
    Sheets(strSheetTo).Range("K" & j & ":K" & j).Value = Sheets(strSheetFrom[w]).Range("Q" & i & ":Q" & i).Value
    Sheets(strSheetTo).Range("L" & j & ":AH" & j).Value = Sheets(strSheetFrom[w]).Range("s" & i & ":AO" & i).Value
    j = j + 1
End If
i = i + 1
Loop

w -= 1
next

I haven't tested it, but something like that. You get the idea.

0
votes

Use a For Each and iterate over the Worksheet collections like this

'Variables
Dim useWorkSheet As Worksheet
Dim totalWorkSheet As Worksheet
Dim yearAsNumeric As Integer
Dim startingYear As Integer

'Settings
startingYear = 2014

'To reference the total worksheet so we can work with it
Set totalWorkSheet = ActiveWorkbook.Worksheets("Total")

'Iterate over each item in the collection
For Each useWorkSheet In ActiveWorkbook.Worksheets
    'Force the name into a numeric value.  If it starts with anything non numeric (A-Z|a-z|$,#,etc) then it will return 0
    yearAsNumeric = Val(useWorkSheet.Name)

    'Greater than or equal to the year we want to start with?
    If yearAsNumeric >= startingYear Then
        'Yes. Do your stuff here
        useWorkSheet.Name
    End If
Next