msquared99
Member
I have some code that uses Auto Filter to copy the matching data from the master workbook called Intercompany or wsIC in the code to a matching workbook called myFile.
My issue is if I have a workbook in the folder and there is no match for the RepName in wsIC the code writes all the data in wsIC into myFile. What I want it to do is if no match is found then copy nothing and move to the next workbook.
Below is the part of the code I need to fix:
My issue is if I have a workbook in the folder and there is no match for the RepName in wsIC the code writes all the data in wsIC into myFile. What I want it to do is if no match is found then copy nothing and move to the next workbook.
Below is the part of the code I need to fix:
Code:
Do While myFile <> "" 'Loop through each Excel file in folder
Set wb = Workbooks.Open(Filename:=myPath & myFile) 'Set variable equal to opened workbook
wb.Worksheets.Add(After:=Worksheets(1)).Name = "IC" 'With opened workbook add a sheet and rename
With Worksheets("IC")
.Range("A1:H1").Value = ColHeads
.Range("A1:H1").Font.Bold = True
.Columns("A:H").AutoFit
End With
RepName = Left(myFile, InStr(myFile, " ") - 1) 'extracts the Rep Name from the file name. -1 for removing space
'****************************************************************************************************************
'================================================================================================================
'Need error handling so if a rep has no IC Commission nothing will be copied to the workbook
'================================================================================================================
'****************************************************************************************************************
'copy matching data from IC to opened workbook aka myFile
With wsIC
.Cells(1).AutoFilter Field:=4, Criteria1:="=" & RepName
With .AutoFilter.Range
If .Rows.Count > 1 Then 'there is at least 1 row which meets the filter criteria
For i = LBound(ColHeads) To UBound(ColHeads)
ColNum = .Rows(1).Find(ColHeads(i)).Column
.Columns(ColNum).Offset(1).Resize(.Rows.Count - 1).Copy Destination:=wb.Worksheets("IC").Cells(2, i + 1)
'offset 1 row to exclude the header row. +1 for first value of i = 0 ColHeads is zero based array
Next i
End If
End With
End With
StartRow = 2
EndRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(EndRow, 1).Value = "Total"
Cells(EndRow, 8).FormulaR1C1 = "=Sum(R[" & StartRow - EndRow & "]C:R[-1]C)"
Set wsCS = Worksheets("Commission Summary")
Set wsM3 = Worksheets("M3")
With wsIC
LastRow1 = Cells(.Cells.Rows.Count, "H").End(xlUp).Row - 1
End With
wsCS.Range("B3") = "=Sum(IC!H2:H" & LastRow1 & ")"
'add total of column H of IC worksheet to Commission Summary worksheet.
With wsM3
LastRow = .Cells(.Rows.Count, "P").End(xlUp).Row
End With
wsCS.Range("B2") = "=sum(M3!P" & LastRow & ")"
LastRow = Empty
'Add total of column P of M3 worksheet to Commission Summary worksheet
wb.Close SaveChanges:=True
myFile = Dir 'Get next file name
Loop[\code]
