runtime 1004 object worksheet not defined error

[jordanburch]

Hey guys

I have the following code

' Sheets.Add.Name = "CRIS"
Sheets.Add.Name = "CRIS"
  Dim Xrow As Long, WS As Worksheet, ws2 As Worksheet, dng As Range, dng2 As Range, dng3 As Range, dng4 As Range, dng5 As Range, dng6 As Range, dng7 As Range, dng8 As Range, dng9 As Range, dng10 As Range, dng11 As Range, dng12 As Range, dng13 As Range, dng14 As Range, dng15 As Range
    Xrow = Cells(Rows.Count, "A").End(xlUp).Row


    Set WS = ThisWorkbook.Worksheets("CRIS")
    Set ws2 = ThisWorkbook.Worksheets("MAY FY20 IDARRS")
    Set dng = WS.Range("af2:af" & Xrow)
    Set dng2 = WS.Range("ag2:ag" & Xrow)
    Set dng3 = WS.Range("ah2:hg" & Xrow)
    Set dng4 = WS.Range("ai2:ig" & Xrow)
    Set dng5 = WS.Range("aj2:jg" & Xrow)
    Set dng6 = WS.Range("ak2:kg" & Xrow)
    Set dng7 = WS.Range("al2:al" & Xrow)
    Set dng8 = ws2.Range("ao2:ao", Xrow)
    Set dng9 = ws2.Range("ap2:ap", Xrow)
    Set dng10 = ws2.Range("aq2:aq", Xrow)
    Set dng11 = ws2.Range("ar2:ar", Xrow)
    Set dng12 = ws2.Range("as2:as", Xrow)
    Set dng13 = ws2.Range("at2:at", Xrow)
    Set dng14 = ws2.Range("au2:au", Xrow)
    Set dng15 = ws2.Range("av2:av", Xrow)
    Set dng16 = ws2.Range("aw2,aw", Xrow)

its erroring out on the dng8 line. I believe I have to set the xrow for that sheet since im using the xrow in two different sheets. How do I do that in the code? or is that my problem at all?

Jordan

 

[RoryA]

For that line and all the ones after you need to replace the comma with an ampersand:

Set dng8 = ws2.Range("ao2:ao" & Xrow)

rather than:

Set dng8 = ws2.Range("ao2:ao", Xrow)

​

 

[jordanburch]

For that line and all the ones after you need to replace the comma with an ampersand:

Set dng8 = ws2.Range("ao2:ao" & Xrow)

rather than:

Set dng8 = ws2.Range("ao2:ao", Xrow)

duH!!!!!!!!!!!!!!!!!!!!!!!! thanks Rory. I dont know why i wrote it like that and didnt catch it sometimes it the most obvious issue.Thanks again!

 

[RoryA]

It's often hard to see what you actually wrote rather than what you meant...

 

[jordanburch]

It's often hard to see what you actually wrote rather than what you meant...

yes if only we could simply talk to excel and tell it what we want and then it would write the code for us hahahah!