VBA Help needed, Do until Loop

[StevenW]

Hi All,
I am strugling (rookie) with a loop. I think I have the ending of the loop wrong some how?

Currently the code selects the folder location then says 'CC Reports completed'.

As I am trying to test it, column A has data 4-100 rows long. Column I has data 4-19 with 4 showing true the rest false.

Basically I need it to

Loop until Column A is empty starting at row 4 carrying out the main body of the macro only IF column I = True.

I have tried to space the code to show 'key' areas.

I appreciate any help!!!

Sub CCReports()

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, 1))



Sheets("CC's Included").Select
CLP = Range("C" & T)
CLG = Range("J" & T) & ".xlsx"



If Range("I" & T) = True Then         
                                                        

Sheets("CC's Included").Range("A" & T).Copy                                                    
Sheets("Report").Range("A1").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sheets("CC's Included").Range("B" & 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 = "O:\_DRXSites\UK_TAM\FP\_FP8\Controlling\Controlling\Management Accounts 2019\01 - Cost Centre Reports\Master Files"
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 & "" & CLP & "" & CLG _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close



Windows("Monster File.xlsb").Activate



End If



T = T + 1



Loop



Application.ScreenUpdating = True
MsgBox ("CC Reports completed.")



End Sub

 

[JLGWhiz]

See if this works for you.

Sub CCReports()
Dim c As Range 'T As Integer
Dim fldr As FileDialog, sItem As String
Dim SPath As String, SFile As String
Dim Wb As Workbook
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, 1)) 'The function is not intended to check if a cell has a value.
    With ActiveSheet
        For Each c In .Range("A4", .Cells(Rows.Count, 1).End(xlUp))
            With Sheets("CC's Included")
                CLP = .Range("C" & c.Row)
                CLG = .Range("J" & c.Row) & ".xlsx"
                If .Range("I" & c.Row) = True Then
                    .Range("A" & c.Row).Copy
                     Sheets("Report").Range("A1").PasteSpecial xlPasteValues
                    .Range("B" & c.Row).Copy
                    Sheets("Report").Range("A2").PasteSpecial xlPasteValues
                     Application.CutCopyMode = False
                    SPath = "O:\_DRXSites\UK_TAM\FP\_FP8\Controlling\Controlling\Management Accounts 2019\01 - Cost Centre Reports\Master Files\"
                    SFile = SPath & "Report Temp Dump - DO NOT DELETE.xlsx"
                    Set Wb = Workbooks.Open(SFile)
                    With Sheets("Report")
                        .Range("$AR$4:$AR$220").AutoFilter Field:=1, Criteria1:="Show"
                        .Range("$A$1:$AP$250").Copy
                        Wb.Sheets("Sheet1").Range("A1").PasteSpecial xlPasteValues
                        Wb.Sheets("Sheet1").Range("A1").PasteSpecial xlPasteFormats
                        Application.CutCopyMode = False
                        Wb.SaveAs Filename:= _
                        sItem & "" & CLP & "" & CLG _
                        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
                        Wb.Close False
                    End With
                    Windows("Monster File.xlsb").Activate
                End If
            End With
        Next
    End With
'T = T + 1
'Loop
Application.ScreenUpdating = True
MsgBox ("CC Reports completed.")
End Sub

 

[StevenW]

Hi Thank you for the reply!

It has come back with a error

Set Wb = Workbooks.Open(SFile)

With Sheets("Report") - This bit!
.Range("$AR$4:$AR$220").AutoFilter Field:=1, Criteria1:="Show"
.Range("$A$1:$AP$250").Copy
Wb.Sheets("Sheet1").Range("A1").PasteSpecial xlPasteValues

Do I just need to ensure that 'Monster file' is active workbook?

I'm kind of lost in what this code all means!

You mention that the 'Do until IsEmpty' function is not intended to check if a cell has a value, what is it for? A lot of people on google seem to use it for that?

Thank you for all your help!!!

 

[JLGWhiz]

Excerpt from the VBA help files.

Returns a Boolean value indicating whether a variable has been initialized.​

Syntax

IsEmpty(expression)The required expression argument is a Variant containing a numeric or string expression. However, because IsEmpty is used to determine if individual variables are initialized, the expression argument is most often a single variable name.​

You can use

If Range("A1").Value = ""

to check for empty cells.
ISEmptyt will not error when used to check cell values, but it can return undesired results.

What was the error you received on the Workbook.Open statement? I only added a backslash to the file path. Maybe it wasn't needed.


The code is standard method using With statements instead of Select and Activate. The intent was to eliminate the flicker and flash. I also used a For Next loop instead of the Do Loop, but it still loops based on column A values.