Changing VBA code to Nextsheet instead of an specific sheet (Last part of the code)

Temkox

New Member
Joined
Feb 8, 2021
Messages
12
Office Version
  1. 2019
Platform
  1. Windows
Hi, i need help changing the sheets in my code, I need to change the "LOG" sheet (Last part of the code) to the following or next sheet
VBA Code:
Option Base 1
Sub FIFO()
'
Dim CantVen() As Double, SYMBOL() As String, Ventas() As String, source() As String, PrecioUnidad() As Double
Dim i As Integer, t As Integer, pendiente As Integer, emparejada As Integer, j As Integer, x As Double
Dim rngA As Range
Dim cell As Range

    Application.ScreenUpdating = False


        'if inventory records < 1 row exit sub
        'else add remaining column fill down
        With ActiveSheet
            If .Cells(.Rows.Count, "A").End(xlUp).Row > 2 Then
            
                'Sort Inventory by Pdt,by Date
                'https://trumpexcel.com/sort-data-vba/
                With ActiveSheet.Sort
                    .SortFields.Clear ' to clear prior sort data
                    .SortFields.Add Key:=Range("B1"), Order:=xlAscending
                    .SortFields.Add Key:=Range("A1"), Order:=xlAscending
                    .SetRange Range("mydata")
                    .Header = xlYes
                    .Apply
                End With
            
                .Range("G2:G" & .Cells(.Rows.Count, "C").End(xlUp).Row).Formula = "=C2-F2"
                .Range("H2:H" & .Cells(.Rows.Count, "C").End(xlUp).Row).Formula = "=G2*D2"
                .Range("O2:O" & .Cells(.Rows.Count, "K").End(xlUp).Row).Formula = "=SUMIFs(LOG!F:F,LOG!A:A,K2,LOG!C:C,L2)"
            End If
            
        End With
        

        
        
        
        'Check Availability of stock for those pendiente insufficient cases

        Set rngA = ActiveSheet.Range("P2:P" & ActiveSheet.Cells(ActiveSheet.Rows.Count, "P").End(xlUp).Row)
        
        t = 0
        
        For Each cell In rngA
            If cell.Value = "Insufficient Stock" Then
                
                If Not WorksheetFunction.SumIf(ActiveSheet.Range("B:B"), ActiveSheet.Range("L" & cell.Row).Value, ActiveSheet.Range("G:G")) < ActiveSheet.Range("M" & cell.Row).Value Then
                    ActiveSheet.Range("N" & cell.Row).Value = ActiveSheet.Range("M" & cell.Row).Value
                    ActiveSheet.Range("P" & cell.Row).ClearContents
                    'Narrow down the range for SKU lookup
                    'goto by find
                    Let endrow = Columns("B:B").Find(What:=ActiveSheet.Range("L" & cell.Row).Value, After:=ActiveSheet.Range("B1"), LookIn:=xlValues, LookAt _
                            :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:= _
                            False, SearchFormat:=False).Row
                    Let startrow = Columns("B:B").Find(What:=ActiveSheet.Range("L" & cell.Row).Value, After:=ActiveSheet.Range("B1"), LookIn:=xlValues, LookAt _
                            :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                            False, SearchFormat:=False).Row
                            
                    x = ActiveSheet.Range("M" & cell.Row).Value
                      
                    'Loop through Inventory
                    For i = startrow To endrow
                                
                        With Range("B" & i)
                                
                            If x <> 0 And .Offset(, 5).Value > 0 Then
                                t = t + 1
                                ReDim Preserve CantVen(t)
                                ReDim Preserve SYMBOL(t) 'Range("L" & j).Value
                                ReDim Preserve Ventas(t) 'Range("K" & j).Value
                                ReDim Preserve source(t)    '.Offset(, 3)
                                ReDim Preserve PrecioUnidad(t)    '.Offset(, 2)
                                    
                                    If .Offset(, 5).Value >= x Then
                                        .Offset(, 4) = .Offset(, 4) + x
                                        CantVen(t) = x
                                        SYMBOL(t) = ActiveSheet.Range("L" & cell.Row).Value
                                        Ventas(t) = ActiveSheet.Range("K" & cell.Row).Value
                                        source(t) = .Offset(, 3)
                                        PrecioUnidad(t) = .Offset(, 2)
                                        x = 0
                                    Else
                                        SYMBOL(t) = ActiveSheet.Range("L" & cell.Row).Value
                                        Ventas(t) = ActiveSheet.Range("K" & cell.Row).Value
                                        source(t) = .Offset(, 3)
                                        PrecioUnidad(t) = .Offset(, 2)
                                        CantVen(t) = .Offset(, 5).Value
                                        x = x - .Offset(, 5).Value
                                        .Offset(, 4) = .Offset(, 4) + .Offset(, 5)
                                    End If
                            End If
                                  
                        End With
                        
                    Next i
                    
                End If
            End If
        Next cell
        

    'Do a check for new orders pendiente to be emparejada comparing the last row of col M & N
        Let pendiente = Columns("M:M").Find(What:="*", After:=ActiveSheet.Range("M1"), LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:= _
        False, SearchFormat:=False).Row
        
        Let emparejada = Columns("N:N").Find(What:="*", After:=ActiveSheet.Range("N1"), LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:= _
        False, SearchFormat:=False).Row
        
        
    'Do a check for availability of remaining inventory b4 going on
    'Loop through sales order .if stock available proceed to match else just 0 and skip to next iteration
        For j = emparejada + 1 To pendiente
            
            If WorksheetFunction.SumIf(ActiveSheet.Range("B:B"), ActiveSheet.Range("L" & j).Value, ActiveSheet.Range("G:G")) < ActiveSheet.Range("M" & j).Value Then
                Range("N" & j).Value = 0
                Range("P" & j).Value = "Insufficient Stock" 'Update those outstanding "insufficient stocks" that are just emparejada to LOG
                GoTo NextIteration:
            Else
                Range("N" & j).Value = Range("M" & j).Value
            End If


        'Narrow down the range for SKU lookup
        'goto by find
            Let endrow = Columns("B:B").Find(What:=ActiveSheet.Range("L" & j).Value, After:=ActiveSheet.Range("B1"), LookIn:=xlValues, LookAt _
                    :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:= _
                    False, SearchFormat:=False).Row
            Let startrow = Columns("B:B").Find(What:=ActiveSheet.Range("L" & j).Value, After:=ActiveSheet.Range("B1"), LookIn:=xlValues, LookAt _
                    :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                    False, SearchFormat:=False).Row
                    
            x = ActiveSheet.Range("M" & j).Value

            
            'Loop through Inventory
            For i = startrow To endrow
                        
                With Range("B" & i)
                        
                    If x <> 0 And .Offset(, 5).Value > 0 Then
                            
                        t = t + 1
                        ReDim Preserve CantVen(t)
                        ReDim Preserve SYMBOL(t) 'Range("L" & j).Value
                        ReDim Preserve Ventas(t) 'Range("K" & j).Value
                        ReDim Preserve source(t)    '.Offset(, 3)
                        ReDim Preserve PrecioUnidad(t)    '.Offset(, 2)
                            
                            If .Offset(, 5).Value >= x Then
                                .Offset(, 4) = .Offset(, 4) + x
                                CantVen(t) = x
                                SYMBOL(t) = ActiveSheet.Range("L" & j).Value
                                Ventas(t) = ActiveSheet.Range("K" & j).Value
                                source(t) = .Offset(, 3)
                                PrecioUnidad(t) = .Offset(, 2)
                                x = 0
                            Else
                                SYMBOL(t) = ActiveSheet.Range("L" & j).Value
                                Ventas(t) = ActiveSheet.Range("K" & j).Value
                                source(t) = .Offset(, 3)
                                PrecioUnidad(t) = .Offset(, 2)
                                CantVen(t) = .Offset(, 5).Value
                                x = x - .Offset(, 5).Value
                                .Offset(, 4) = .Offset(, 4) + .Offset(, 5)
                            End If
                    
                    End If
                                              
                End With
                
            Next i
