VBA Loop

[StevenW]

Hi All,
I am having issues with loops in VBA, I am convinced it should work but doesn't & I have no clue why!

      Sub SiteReports()

Dim T As Integer
Dim fldr As FileDialog
Dim sItem As String
Application.ScreenUpdating = False
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        .InitialFileName = Application.DefaultFilePath
        If .Show <> -1 Then GoTo NextCode
        sItem = .SelectedItems(1)
    End With
NextCode:
    GetFolder = sItem
    Set fldr = Nothing
T = 4
Do Until IsEmpty(Cells(T, 12))
Sheets("CC's").Select
CLZ = Range("N" & T) & ".xlsx"
Sheets("CC's").Range("L" & T).Copy
Sheets("Report").Range("A1").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sheets("CC's").Range("M" & T).Copy
Sheets("Report").Range("A2").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sheets("Report").Select
ActiveSheet.Range("$AR$4:$AR$220").AutoFilter Field:=1, Criteria1:="Show"
Sheets("Report").Range("$A$1:$AP$250").Copy
Dim SPath As String, SFile As String
Dim Wb As Workbook
SPath = "C:\Users\ws00056244\Documents\Temp"
SFile = SPath & "Report Temp Dump - DO NOT DELETE.xlsx"
Set Wb = Workbooks.Open(SFile)
Sheets("Sheet1").Range("A1").PasteSpecial xlPasteValues
Sheets("Sheet1").Range("A1").PasteSpecial xlPasteFormats
Application.CutCopyMode = False
 ActiveWorkbook.SaveAs Filename:= _
        sItem & "" & "" & CLZ _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Windows("Reporting File.xlsb").Activate
T = T + 1
Loop
Application.ScreenUpdating = True
MsgBox ("Site Reports Completed.")
End Sub

 

[steve the fish]

If you look at these 3 lines can you tell us why you have the middle one?

Do Until IsEmpty(Cells(T, 12))
Sheets("CC's").Select
CLZ = Range("N" & T) & ".xlsx"

 

[StevenW]

Oh dear! That has solved that question!

I was running the Macro using a button on a different sheet to the CC's (range which it should be working through until empty!) The middle line was to swith to the correct sheet & should be BEFORE the 'do until' not after!

Thank you!!!