Posts by norie

Option Explicit


Global UtilData As String
Global FinanceData As String
Global ULastCol As Long
'________________________________


Sub Months()
Dim wbUtil As Workbook
Dim wbFinance As Workbook
Dim FLastCol As Long
Dim FLastRow As Long
Dim UCAC() As String
Dim ULastRow As Long
Dim FFindCell As Range
Dim SrchCell As String
Dim CurrentUCAC As String
Dim iReply As VbMsgBoxResult
Dim I As Long
Dim X As Long
Dim Y As Long
Dim W As Long


    '**** 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.


    Set wbUtil = ActiveWorkbook    '    UtilData = ActiveWorkbook.Name


    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
        Set wbFinance = ActiveWorkbook   ' Data = ActiveWorkbook.Name
    End If


    'Workbooks(UtilData).Sheets("Utility Data").Activate
    ULastCol = wbUtil.Sheets("Utility Data").Cells(1, Columns.Count).End(xlToLeft).Column


    With wbFinance.ActiveSheet    '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 I
    End With


    '***End Set-up Code*********


    ULastRow = wbUtil.Sheets("Utility Data").Cells(Rows.Count, 3).End(xlUp).Row


    With wbFinance.ActiveSheet


        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


                        For W = 3 To ULastRow
                            If wbUtil.Sheets("Utility Data").Cells(W, 3) = CurrentUCAC And wbUtil.Sheets("Utility Data").Cells(W, 9) = SrchCell Then
                                wbUtil.Sheets("Utility Data").Cells(W, 11).PasteSpecial (xlPasteValues)
                                SrchCell = FFindCell.Offset(0, 1)
                            End If
                        Next W   '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 Y    '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 X
        
    End With


End Sub

Sub macrorebuy1()
 '
 ' macrorebuy1 Macro
 '
 ' Keyboard Shortcut: Ctrl+k
 '


    Sheets("Rebuy Shipping").Copy Before:=Sheets(4)


   With ActiveSheet
      .Cells.Copy
      .Cells.PasteSpecial Paste:=xlPasteValues
      .Name = Format(Date, "ddmmmyyyy")
    End With


 End Sub