NextIteration:
        Next j
        
        'UPDATE LOG
        On Error Resume Next
        'http://www.cpearson.com/excel/ArraysAndRanges.aspx
        'Could be improved through split function I think....to be explored later
        Dim Destination As Range
        
        Set Destination = LOG.Cells(LOG.Rows.Count, "A").End(xlUp).Offset(1, 0)
        Set Destination = Destination.Resize(UBound(Ventas), 1)
        Destination.Value = Application.Transpose(Ventas)
        
        Set Destination = LOG.Cells(LOG.Rows.Count, "B").End(xlUp).Offset(1, 0)
        Set Destination = Destination.Resize(UBound(source), 1)
        Destination.Value = Application.Transpose(source)
        
        Set Destination = LOG.Cells(LOG.Rows.Count, "C").End(xlUp).Offset(1, 0)
        Set Destination = Destination.Resize(UBound(SYMBOL), 1)
        Destination.Value = Application.Transpose(SYMBOL)
        
        Set Destination = LOG.Cells(LOG.Rows.Count, "D").End(xlUp).Offset(1, 0)
        Set Destination = Destination.Resize(UBound(CantVen), 1)
        Destination.Value = Application.Transpose(CantVen)
        
        Set Destination = LOG.Cells(LOG.Rows.Count, "E").End(xlUp).Offset(1, 0)
        Set Destination = Destination.Resize(UBound(PrecioUnidad), 1)
        Destination.Value = Application.Transpose(PrecioUnidad)
        
        LOG.Range("F2:F" & LOG.Cells(LOG.Rows.Count, "E").End(xlUp).Row).Formula = "=E2*D2"
        '''''End If
        
        With ActiveSheet
            .Range("Orders").Value = .Range("Orders").Value
            .Range("MyData").Value = .Range("MyData").Value
        End With
        DoEvents

    Application.ScreenUpdating = True
End Sub
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Watch MrExcel Video

Forum statistics

Threads
1,127,374
Messages
5,624,323
Members
416,020
Latest member
ANDREAC247

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top