Move sheet to a new workbook based on C3's contents?

[poptart141]

I have a workbook that has multiple sheets related to current projects my department is working on. Whenever cell C3 in one of these sheets says "Complete" or "Archived," I need that whole sheet to be moved to another workbook, called "CPTS Complete & Archived." Here's the code I have so far, which isn't doing anything:

Sub MoveSheets()
   Dim NewWorkbook As Workbook
   Set NewWorkbook = Workbooks("CPTS Complete & Archived.xlsx")
    
   If InStr(1, ActiveSheet.Range("C3").Value, "Complete" Or "Archived") > 0 Then
      Application.Workbooks.Open ("C:\Documents and Settings\username\Desktop\CPTS\CPTS Complete & Archived.xlsx")
      ActiveSheet.Move After:=NewWorkbook.Sheets(NewWorkbook.Sheets.Count)
      Workbooks("CPTS Complete & Archived.xlsx").Save
      Workbooks("CPTS Complete & Archived.xlsx").Close
  End If
End Sub

Can someone tell me what I can do to make that work?

 

[John Davis]

Not sure if you wanted to continuously override the Workbook "CPTS Complete & Archived"? This will only add too it.

Sub poptart141()
'
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

    ws.Activate
    
Set ws = ActiveSheet
    
        If ws.Range("C3").Value = "Complete" Or ws.Range("C3").Value = "Archived" Then
        
            ws.Cells.Copy
            Workbooks.Open ("C:\Documents and Settings\username\Desktop\CPTS\CPTS Complete & Archived.xlsx")
            Sheets.Add
            ActiveSheet.Range("A1").Select
            ActiveSheet.Paste
            ActiveSheet.Move After:=Workbooks("CPTS Complete & Archived.xlsx").Sheets(Sheets.Count)
            Workbooks("CPTS Complete & Archived.xlsx").Close True

        End If
        
Next ws

End Sub

 

[poptart141]

Thanks for the reply, John!

I tried the code you provided, but nothing happens, still. I change C3 in any of the sheets in the current workbook to "Complete" or "Archived," and nothing happens. It just sits there. I really don't know why it's not doing anything at all...