Last sheet does not follow the code

[ukanez]

Hi everyone, I have a workbook that has 12 worksheets for each province. I'm trying to select a row based on a value of a cell, cut that row, and insert it before another row lower in the worksheet.

I'm trying to do this for each worksheet in the workbook and have been having problems because the code executes with no errors, but the final sheet in the workbook remains unchanged. Thanks again for your help.

Application.ScreenUpdating = False
Dim rng1, cell, cella As Range
Dim ws As Worksheet

Workbooks("Provincial - INTERNAL" & ".xls").Activate

     Sheets(Array("BC", "AB", "SK", "MB", "Prairies", "ON", "QC", "NB", "PE", "NS", "NF", "Atlantic")).Copy
  
 For Each ws In ActiveWorkbook.Worksheets
     Set rngA = Range("C1", Range("C65536").End(xlUp))
         For Each cell In rngA
             If cell.Value = "A" Then
                 cell.EntireRow.Cut
             End If
         Next cell
         For Each cella In rngA
             If cella.Value = "B" Then
                 cella.EntireRow.Insert Shift:=xlDown
             End If
         Next
 With ws.UsedRange
     .Value = .Value
  
 End With
     Application.Goto ws.Cells(1, 1)
 Next

 

[ukanez]

As you only have one A & one B, lets try a different tack.

Dim FndA As Range
Dim FndB As Range
Dim ws As Worksheet

Workbooks("Provincial - INTERNAL" & ".xls").Activate

     Sheets(Array("BC", "AB", "SK", "MB", "Prairies", "ON", "QC", "NB", "PE", "NS", "NF", "Atlantic")).Copy
  
 For Each ws In ActiveWorkbook.Worksheets
   Set FndA = ws.Range("C:C").Find("A", , , xlWhole, , , False, , False)
   Set FndB = ws.Range("C:C").Find("B", , , xlWhole, , , False, , False)
   FndA.EntireRow.Copy
   FndB.EntireRow.Insert
   FndA.EntireRow.Delete
   With ws.UsedRange
       .Value = .Value
   End With
   Application.Goto ws.Cells(1, 1)
 Next ws

So the code as is results in #REF errors in the Prairies and Atlantic sheets, I don't know why but I tried using Cut and Insert instead of Copy, Insert, and Delete and it seemed to work! Thank you so much!

 

[Fluff]

Glad it's sorted & thanks for the feedback