VBA errors in Excel

[that_one_girl]

Hello,

Below is my code. I have a workbook with 3 sheets "ALL RECORDS, ACTIVE, ARCHIVE", and I am trying to get the VBA to move the rows in the ALL RECORDS sheet that have "CURRENET" in Column J to the ACTIVE sheet, and rows that have "ARCHIVE" in Column J to the ARCHIVED sheet.......I am getting an error
"Compile Error: For without Next"

Can anyone help?

Private Sub Workbook_Open()
Dim i, LastRow
LastRow = Sheets("ALL RECORDS").Range("A" & Rows.Count).End(xlUp).Row
Sheets("ACTIVE").Range("A2:L60869").ClearContents
Sheets("ARCHIVE").Range("A2:L60869").ClearContents
For i = 2 To LastRow
If Sheets("ALL RECORDS").Cells(i, "J").Value = "CURRENT" Then
Sheets("ALL RECORDS").Cells(i, "J").EntireRow.Copy Destination:=Sheets("ACTIVE").Range("A" & Rows.Count).End(xlUp).Offset(1)

ElseIf Sheets("ALL RECORDS").Cells(i, "J").Value = "ARCHIVE" Then
Sheets("ALL RECORDS").Cells(i, "J").EntireRow.Copy Destination:=Sheets("ARCHIVE").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
End Sub

 

[igold]

Hi that_one_girl,

Welcome to the MrExcel Board.

I have not tested your code but to fix your error you need to add a NEXT line as shown below:

Private Sub Workbook_Open()


    Dim i, LastRow
    LastRow = Sheets("ALL RECORDS").Range("A" & Rows.Count).End(xlUp).Row
    Sheets("ACTIVE").Range("A2:L60869").ClearContents
    Sheets("ARCHIVE").Range("A2:L60869").ClearContents
    For i = 2 To LastRow
    If Sheets("ALL RECORDS").Cells(i, "J").Value = "CURRENT" Then
        Sheets("ALL RECORDS").Cells(i, "J").EntireRow.Copy Destination:=Sheets("ACTIVE").Range("A" & Rows.Count).End(xlUp).Offset(1)
    ElseIf Sheets("ALL RECORDS").Cells(i, "J").Value = "ARCHIVE" Then
        Sheets("ALL RECORDS").Cells(i, "J").EntireRow.Copy Destination:=Sheets("ARCHIVE").Range("A" & Rows.Count).End(xlUp).Offset(1)
    End If
[COLOR=#ff0000]    Next[/COLOR]


End Sub

I hope this helps.

 

[that_one_girl]

I entered that and now it says Runtime error "9": Subscript out of range

 

[Joe4]

I entered that and now it says Runtime error "9": Subscript out of range

While line of code is returning that error?
If you hit Debug, it should highlight it.

 

[that_one_girl]

It worked!! THANK YOU!

My only question now is, will this code run everytime I open the workbook? Or do I need to create an active control button?

 

[Joe4]

This is an Event Procedure. Events Procedures are designed to run automatically upon some event happening.
If you have pasted this code in the "ThisWorkbook" module, and Macros are enabled when you open it, it will run every time you open it.

To read up more on Event Procedures, see: Events In Excel VBA

 

[igold]

Great, I am glad you got it working.