Replace the value in each worksheet

Question

I wrote a macro that copies all files from a given folder with the extension .rpt to excel. Then creating a separate sheet for each product with name of product.

What I need?

Now I need to create a panel in Steering Tab (columns L-N) that changes certain values ​​(from the table in steering tabs) in column B in each sheet, but I can't do it myself.

The concept I came up with is as follows:

I would like my macro to open each sheet separately and do the following tasks for each sheet:

1. Select the range from cell B4 to the end. (I already did it)

   Sub a()
       Dim i As Intiger
       i = Sheets(3).Range("B4").End(xlDown).row
       Sheets(3).Range("B4: B" & i).Select
   End Sub
   

2. Searched for values ​​in the selected range that are > 400 in column B of the each sheet

3. If the condition is true then it should read the values ​​from the table in the steering table in column L and if it finds the same SPcode then:

   a) copied the entire line to the bottom of the sheet

   b) did replace in cell B to the value in cell M from steering tab

4. Then it did an Offset of 6 cells to the side and changed the value to that specified in cell N (but I can already write it myself)

Or second concept

1. A function that Determines where the sheet ends (on which line) and assigns a variable eg endrow
2. Checks column B (spcode) in the sheet to see if there are values from the L column in steering
3. If the condition is true, then it copies the entire line of the spreadsheet with the values satisfying the condition down (under all data), ie under the endrow line - I can't write it totally.
4. In the copied line, changes the SP code to the new one from column M and Entry_month from the Steering Tab 5. Goes to the next sheet

Below are screenshots with view on tables

Important thing to know is:
I would like to be able to add an unlimited number of combinations in the steering tab

I am not an advanced programmer. If there are any questions, ask me on an ongoing basis. I will answer you. In the meantime, I will fight with it myself ;) But please help.

Answer 1

Sub NBSPCODES()

Dim rgSteer As Range
Dim rg As Range
Dim sh As Worksheet
Dim cell As Range
Dim RC As Long
Dim LC As Long
Dim rgDest As Range
Dim MonthCol As Long
Dim i As Long
Dim c As Range
Dim cek As Boolean

'create the range of spcode as rgSteer variable
With Sheets("Config")
Set rgSteer = .Range("L4", .Range("L" & Rows.Count).End(xlUp))
End With

'loop to each sheets
For Each sh In Sheets
    If sh.Name = "Config" Or sh.Name = "Summary" Then GoTo nxt 'skip the loop (too many end if so I reverse the if)
    
    'get the column number where the header value is ENTRY_MONTH as MonthCol variable
    'get the last column of the header as LC variable
        MonthCol = sh.Rows(2).Find("ENTRY_MONTH").Column
        LC = sh.Range("A2").End(xlToRight).Column
        
        'loop to each cell in rgSteer
        For Each cell In rgSteer
            
            'check if the looped cell value (spcode from sheet Configure) is found in the looped sheet column 2
            If Not sh.Columns(2).Find(cell.Value) Is Nothing Then
            'if it is found, then set a range as c variable to find the value of
            'spcode to be replaced (the looped cell.offset(0,1) value) is already in the looped sheet column 2 or not
                Set c = sh.Columns(2).Find(cell.Offset(0, 1).Value, after:=sh.Range("B1"))
                    
                    If Not c Is Nothing Then
                        'if found
                         'check if the entry_month from sheet Configure (looped cell.Offset(0,2) value
                        'is the same with the found cell (the c variable) row, MonthCol value - if same then have cek as false
                        If Cells(c.row, MonthCol).Value = cell.Offset(0, 2).Value Then cek = False
                    Else
                        'if not found have cek variable as true
                        cek = True
                    End If
            End If
            
            'the below code will be processed if it does find the spcode from sheet Configure in the looped sheet column 2
            'but it doesn't find the spcode to be replaced and the entry_month from sheet Configure in the looped sheet
            If cek = True Then
                With sh.Columns(2)
                    .Replace cell.Value, True, xlWhole, , False, False, False
                    Set rg = .SpecialCells(xlConstants, xlLogical).Offset(0, -1)
                    RC = Application.CountA(rg)
                    .Replace True, cell.Value, xlWhole, , False, , False, False
                End With
        
                For i = 1 To LC - 1: Set rg = Union(rg, rg.Offset(0, 1)): Next i
            
                Set rgDest = sh.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
                Set rgDest = rgDest.Resize(RC, LC)
                rg.Copy Destination:=rgDest
                rgDest.Columns(2).Value = cell.Offset(0, 1).Value
                rgDest.Columns(MonthCol).Value = cell.Offset(0, 2).Value
            End If
        
        Next

