Apologies if the thread name is vague, it's the best description I could come up with.
Here's the issue:
I have several loops of code nested together. Their purpose is to transpose the values of cells that are arranged horizontally in one worksheet (FinanceData) into a vertical format in a second worksheet (UtilData). In transposing the data, every account number (UCAC) has 12 rows in the new workbook (UtilData) to correspond to 12 columns in the original workbook (FinanceData). You may have already guessed that the 12 rows / columns correspond to calendar months and the data captures performance within those months. I use both the 'current' account number (CurrentUCAC) and the calendar month (SrchCell) as variables to identify the correct copy/paste cells between workbooks.
Global UtilData As String Global FinanceData As String Global ULastCol As Integer '________________________________ Sub Months() Dim FLastCol as Integer Dim FLastRow as Integer Dim UCAC as String Dim ULastRow As Integer Dim FFindCell As Range Dim SrchCell As String Dim CurrentUCAC As String '**** Some set-up code - I am not having problems with this code, just including it for reference**** 'Workbooks(UtilData) is the workbook with the updated format. 'Workbooks(FinanceData) is the workbook with the original data in its original format. This version works great for our finance folk, but not so much for my analyses. UtilData = ActiveWorkbook.Name 'Message Box offers to open and identify the new Finance Data file. iReply = MsgBox("Please open the new utility data file. The Macro will copy / paste the new data into the existing table.", vbOKCancel, Title:="Launch Transfer") If iReply = vbOK Then Application.FindFile FinanceData = ActiveWorkbook.Name End If Workbooks(UtilData).Sheets("Utility Data").Activate ULastCol = Cells(1, Columns.Count).End(xlToLeft).Column Workbooks(FinanceData).Activate FLastCol = Cells(1, Columns.Count).End(xlToLeft).Column FLastRow = Cells(Rows.Count, 7).End(xlUp).Row 'Use "City Acct Code" as anchors to copy / paste data. There will be a separate row for each month. ReDim UCAC(2 To FLastRow) For i = 2 To FLastRow UCAC(i) = Cells(i, 3) Next '***End Set-up Code********* Workbooks(UtilData).Sheets("Utility Data").Activate ULastRow = Cells(Rows.Count, 3).End(xlUp).Row Workbooks(FinanceData).Activate SrchCell = "July kwh" For x = 2 To FLastRow CurrentUCAC = UCAC(x) 'If this looks funky, that's because I use the String in another bank of code not shown here. However, I'm not convinced this is the problem bc the loop never makes it back to this variable. If Cells(x, 3) = CurrentUCAC Then For y = 7 To 19 'This is the loop that is failing. Set FFindCell = Cells(1, y).Find(SrchCell, LookIn:=xlValues) If Not FFindCell Is Nothing Then Cells(x, y).Copy Workbooks(UtilData).Sheets("Utility Data").Activate For w = 3 To ULastRow If Cells(w, 3) = CurrentUCAC And Cells(w, 9) = SrchCell Then Cells(w, 11).PasteSpecial (xlPasteValues) Workbooks(FinanceData).Activate FFindCell.Offset(0, 1).Select SrchCell = ActiveCell End If Next 'Everything works great until this loop has one successful completion of code (i.e. it successfully copy/pastes values). Then the Sub just ends, and does not finish 'y = 7 to 19' End If Next 'This is the loop that is failing. I want 'y' to run through columns 7 to 19, currently it's quitting at 8. Column 8 is the first relevant column to the copy/paste method. End If 'This loop is not completed either. 'x' should loop to row 900-ish. If I create breakpoints and run the program slowly, it runs perfect! But it I let it run on its own, at full speed, it does one successful loop and ends. Next End Sub