Pause or slow down looping macro, resume macro, Excel VBA

[Rowland Hamilton]

Folks,

How do I stop a loop after 3,000 iterations and start it back up at the same spot I left off, or make it slow down so I can pause it and step thru from there? I am investigating an error within the last 35 iterations of the For Step Next loop.

Thank you, Rowland Hamilton

Sub ReorgDataV3()
'Option Explicit works above this one (if all other module macros worked,too)
' Modified from hiker95's code,
' [URL="http://www.mrexcel.com/forum/excel-questions/544487-excel-macro-change-column-data-multiple-rows.html"][COLOR=#49644e]Excel macro - change column data to multiple rows[/COLOR][/URL]
Dim ws1 As Worksheet 'Source worksheet
Dim wsR As Worksheet 'Results worksheet
Dim LR As Long 'Last row
Dim a As Long 'iterated cell in loop
Dim NR As Long 'Next Row
Dim LC As Long 'LC is last column
Application.ScreenUpdating = False
Set ws1 = Worksheets("HC-Stacked")
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=ws1).Name = "Results"
Set wsR = Worksheets("Results")
wsR.UsedRange.Clear
wsR.Range("A1:D1") = [{"Location","Home Department","Week","HC"}]
'Replace this: LR = ws1.Cells(Rows.Count, 1).End(xlUp).Row
               LR = ws1.Cells(Rows.Count, 2).End(xlUp).Row
'Added Last column derivation
               LC = ws1.Cells(1, Columns.Count).End(xlToLeft).Column
For a = 2 To LR Step 1
    '*Finds last populated row in results tab and goes to row below
  NR = wsR.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
  
'*Make current row of Source tab labels in Columns A & B = copy to next # of rows in Results tab = # data columns
  'replace: wsR.Range("A" & NR).Resize(12, 2).Value = ws1.Range("A" & a).Resize(, 2).Value
        wsR.Range("A" & NR).Resize(LC - 2, 2).Value = ws1.Range("A" & a).Resize(, 2).Value
        
'*Transposes Source data headers to Results column C
  'replace: wsR.Range("C" & NR).Resize(12).Value = Application.Transpose(ws1.Range("C1:N1").Value)
        wsR.Range("C" & NR).Resize(LC - 2).Value = Application.Transpose(ws1.Range(ws1.Cells(1, 3), ws1.Cells(1, LC)).Value)
'*Transposes current Source row's data to Results column D
  'replace: wsR.Range("D" & NR).Resize(12).Value = Application.Transpose(ws1.Range("C" & a & ":N" & a).Value)
        wsR.Range("D" & NR).Resize(LC - 2).Value = Application.Transpose(ws1.Range(ws1.Cells(a, 3), ws1.Cells(a, LC)).Value)
Next a
wsR.UsedRange.Columns.AutoFit
wsR.Activate
Application.ScreenUpdating = True
End Sub

 

[BobUmlas]

Inside the loop:
If a>=3000 then stop 'puts it in step mode

 

[Tulon]

There's probably a better way, but you could try something like:

  If a = LR-35 then
    Workbooks("afsdjhkfd.xls").Activate
  End If

When you're 35 away from the end, it'll try activating a workbook that's not there and will debug, allowing you to begin stepping through the code.