nxt:
Next sh
End Sub

FYI, if the value under "Existing" is not unique .... For example (based on your image of sheet config), you have 651 ... then this number is to be replaced with 661, entry month 4. if there is another 651 value under "Existing" and the replacing value is (for example) 123 - entry month 7, then the sub at the time the loop in rgSteer find the second 651, it will copy again those 651 range under SpCode, paste to rgDest and fill the rgDest column 2 replacement with 123. Which give you a result that now there are 2 similar data under the last row of the original data.... one with 661 SpCode entry month 4 and one with 123 SpCode entry month 7.

The code assumed that the SpCode data is not sorted. For example, under SpCode of the looped sheet, row 5 to 7 value is 651, row 100 value is 651, row 123 value is 651. That's why it use the union rather than set the rg from rg to rg.offset(0,LC-1).

Once again, it's not tested in my side.

Still, to be honest I'm not so sure if the sub above is "bullet proof", because maybe the data in the looped sheet is already contains something like this :
651 with entry_month 4
651 with entry_month 9
661 with entry_month 7
661 with entry_month 5
661 with entry_month 3

Say for example in sheet configure, the data is : 651 - 661 - 3 So when the sub run, in the looped sheet it find 651 ...
then it check if 661 is already in the looped sheet or not...
it find out that 661 is already there (the c variable is not nothing) ---> this will be the the first 661 found cell in the looped sheet with entry_month 7

then it check if the found (c.row, MonthCol) value is the same with entry month from sheet Configure (3 from the example) or not. Because 7 <> 3 then it won't flag cek = false, the "if" continue and flag cek as true continue the replace-copy-paste process. Resulting like this :
651 with entry_month 4
651 with entry_month 9
661 with entry_month 7
661 with entry_month 5
661 with entry_month 3
661 with entry_month 3 (addition data, changing 651 to 661 entry_month 4 to 3)
661 with entry_month 3 (addition data, changing 651 to 661 entry_month 9 to 3)

So if the data in the looped sheet like the example above and you don't want the result to be like that, then the sub is not "bullet proof".

The sub then need to be revised, to check the entry_month value of each row in column B of the looped sheet with 661 value by looping or find-next method. From the example above, so at the third iteration, it then find out that the third found cell contains 661 in column B of the looped sheet - its entry_month is 3 (the same with the one in sheet Configure), then it flag cek = false, no replace-copy-paste process.

Answer 2

I was able to finish the code and i paste the solution below

Sub NB_TOOL()
Dim rgSteer As Range: Dim rg As Range
Dim sh As Worksheet: Dim cell As Range
Dim RC As Long: Dim LC As Long
Dim rgDest As Range: Dim i As Long
Dim c As Integer
Dim s As Range
Dim strSearch As String

strSearch = "ENTRY_MONTH"

With Sheets("Config")
Set rgSteer = .Range("L4", .Range("L" & Rows.Count).End(xlUp))
End With

For Each sh In Sheets
    If sh.Name <> "Config" And sh.Name <> "Summary" Then
    LC = sh.Range("A2").End(xlToRight).Column
        For Each cell In rgSteer
            With sh.Columns(2)
                .Replace cell.Value, True, xlWhole, , False, , False, False
                Set rg = Nothing
                On Error Resume Next
                Set rg = .SpecialCells(xlConstants, xlLogical).Offset(0, -1)
                On Error GoTo 0
                RC = Application.CountIf(sh.Columns(2), True)
                .Replace True, cell.Value, xlWhole, , False, , False, False
            End With
                       
            If Not rg Is Nothing Then
            
            For i = 1 To LC - 1
            Set rg = Union(rg, rg.Offset(0, 1))
            Next i
            
            Set rgDest = sh.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
            Set rgDest = rgDest.Resize(RC, LC)
            rg.Copy Destination:=rgDest
            rgDest.Columns(2).Value = cell.Offset(0, 1).Value
            
            Set s = sh.Rows(2).Find(What:=strSearch, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, searchFormat:=False)
            If Not s Is Nothing Then
             c = s.Column
            End If
            rgDest.Columns(c).Value = cell.Offset(0, 2).Value
            
            End If
        Next
    End If
Next
End Sub

Thank you @karma for huge help :)

Yesterday I managed to solve all the missing topics by myself :)