CODEWITHSUNDEEP
excelvba
jonathan marin
jonathan marin

Reputation: 9

Null Error When using If statements to loop through specific sheets

I get error message of "Cannot enter a null value as an item or field name in a pivot table report"

   Sub loopthruallsheets()

    Dim Shts() As Variant
    Dim Sheet As Worksheet


   Shts = Array("1", "3", "5", "7")

 For Each Sheet In ActiveWorkbook.Worksheets
 With Sheet
    If .Range("G2").Value = 1 Then
        .Range("H10:H11").Value = .Range("N10:N11").Value
        .Range("H14:H22").Value = .Range("N14:N22").Value
        .Range("H27:H29").Value = .Range("N27:N29").Value
        End If
    If .Range("G2").Value = 2 Then
        .Range("H10:H11").Value = .Range("O10:O11").Value
        .Range("H14:H22").Value = .Range("O14:O22").Value
        .Range("H27:H29").Value = .Range("O27:O29").Value
        End If
     If .Range("G2").Value = 3 Then
        .Range("H10:H11").Value = .Range("P10:P11").Value
        .Range("H14:H22").Value = .Range("P14:P22").Value
        .Range("H27:H29").Value = .Range("P27:P29").Value
        End If
     If .Range("G2").Value = 4 Then
        .Range("H10:H11").Value = .Range("Q10:Q11").Value
        .Range("H14:H22").Value = .Range("Q14:Q22").Value
        .Range("H27:H29").Value = .Range("Q27:Q29").Value
        End If
    If .Range("G2").Value = 5 Then
        .Range("H10:H11").Value = .Range("R10:R11").Value
        .Range("H14:H22").Value = .Range("R14:R22").Value
        .Range("H27:H29").Value = .Range("R27:R29").Value
        End If
    If .Range("G2").Value = 6 Then
        .Range("H10:H11").Value = .Range("S10:S11").Value
        .Range("H14:H22").Value = .Range("S14:S22").Value
        .Range("H27:H29").Value = .Range("S27:S29").Value
        End If
    If .Range("G2").Value = 7 Then
        .Range("H10:H11").Value = .Range("T10:T11").Value
        .Range("H14:H22").Value = .Range("T14:T22").Value
        .Range("H27:H29").Value = .Range("T27:T29").Value
        End If
    If .Range("G2").Value = 8 Then
        .Range("H10:H11").Value = .Range("U10:U11").Value
        .Range("H14:H22").Value = .Range("U14:U22").Value
        .Range("H27:H29").Value = .Range("U27:U29").Value
        End If
    If .Range("G2").Value = 9 Then
        .Range("H10:H11").Value = .Range("V10:V11").Value
        .Range("H14:H22").Value = .Range("V14:V22").Value
        .Range("H27:H29").Value = .Range("V27:V29").Value
        End If
    If .Range("G2").Value = 10 Then
        .Range("H10:H11").Value = .Range("W10:W11").Value
        .Range("H14:H22").Value = .Range("W14:W22").Value
        .Range("H27:H29").Value = .Range("W27:W29").Value
        End If
    If .Range("G2").Value = 11 Then
        .Range("H10:H11").Value = .Range("X10:X11").Value
        .Range("H14:H22").Value = .Range("X14:X22").Value
        .Range("H27:H29").Value = .Range("X27:X29").Value
        End If
    If .Range("G2").Value = 12 Then
        .Range("H10:H11").Value = .Range("Y10:Y11").Value
        .Range("H14:H22").Value = .Range("Y14:Y22").Value
        .Range("H27:H29").Value = .Range("Y27:Y29").Value

         End If
       End With
         Next Sheet
      End Sub

I need the code to copy and paste value in a specific place but with each different period (months1-12) its a different column of data. My code errors out at Period 4 ( which is my current period for our fiscal year)

Upvotes: 0

Views: 79

Answers (2)

Rafał B.
Rafał B.

Reputation: 404

This is @BruceWayne answer with your array 1/3/5/7 - (names, not indexes)

 Sub loopthruallsheets()
 Dim Shts() As Variant
 Dim Sheet As Variant
 Dim startCol As Long
    startCol = 13 ' 13 is Column M
 Dim celVal As Long

 Shts = Array("1", "3", "5", "7")

 For Each Sheet In Shts
    With Worksheets(Sheet)
    celVal = .Range("G2").Value
    .Range("H10:H11").Value = .Range(.Cells(10, startCol + celVal), .Cells(11, startCol + celVal)).Value
    .Range("H14:H22").Value = .Range(.Cells(14, startCol + celVal), .Cells(22, startCol + celVal)).Value
    .Range("H27:H29").Value = .Range(.Cells(27, startCol + celVal), .Cells(29, 
  startCol + celVal)).Value
    End With
  Next Sheet
  End Sub

Upvotes: 0

BruceWayne
BruceWayne

Reputation: 23283

There's no real need to have that many If statements (or use Select Case). This is just a pattern you can use with Cells() instead of just Range():

Sub t()
Dim sheet As Worksheet
Dim startCol As Long, celVal As Long, i As Long
Dim shts() As Variant

startCol = 13 ' 14 is Column M

shts = Array(1, 3, 5, 7)

For i = LBound(shts) To UBound(shts)
    With ActiveWorkbook.Worksheets(shts(i))
        celVal = .Range("G2").Value
        .Range("H10:H11").Value = .Range(.Cells(10, startCol + celVal), .Cells(11, startCol + celVal)).Value
        .Range("H14:H22").Value = .Range(.Cells(14, startCol + celVal), .Cells(22, startCol + celVal)).Value
        .Range("H27:H29").Value = .Range(.Cells(27, startCol + celVal), .Cells(29, startCol + celVal)).Value
    End With
Next i

End Sub

From your comment, I also tweaked the For loop to instead just loop through the specific sheets you want. (I also assumed that 1, 3, 5, 7 are the sheet indexes you want to use, not actual sheet name. If sheets are indeed named 1, 3, etc. then use shts = Array("1", "3", "5", "7"))

Upvotes: 4

Related Questions