troubleshooting Question

If Statement not working Excel VBA 2010

Avatar of rhadash
rhadash asked on
Microsoft OfficeMicrosoft ExcelMicrosoft Applications
10 Comments1 Solution527 ViewsLast Modified:
Hi,
I have code tha will copy all excel workbook sheets in a folder into a single workbook.  Then it looks at the Affs tab for the ticket number.  It will find match the ticket number from the Affs tab to the ticket number in any tab with name "aviswkly" .  Every ticket in the Affs tab will have a match.  It then copies the "aviswkly" data followed by Affs tab data into the combinedsheet tab.   It does the copy and paste fine but then gets hung up.  The following code has issue that I can't figure out.    It just hangs like it might be looping through all rows. I've attached the code and workbooks. Appreciate any advice on this.


Dim t As Range, cs As Worksheet, zs As Worksheet, ss As Worksheet, tmatch As Range, cs_last_row As Long


Set fs = Worksheets("affs")
Set cs = Worksheets("Combinedsheet")

For Each t In fs.Columns(9).Cells
    If t.Value = "Ticket Number" Then
   If t.Value = "" Then Exit For
End If

    For Each zs In Wb1.Sheets ' was: ThisWorkbook.Sheets
        If Left(zs.Name, 9) = "aviswkly_" Then
'            If Left(zs.Name, 9) = "aviswkly_" Then this is original line changing tab name
            Set tmatch = zs.Columns(9).Find(What:=t.Value)
            If Not tmatch Is Nothing Then
                cs_last_row = cs.UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1
                cs.Range("A" & cs_last_row & ":Q" & cs_last_row).Value = zs.Range("A" & tmatch.Row & ":Q" & tmatch.Row).Value
                cs.Range("R" & cs_last_row & ":AQ" & cs_last_row).Value = fs.Range("A" & t.Row & ":Z" & t.Row).Value
       
           End If
        End If
    Next zs
Next t
AffReport.xls
Afftest-code.txt
ABG-103455-MiamiDade-012913.xls
ABG-103455-MiamiDade-021213.xls
ASKER CERTIFIED SOLUTION
NorieAnalyst Assistant
Join our community to see this answer!
Unlock 1 Answer and 10 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros