VBA Worksheet Loops

Question

I'm currently working with a workbook containing 34 different tabs. I'm trying to extract Monthly Data from each of the tabs and Transpose it into daily figures for each specific city. I have put all the dates within the year 2019 as columns in order to present it as daily figures. (See example in img below)

Each tab contains data for each specific city. I always want to extract the data present on row 20 from column 4 to 15 in each tab for each specific city. ( see 2nd image below highlighted in yellow)

Public Sub CreateArray()



Dim myArrayOfMonths(11) As Double

Dim currentWorkbook As Workbook
Dim currentSheet As Worksheet
Dim otherSheet As Worksheet

Dim i As Integer

Dim r As Integer
Dim c As Integer
Dim j As Integer


Set currentWorkbook = ActiveWorkbook
Set otherSheet = currentWorkbook.Worksheets("Output")

i = 1

For Each currentSheet In currentWorkbook.Worksheets

r = 20
j = 0

For c = 4 To 15

    myArrayOfMonths(j) = ActiveSheet.Cells(r, c)
    
    j = j + 1

Next c

Debug.Print myArrayOfMonths(0)

   
  i = i + 1

Next currentSheet


Set currentSheet = Nothing
Set currentWorkbook = Nothing







End Sub

In my code I'm trying to run through all of the tabs with a loop and with a 2nd loop check the date (row 16, column 4 to 15) and extract it on my template (Similiar to a vlookup) Unfortunately, it never passes through the first tab as i=0 always for some reason.

Could you please advise?

You set `i = 1` at a few places, but never use `i` in your code. Also get rid of the `Activesheet` see: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — Luuklag, Apr 26 '19 at 13:34
Do you realise that this `ActiveSheet` never changes - is this the cause of the problem? You should probably be referring to `currentSheet`. — SJR, Apr 26 '19 at 13:34
@Luuklag I ve tried incremeting the i but it still outputs the same thing — David Gorgan, Apr 26 '19 at 13:40
@DavidGorgan ofcourse, you don't use the `i` anywhere in your code to reference worksheets. So incrementing does nothing but changing the value of `i`. — Luuklag, Apr 26 '19 at 13:41
Keep in mind that as you go through all the sheets, eventually your `currentSheet` is going to equal the `otherSheet`. You may want to qualify that. — Darrell H, Apr 26 '19 at 13:49
Your dates are screwy. On your output tab you are using Day/Month/Year, and showing dates daily. On your city data tab, your row 2 dates appear to be in Day/Month/Year, but Row 16 dates are in Month/Day/Year. Pet-Peeve: There should only be one date format Year-Month-Day Hour:Minute:Second. — JosephC, Apr 26 '19 at 18:16

score 0 · Answer 1 · answered Apr 26 '19 at 14:02

0

Would you be able to do something like this?

Option Explicit

Public Sub PopulateOutput()

    Dim outputSheet As Worksheet
    Dim i As Integer

    Set outputSheet = ActiveWorkbook.Worksheets("Output")

    ' starting at index 2 since output sheet looks like index 1
    For i = 2 To ActiveWorkbook.Worksheets.Count
        With ActiveWorkbook.Worksheets(i)
            outputSheet.Range("B" & i & ":M" & i).Value = .Range("D20:O20").Value
        End With
    Next

End Sub

answered Apr 26 '19 at 14:02

Kubie

1,551
3
12
23

Thanks for the effort, but I need the same figure for january all across the january dates, then the same for february and so on, similiar to a vlookup. Thank you for try though! – David Gorgan Apr 26 '19 at 14:14

score 0 · Answer 2 · answered Apr 26 '19 at 14:18

Does this suit your needs?

Public Sub CreateArray()

    Dim myArrayOfMonths(11) As Double

    Dim currentWorkbook As Workbook
    Dim currentSheet As Worksheet
    Dim otherSheet As Worksheet

    Dim r As Integer
    Dim c As Integer

    Set currentWorkbook = ActiveWorkbook
    Set otherSheet = currentWorkbook.Worksheets("Output")

    For Each currentSheet In currentWorkbook.Worksheets

        r = 20

        For c = 4 To 15
            myArrayOfMonths(c - 4) = myArrayOfMonths(c - 4) + currentSheet.Cells(r, c)
        Next c

    Next currentSheet

    otherSheet.Range("B1:M1").Value = myArrayOfMonths

    Set currentSheet = Nothing
    Set currentWorkbook = Nothing

End Sub

Blaieet · Answer 3 · 2019-04-26T13:58:55.747

-2

Use currentSheet.Cells(r,c) instead of ActiveSheet

or use currentSheet.Activate and then myArrayOfMonths(j) = ActiveSheet.Cells(r, c), but try to avoid ActiveSheet.

edited Apr 26 '19 at 13:58

answered Apr 26 '19 at 13:47

Blaieet

21
3

1

Please avoid the use of .Activate It is error-prone and computationally heavy. See https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba for alternatives – Luuklag Apr 26 '19 at 13:49

VBA Worksheet Loops

3 Answers3