0

I just wanna ask about how the loop workflow and why does my code doesn't work.

Basically I need to loop to create a Pivot Data Table from each worksheet in a workbook. 

WS_Count = ActiveWorkbook.Worksheets.Count
For i = 1 To WS_Count
    lastRow = Range("IB" & Rows.Count).End(xlUp).Row
    ActiveCell.FormulaR1C1 = "=IF(AND(RC[-1]<=2,RC[-1]>=0),""Ok"",""Failed"")"
    Range("ID2").Select
    Selection.AutoFill Destination:=Range("ID2:ID" & lastRow)
    If i Mod 2 = 0 Then
        SrcData = ActiveSheet.Name & "!" & Range("A1:IF" & lastRow) _
            .Address(ReferenceStyle:=xlR1C1)
        Set sht = Sheets.Add
        StartPvt = sht.Name & "!" & sht.Range("A1:C3") _
            .Address(ReferenceStyle:=x1R1C1)
        Set pvtCache = ActiveWorkbook.PivotCaches _
            .Create(SourceType:=xlDatabase, SourceData:=SrcData)
        Set pvt = pvtCache.CreatePivotTable(TableDestination:=StartPvt, _
            TableName:="PivotTable1")
End if
Next i

What is happening to my code is that this loop assigns everything that I wanted to assign using that formula, but the for the IF part it always runs the code. So after the third loop, it will give an error message said that pivot talbe source is undefined because the Activesheet is empty and no data in it.

What I need is, is my code right? Or, how do I make the IF statement work only if it's correct?

Yohanes Lim
  • 95
  • 10
  • 3
    Welcome! You need to include a [mcve] with your question. Your example is not "verifiable" since it's missing key parts... like an "`End If`", for example. How do you define "crash"? Are you getting an error? See "[ask]" as well as these [tips](http://codeblog.jonskeet.uk/2012/11/24/stack-overflow-question-checklist), and the [help/on-topic] for more information about what's on-topic on this site. You can [edit] your questions as needed. – ashleedawg Sep 27 '18 at 03:28
  • sorry got cut out when i tried to leave the important part of the code. i've edited my code. – Yohanes Lim Sep 27 '18 at 03:58
  • `If i Mod 2 = 0 Then` means run the code within the `If` for all even sheet numbers. So if `WS_Count = 10` it will run on `i = 2, 4, 6, 8 and 10` also you never use that `i` so it's very unclear to me what you are trying to do at all. So the `If` statement definitely does what it should do. – Pᴇʜ Sep 27 '18 at 06:25
  • thanks peh for your response. i just realise the problem that i had. my loop is not moving the active worksheet, so when it loop, it stays in the added sheet. do you know how to move the active sheet to the next sheet? thanks for your help, appreciate it. – Yohanes Lim Sep 27 '18 at 06:39
  • Avoid using `ActiveSheet`, `.Activate` and `.Select` at all. This is a bad practice and results in errors like you described in your question. Read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and apply this technique to your code to avoid any errors and always reference the correct worksheet. – Pᴇʜ Sep 28 '18 at 08:27

0 Answers0