Avatar of rjef
rjef
Flag for United States of America

asked on 

vba script not selecting the right tab

any idea why this vba script is not putting the correct company name in the correct tab
attached is the excel sheet.  Column 'E' should be the company name, same as the tab

here is the script
Sub DailyReport()
    Dim cust(100) As String
cust(1) = "Company1"
cust(2) = "Company2"
cust(3) = "Company3"
cust(4) = "Company4"
cust(5) = "Company5"
cust(6) = "Company6"
cust(7) = "Company7"
cust(8) = "Company8"
cust(9) = "Company9"
cust(10) = "Company10"
cust(11) = "Company11"
cust(12) = "Company12"
cust(13) = "Company13"
xxx = 13




For gg = 1 To xxx
Set Rng = Nothing
    Sheets("results").Select
    Range("A1").Select
    stuff = Cells(2, 1)
        If stuff <> 0 Then
         Sheets("results").Select
            Selection.AutoFilter
             Sheets("results").Select
            With ActiveSheet
                'Getting the last filled in row number for a column. Can be any column (avoid columns that will have blanks), just want to know how far down the list goes.
                DataEnd = .Range("A" & .Rows.Count).End(xlUp).Row
                    MsgBox DataEnd
                    If DataEnd <> 1 Then
                     '   On Error Resume Next
                            .Range("A:AV").AutoFilter Field:=5, Criteria1:=cust(gg)
                            Set Rng = .Range("E2:E" & DataEnd).SpecialCells(xlCellTypeVisible).EntireRow
                                Sheets(cust(gg)).Select
                                Rng.Copy Sheets(cust(gg)).Range("A2")
                                Rng.Delete
                     '       On Error GoTo 0
                    End If
            End With
        End If
            Sheets(cust(gg)).Select
            Cells.Select
            Cells.EntireColumn.AutoFit
            Range("A1").Select
Next gg
       

Open in new window

results.xlsx
VBAMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
rjef

8/22/2022 - Mon