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

 

[Fluff]

Try

     Set Rnga = [COLOR=#ff0000]ws.[/COLOR]Range("C1", [COLOR=#ff0000]ws.[/COLOR]Range("C65536").End(xlUp))

At the moment it's only working on the active sheet.

 

[MARK858]

Btw also as a side note you are declaring both rng1 and cell as Variant not Range.

 

[ukanez]

Try

     Set Rnga = [COLOR=#ff0000]ws.[/COLOR]Range("C1", [COLOR=#ff0000]ws.[/COLOR]Range("C65536").End(xlUp))

At the moment it's only working on the active sheet.

Thank you and apologies for the duplicate thread.

I've tried making your change but I'm finding the same result: 11 of the worksheets in the workbook work as intended but the final sheet (Atlantic in this case) still remains unchanged.

 

[ukanez]

Btw also as a side note you are declaring both rng1 and cell as Variant not Range.

Sorry could you expand on this? I'm very much a beginner at this haha.

 

[Fluff]

How about

Application.ScreenUpdating = False
Dim i As Long
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
   For i = S.Range("C" & Rows.Count).End(xlUp).Row To 1 Step -1
      If ws.Range("C" & i).Value = "A" Then
         ws.Rows(i).Cut
      ElseIf ws.Range("C" & i).Value = "B" Then
         ws.Rows(i).Insert xlDown
      End If
   Next i
   With ws.UsedRange
       .Value = .Value
   End With
   Application.Goto ws.Cells(1, 1)
 Next ws

 

[ukanez]

How about

   For i = S.Range("C" & Rows.Count).End(xlUp).Row To 1 Step -1

I am getting a 424 error: Object required.

 

[Fluff]

Typo that should read

For i = [COLOR=#ff0000]ws[/COLOR].Range("C" & Rows.Count).End(xlUp).Row To 1 Step -1

 

[ukanez]

Typo that should read

For i = [COLOR=#ff0000]ws[/COLOR].Range("C" & Rows.Count).End(xlUp).Row To 1 Step -1

Thanks. Now the result is that the first 11 worksheets have a blank row where the cut row was.

The final worksheet still has the cut row in the same place, but now has a duplicate of that row in the correct place lower in the sheet.

 

[Fluff]

Will there be any blank cells in col C other than the cut rows?
Also are all the cut rows being placed in the correct location?