Loop for spefic condition not working

[VISHAL120]

Hi ,

i have data which am taking from another workbook and which are as follows:

ref season cust depart status
1 aw tom man NS
2 aw jhon man OP
3 aw david man OP
4 aw vishal man OP
5 aw rocky man OP
6 aw vetir man OP
7 aw own man NS
8 aw myson man NS
9 ss market man NS
10 ss catia woman NS
11 ss claudie woman C
12 ss port woman C



I need to take data that has status NS only to place in the other workbook.

I am using the code as :

Do Until Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_INdex - 1, Marketing_Depart - 1).Value = "NS"
    
        
        'assigning Ref Client
        Range("Master_p!A1").Offset(Master_Row_Index - 1, Master_ref_client - 1) = _
        Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_INdex - 1, Marketing_Ref_Client - 1)
        
        'CHECK IF DEPT IS COMPLETE,ON PROCESS OR NEW SAMPLE _
        ONLY NEW SAMPLE TO BE UPDATED
        
        New_sample_status = Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_INdex - 1, Marketing_Depart - 1)
        
        Range("Master_p!A1").Offset(Master_Row_Index - 1, Master_Depart - 1) = _
        Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_INdex - 1, Marketing_Depart - 1)
        
        Range("Master_p!A1").Offset(Master_Row_Index - 1, Master_Client - 1) = _
        Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_INdex - 1, Marketing_Client - 1)
        
        Range("Master_p!A1").Offset(Master_Row_Index - 1, Master_Marketing_Manager - 1) = _
        Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_INdex - 1, Marketing_Marketing_Manager - 1)
        
        Range("Master_p!A1").Offset(Master_Row_Index - 1, Master_Merchandiser - 1) = _
        Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_INdex - 1, Marketing_Merchandiser - 1)
        
        Range("Master_p!A1").Offset(Master_Row_Index - 1, Master_Ref_Karina - 1) = _
        Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_INdex - 1, Marketing_Ref_Karina - 1)
        
        Range("Master_p!A1").Offset(Master_Row_Index - 1, Master_Saison - 1) = _
        Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_INdex - 1, Marketing_Saison - 1)
        
        
skip_row:
             Row_INdex = Row_INdex + 1
            Master_Row_Index = Master_Row_Index + 1
    Loop

with the above code its not doing only sending data which has status "NS" but all are being send.

can you guys just give me some hint on that please.

thanks a lot.

[SamT]

That is not all the code. For example We don't have a clue as to the values or intent of any of these variables: New_sample_status, Master_ref_client, Marketing_Ref_Client, Marketing_Depart, Master_Depart, Master_Client, Marketing_Client, Master_Marketing_Manager, Marketing_Marketing_Manager, Master_Merchandiser, Marketing_Merchandiser, Master_Ref_Karina, Marketing_Ref_Karina, Master_Saison, Marketing_Saison.

This sub will do what I am guessing you asked for

Option Explicit

Option Explicit

Sub SamT(Optional ColumnToTest As String = "E", Optional CheckValue As Variant = "NS")
'copies the entire row, if Status = "NS", from the Marketing Planning Sheet
'to the Master_p Sheet.
'CheckValue is Case Insensitive.
'Optional: Can be used to transfer data by any column with any value.

'For Help, see: "http://www.vbaexpress.com/forum/showthread.php?53525-Loop-for-spefic-condition-not-working"

'Assumptions: With data Sheet, Row(1) is Headers, Status Column is "E"
Dim PasteCell As Range
Dim LastRow As Long
Dim DataSht As Worksheet
Dim rw As Long


  Set PasteCell = Sheets("Master_p").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
  Set DataSht = Workbooks("Marketing_sample_planning.XLS").Sheets("Marketing")
  LastRow = DataSht.Cells(Rows.Count, "A").End(xlUp).Row
  
  With DataSht
    For rw = 2 To LastRow 'Assumes Headers in Row(1)
      If LCase(.Cells(rw, ColumnToTest)) = LCase(CheckValue) Then
        .Cells(rw, ColumnToTest).EntireRow.Copy PasteCell
        Set PasteCell = PasteCell.Offset(1, 0)
      End If
    Next rw
  End With

End Sub

[VISHAL120]

Hi SamT,

thanks for the quick reply. Yes you are right its not all the code as i have send only part of the code where i have been having problem.

In fact i have just tested the code and it works but the problem i am having now are some columns (like dates which are filled later) which are blank and after that we have some data again e.g :

Column F,H,I,J can be blank to filled later for dates and Column K,L,M,N have data. When the code are running it is not copying the data that are found in column K,L,M,N as it is taking only column which has data before the blank column F,H,I,J.

Can you please see to that and advise .

thank you again.

[SamT]

Did you test my code? You need to use a "Stub" to test it

Sub Test_SamT()
SamT
End Sub

In this line

LastRow = DataSht.Cells(Rows.Count, "A").End(xlUp).Row

You need to replace "A" with a Column Letter that is always filled to the bottom

Yes you are right its not all the code as i have send only part of the code where i have been having problem.

Are you sure you sent the part that is causing the problem?

Please send all the code.

[VISHAL120]

Hi SamT,

Actually am having problem of duplication every time the workbook is updating As such :

CAGAGO RK 5697 S		ETE 16	AAA15	BCCC15
CAGAGO RK 5697 S		ETE 16	AAA15	BCCC15
CAGAGO RK 5697 S		ETE 16	AAA15	BCCC15
CALCIF RK 5421 S		ETE 16	AAA15	BCCC15
CALCIF RK 5421 S		ETE 16	AAA15	BCCC15
CALCINE RK 5411 S		ETE 16	AAA15	BCCC15

As requested please find below the complete code that am using to update the workbook and also to check if the order is new or already launch.

The problem everytime the code is running it is adding the order having status "NS" again and again as the status is still NS. thus is duplicating several times.

Do Until Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_INdex - 1, Marketing_Ref_Client_Column - 1) = ""
        
                
                'check if order number exist on the workbook to updated
                check_order = Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_INdex - 1, Marketing_Ref_Client_Column - 1)
                
                If check_order = Range("Master_p!A1").Offset(Master_Row_INdex - 1, Master_ref_client - 1) Then
                GoTo skip_row
                End If
                
                'check if the order is new (NS) OR (LA) launch. If launch then no need to add the order number on the workbook.
                New_sample_status = Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_INdex - 1, Marketing_New_Order_Control_Ind_Column - 1)
                If New_sample_status = "LA" Then
                GoTo skip_row
                End If
                
                
                
                'assigning Ref Client
                Range("Master_p!A1").Offset(Master_Row_INdex - 1, Master_ref_client - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_INdex - 1, Marketing_Ref_Client_Column - 1)
                'assigning Ref kARINA
                Range("Master_p!A1").Offset(Master_Row_INdex - 1, Master_Ref - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_INdex - 1, Marketing_Ref_Karina_Column - 1)
                'assigning Ref SAISON
                Range("Master_p!A1").Offset(Master_Row_INdex - 1, Master_Saison - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_INdex - 1, Marketing_Saison_Column - 1)
                'assigning Ref MARKETING MANAGER
                Range("Master_p!A1").Offset(Master_Row_INdex - 1, Master_Marketing_Manager - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_INdex - 1, Marketing_Marketing_Manager_Column - 1)
                'assigning Ref MERCHANDISER
                Range("Master_p!A1").Offset(Master_Row_INdex - 1, Master_Merchandiser - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_INdex - 1, Marketing_Merchandiser_Column - 1)
                'assigning Ref CLIENT
                Range("Master_p!A1").Offset(Master_Row_INdex - 1, Master_Client - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_INdex - 1, Marketing_Client_Column - 1)
                'assigning Ref depart
                Range("Master_p!A1").Offset(Master_Row_INdex - 1, Master_Depart - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_INdex - 1, Marketing_Depart_Column - 1)
                'assigning Ref theme
                Range("Master_p!A1").Offset(Master_Row_INdex - 1, Master_Theme - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_INdex - 1, Marketing_Theme_Column - 1)
                'assigning Ref desc
                Range("Master_p!A1").Offset(Master_Row_INdex - 1, Master_Desc - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_INdex - 1, Marketing_Desc_Column - 1)
                'assigning Ref type echantiliion
                Range("Master_p!A1").Offset(Master_Row_INdex - 1, Master_Type_echantillion - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_INdex - 1, Marketing_Type_echantillion_Column - 1)
                'assigning Ref taille
                Range("Master_p!A1").Offset(Master_Row_INdex - 1, Master_Taille - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_INdex - 1, Marketing_Taille_Column - 1)
                
                'assigning Ref qty
                Range("Master_p!A1").Offset(Master_Row_INdex - 1, Master_Qty - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_INdex - 1, Marketing_Qty_Column - 1)
                
                'assigning Ref type lavage
                Range("Master_p!A1").Offset(Master_Row_INdex - 1, Master_Type_Lavage - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_INdex - 1, Marketing_Type_Lavage_Column - 1)
                'assigning Ref colori
                Range("Master_p!A1").Offset(Master_Row_INdex - 1, Master_Colori_Gmt_Dye - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_INdex - 1, Marketing_Colori_Gmt_Dye_Column - 1)
                 'assigning Ref valeur
                Range("Master_p!A1").Offset(Master_Row_INdex - 1, Master_Code_Valeur_ajouter - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_INdex - 1, Marketing_Valeur_Ajouter_Column - 1)
                'assigning date request merc
                Range("Master_p!A1").Offset(Master_Row_INdex - 1, Master_Date_Request_Merc - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_INdex - 1, Marketing_Date_Request_Merc_Column - 1)
                'assigning date livraison
                
                
                
                
                
        
skip_row:
             Row_INdex = Row_INdex + 1
            Master_Row_INdex = Master_Row_INdex + 1
         Loop

am really stuck with that actually , it shall be checking if the value exist, if so then skip the row else to update. I have tried doing this with thebelow code but it seems not working :

'check if order number exist on the workbook to updated
                check_order = Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_INdex - 1, Marketing_Ref_Client_Column - 1)
                
                If check_order = Range("Master_p!A1").Offset(Master_Row_INdex - 1, Master_ref_client - 1) Then
                GoTo skip_row
                End If

i will be much grateful if you can give some hints on that please as these files will be run on a network where everyone will update their data on it and its already duplicating now .

thanking you in advance.

[SamT]

That is not all of your code.
Please post the entire sub from
Sub Name()
.
.
.
to
.
.
.
End Sub

The detail that is causing the problem can be anywhere.

[SamT]

Column F,H,I,J can be blank to filled later for dates and Column K,L,M,N have data. When the code are running it is not copying the data that are found in column K,L,M,N as it is taking only column which has data before the blank column F,H,I,J.

Can you please see to that and advise .

Please map these variables to column letters: New_sample_status, Master_ref_client, Marketing_Ref_Client, Marketing_Depart, Master_Depart, Master_Client, Marketing_Client, Master_Marketing_Manager, Marketing_Marketing_Manager, Master_Merchandiser, Marketing_Merchandiser, Master_Ref_Karina, Marketing_Ref_Karina, Master_Saison, Marketing_Saison.

Also, looking at the code:

IF New Order Control Ind = "LA" then DO NOT COPY
And
IF Marketing Ref client = Master Ref Client Then DO NOT COPY
ELSE COPY ALL

[SamT]

I rewrote that part of your code to make it approximately 2 times faster. If you can remove all the "-1" math from the loop, it will be a bit more faster.

Dim Marketing As Worksheet
Dim Master As Worksheet

Set Marketing = Workbooks("Marketing_sample_planning.XLS").Sheets("Marketing")
Set Master = ThisWorkbook.Sheets("Master_p")

Row_Index = Row_Index + 1
Master_Row_Index = Master_Row_Index + 1

Do Until Marketing.Cells(Row_Index, Marketing_Ref_Client_Column - 1) = ""
     
     
     'check if order number exist on the workbook to updated
    check_order = Marketing.Cells(Row_Index, Marketing_Ref_Client_Column - 1)
     
    If check_order = Master.Cells(Master_Row_Index, Master_ref_client - 1) Then
        GoTo skip_row
    End If
     
     'check if the order is new (NS) OR (LA) launch. If launch then no need to add the order number on the workbook.
    New_sample_status = Marketing.Cells(Row_Index, Marketing_New_Order_Control_Ind_Column - 1)
    If New_sample_status = "LA" Then
        GoTo skip_row
    End If
     
     
     
     'assigning Ref Client
    Master.Cells(Master_Row_Index, Master_ref_client - 1) = _
      Marketing.Cells(Row_Index, Marketing_Ref_Client_Column - 1)
     'assigning Ref kARINA
    Master.Cells(Master_Row_Index, Master_Ref - 1) = _
      Marketing.Cells(Row_Index, Marketing_Ref_Karina_Column - 1)
     'assigning Ref SAISON
    Master.Cells(Master_Row_Index, Master_Saison - 1) = _
      Marketing.Cells(Row_Index, Marketing_Saison_Column - 1)
     'assigning Ref MARKETING MANAGER
    Master.Cells(Master_Row_Index, Master_Marketing_Manager - 1) = _
      Marketing.Cells(Row_Index, Marketing_Marketing_Manager_Column - 1)
     'assigning Ref MERCHANDISER
    Master.Cells(Master_Row_Index, Master_Merchandiser - 1) = _
      Marketing.Cells(Row_Index, Marketing_Merchandiser_Column - 1)
     'assigning Ref CLIENT
    Master.Cells(Master_Row_Index, Master_Client - 1) = _
      Marketing.Cells(Row_Index, Marketing_Client_Column - 1)
     'assigning Ref depart
    Master.Cells(Master_Row_Index, Master_Depart - 1) = _
      Marketing.Cells(Row_Index, Marketing_Depart_Column - 1)
     'assigning Ref theme
    Master.Cells(Master_Row_Index, Master_Theme - 1) = _
      Marketing.Cells(Row_Index, Marketing_Theme_Column - 1)
     'assigning Ref desc
    Master.Cells(Master_Row_Index, Master_Desc - 1) = _
      Marketing.Cells(Row_Index, Marketing_Desc_Column - 1)
     'assigning Ref type echantiliion
    Master.Cells(Master_Row_Index, Master_Type_echantillion - 1) = _
      Marketing.Cells(Row_Index, Marketing_Type_echantillion_Column - 1)
     'assigning Ref taille
    Master.Cells(Master_Row_Index, Master_Taille - 1) = _
      Marketing.Cells(Row_Index, Marketing_Taille_Column - 1)
     'assigning Ref qty
    Master.Cells(Master_Row_Index, Master_Qty - 1) = _
      Marketing.Cells(Row_Index, Marketing_Qty_Column - 1)
     'assigning Ref type lavage
    Master.Cells(Master_Row_Index, Master_Type_Lavage - 1) = _
     Marketing.Cells(Row_Index, Marketing_Type_Lavage_Column - 1)
     'assigning Ref colori
    Master.Cells(Master_Row_Index, Master_Colori_Gmt_Dye - 1) = _
      Marketing.Cells(Row_Index, Marketing_Colori_Gmt_Dye_Column - 1)
     'assigning Ref valeur
    Master.Cells(Master_Row_Index, Master_Code_Valeur_ajouter - 1) = _
      Marketing.Cells(Row_Index, Marketing_Valeur_Ajouter_Column - 1)
     'assigning date request merc
    Master.Cells(Master_Row_Index, Master_Date_Request_Merc - 1) = _
    Marketing.Cells(Row_Index, Marketing_Date_Request_Merc_Column - 1)
     'assigning date livraison
     
skip_row:
    Row_Index = Row_Index + 1
    Master_Row_Index = Master_Row_Index + 1
Loop

End Sub

[SamT]

am really stuck with that actually , it shall be checking if the value exist, if so then skip the row else to update. I have tried doing this with thebelow code but it seems not working :

 'check if order number exist on the workbook to updated
check_order = Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_INdex - 1, Marketing_Ref_Client_Column - 1) 
 
If check_order = Range("Master_p!A1").Offset(Master_Row_INdex - 1, Master_ref_client - 1) Then 
    GoTo skip_row 
End If

This is coded with Variables from my previous post

Dim Order_Found As Range
     
    Set Order_Found = Master.Columns(Master_ref_client - 1).Find(check_order)
        If Not Order_Found Is Nothing Then GoTo skip_row 
    End If

If Not item Is Nothing = item is something = item was found

[VISHAL120]

Hi SamT,

Many thanks again for your kind support and sorry for the late reply.

I have try adjusting your propose solution with the actual code and continue to have errors. I ma sending the complete code now as you stated the problem can be else where :

Here is the complete code that i have been using:

Sub Read_Data_From_Marketing()


With Application
    '.Cursor = xlWait
    .Cursor = xlDefault
    .EnableEvents = False
    .ScreenUpdating = False
End With




'INITIALSING CODE ON THE SPECIFIC WORKSHEET AND WORKBOOK.


    ThisWorkbook.Activate
    Current_ACTIVESHEET = ActiveSheet.Name
    Current_Selection = Selection.Address
    
    start_time = Time
    'Call SamT
   SampleMaster_file_accessible = False
   


    On Error GoTo After_File_Test
    
        Application.StatusBar = "Sample_Management << Connecting to Master Sample File>>"
        
        
        'OPENING THE Master Sample
        'SEARCH CONNECTION SHEETS
        Sheets("CONNECTION").Visible = True
        Sheets("Connection").Select
        
        
                    For Each CELL In Range("B3")


                        If CELL.Value <> "" Then


                            Workbooks.Open Filename:=CELL.Value, ReadOnly:=True
                        End If
                    Next CELL
 
                       
     
   SampleMaster_file_accessible = True


After_File_Test:
    If Not (SampleMaster_file_accessible) _
    Then
        MsgBox ("Sample Master File Not accessible;Connection Problem !! ")
        GoTo Exit_Sub_2
    End If
'READ COLUMN INDEX FROM MARKETING


Marketing_Client_Column = Range("Marketing_Client_Column").Column
Marketing_Colori_Gmt_Dye_Column = Range("Marketing_Colori_Gmt_Dye_Column").Column


Marketing_Commentaire_Client_Column = Range("Marketing_Commentaire_Client_Column").Column
Marketing_Commentaires_Merc_Column = Range("Marketing_Commentaires_Merc_Column").Column
Marketing_Courrier_No_Courier_Service_Column = Range("Marketing_Courrier_No_Courier_Service_Column").Column
Marketing_Date_Envoyer_Client_Column = Range("Marketing_Date_Envoyer_Client_Column").Column


Marketing_Date_Livraison_Ech_Column = Range("Marketing_Date_Livraison_Ech_Column").Column
Marketing_Date_Livraison_Ech_Reviser_Column = Range("Marketing_Date_Livraison_Ech_Reviser_Column").Column
Marketing_Date_Request_Merc_Column = Range("Marketing_Date_Request_Merc_Column").Column
Marketing_Depart_Column = Range("Marketing_Depart_Column").Column
Marketing_Desc_Column = Range("Marketing_Desc_Column").Column




Marketing_Keep_KI_Column = Range("Marketing_Keep_KI_Column").Column
Marketing_Marketing_Manager_Column = Range("Marketing_Marketing_Manager_Column").Column
Marketing_Merchandiser_Column = Range("Marketing_Merchandiser_Column").Column
Marketing_Month_Column = Range("Marketing_Month_Column").Column
Marketing_New_Order_Control_Ind_Column = Range("Marketing_New_Order_Control_Ind_Column").Column
Marketing_Qty_Column = Range("Marketing_Qty_Column").Column
Marketing_Ref_Client_Column = Range("Marketing_Ref_Client_Column").Column
Marketing_Ref_Karina_Column = Range("Marketing_Ref_Karina_Column").Column
Marketing_Saison_Column = Range("Marketing_Saison_Column").Column
Marketing_Statue_Ech_Auto_Column = Range("Marketing_Statue_Ech_Auto_Column").Column
Marketing_Taille_Column = Range("Marketing_Taille_Column").Column
Marketing_Theme_Column = Range("Marketing_Theme_Column").Column


Marketing_Type_echantillion_Column = Range("Marketing_Type_echantillion_Column").Column
Marketing_Type_Lavage_Column = Range("Marketing_Type_Lavage_Column").Column
Marketing_Valeur_Ajouter_Column = Range("Marketing_Valeur_Ajouter_Column").Column
Marketing_Week_Column = Range("Marketing_Week_Column").Column
Marketing_Year_Column = Range("Marketing_Year_Column").Column


Marketing_header_row = Range("Marketing_header_row").Row + 1


'REMOVING FILTER ON MARKETING FILE
Sheets("Marketing").Activate
ActiveSheet.Unprotect
If ActiveSheet.AutoFilterMode _
Then Selection.AutoFilter


'------------------------------------------------------------


'VERIFY IF Marketing file IS EMPTY
    Sheets("Marketing").Activate
    Market_Data_Row_Start = Marketing_header_row
    Market_Data_Row_End = Range("A65536").Offset(0, Marketing_Ref_Client_Column - 1).End(xlUp).Row
    
    
'    If Market_Data_Row_End < Market_Data_Row_Start _
'    Then
'        MsgBox ("NO Data to Search")
'        GoTo Exit_Sub
'    End If
    
    'SORT DATA IN MARKETING FILE
    Sheets("Marketing").Activate
    
    Application.StatusBar = "Sample Analysis << Sorting Data on Marketing file>>"
    
    Range("Marketing!A" & Market_Data_Row_Start & ":IV" & Market_Data_Row_End).Sort _
        Key1:=Range("Marketing!A1").Offset(Market_Data_Row_Start - 1, Marketing_Ref_Client_Column - 1), _
        Order1:=xlAscending, _
        Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
        
     '-------------------------------------------------------------------------------------
        
        
     'NOW WORKING WITH THE Master File


    'REMOVING FILTER ON THE MASTER
     ThisWorkbook.Activate
    Sheets("Master_p").Activate
    ActiveSheet.Unprotect
    If ActiveSheet.AutoFilterMode _
    Then Selection.AutoFilter
    ActiveSheet.Unprotect
    
    
' reading column on master
Master_Client = Range("Master_Client").Column
Master_Marketing_Manager = Range("Master_Marketing_Manager").Column
Master_Merchandiser = Range("Master_Merchandiser").Column
Master_ref_client = Range("Master_ref_client").Column
Master_Ref_Karina = Range("Master_Ref_Karina").Column
Master_Saison = Range("Master_Saison").Column
Master_Depart = Range("Master_Depart").Column


 '------------------------------------------------------------------
 Master_Code_Valeur_ajouter = Range("Master_Code_Valeur_ajouter").Column


Master_Commentaire_Client = Range("Master_Commentaire_Client").Column
Master_Commentaire_Fabric = Range("Master_Commentaire_Fabric").Column


Master_Commentaires_Merc = Range("Master_Commentaires_Merc").Column


Master_Courrier_No_Courier_Service = Range("Master_Courrier_No_Courier_Service").Column
Master_Colori_Gmt_Dye = Range("Master_Colori_Gmt_Dye").Column
Master_Date_Envoyer_Client = Range("Master_Date_Envoyer_Client").Column


Master_Date_Livraison_Ech = Range("Master_Date_Livraison_Ech").Column
Master_Date_Livraison_Ech_Reviser = Range("Master_Date_Livraison_Ech_Reviser").Column


Master_Date_Request_Merc = Range("Master_Date_Request_Merc").Column


Master_Desc = Range("Master_Desc").Column


Master_header_row = Range("Master_Header_Row").Column
Master_Keep_KI = Range("Master_Keep_KI").Column


Master_Month = Range("Master_Month").Column
Master_New_Order_Control_Ind = Range("Master_New_Order_Control_Ind").Column




Master_Qty = Range("Master_Qty").Column


Master_Qty_Livrer_Reel = Range("Master_Qty_Livrer_Reel").Column
Master_Statue_Ech_Auto = Range("Master_Statue_Ech_Auto").Column


Master_Taille = Range("Master_Taille").Column
Master_Theme = Range("Master_Theme").Column


Master_Type_echantillion = Range("Master_Type_echantillion").Column
Master_Type_Lavage = Range("Master_Type_Lavage").Column


Master_Valeur_Ajouter = Range("Master_Valeur_Ajouter").Column
Master_Week = Range("Master_Week").Column
Master_Year = Range("Master_Year").Column
'--------------------------------------------------------
       
 Master_header_row = Range("Master_Header_Row").Row + 1
 Master_Data_Row_End = Range("A65536").Offset(0, Master_ref_client - 1).End(xlUp).Row
'--------------------------------------------------------
'  Range("Marketing!A" & Market_Data_Row_Start & ":IV" & Market_Data_Row_End).Sort _
'        Key1:=Range("Marketing!A1").Offset(Market_Data_Row_Start - 1, Marketing_Ref_Client_Column - 1), _
'        Order1:=xlAscending, _
'        Header:=xlNo, _
'        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom


'-------------------------------------------------------------------------
Application.StatusBar = "Sample Analysis << Sorting Data on master file>>"
    Sheets("master_p").Activate
    Range("a4").Select
    Range("master_p!A" & Master_header_row & ":IV" & Master_Data_Row_End).Sort _
        Key1:=Range("master_p!A1").Offset(Master_header_row - 1, Master_Data_Row_End - 1), _
        Order1:=xlAscending, _
        Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
        
  '------------------------------------------------------------------------------------------------------------------
          Row_Index = Marketing_header_row ' Read the start row on the marketing
          Master_Row_Index = Master_header_row  ' Read the start row on the master


        
        
        Do Until Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_Index - 1, Marketing_Ref_Client_Column - 1) = ""
        
                
                'check if order number exist on the workbook to updated
                check_Order = Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_Index - 1, Marketing_Ref_Client_Column - 1)
                
                If check_Order = Range("Master_p!A1").Offset(Master_Row_Index - 1, Master_ref_client - 1) Then
                GoTo skip_row
                End If
                
                'check if the order is new (NS) OR (LA) launch. If launch then no need to add the order number on the workbook.
                New_sample_status = Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_Index - 1, Marketing_New_Order_Control_Ind_Column - 1)
                If New_sample_status = "LA" Then
                GoTo skip_row
                End If
                
                
                
                'assigning Ref Client
                Range("Master_p!A1").Offset(Master_Row_Index - 1, Master_ref_client - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_Index - 1, Marketing_Ref_Client_Column - 1)
                'assigning Ref kARINA
                Range("Master_p!A1").Offset(Master_Row_Index - 1, Master_Ref_Karina - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_Index - 1, Marketing_Ref_Karina_Column - 1)
                'assigning Ref SAISON
                Range("Master_p!A1").Offset(Master_Row_Index - 1, Master_Saison - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_Index - 1, Marketing_Saison_Column - 1)
                'assigning Ref MARKETING MANAGER
                Range("Master_p!A1").Offset(Master_Row_Index - 1, Master_Marketing_Manager - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_Index - 1, Marketing_Marketing_Manager_Column - 1)
                'assigning Ref MERCHANDISER
                Range("Master_p!A1").Offset(Master_Row_Index - 1, Master_Merchandiser - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_Index - 1, Marketing_Merchandiser_Column - 1)
                'assigning Ref CLIENT
                Range("Master_p!A1").Offset(Master_Row_Index - 1, Master_Client - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_Index - 1, Marketing_Client_Column - 1)
                'assigning Ref depart
                Range("Master_p!A1").Offset(Master_Row_Index - 1, Master_Depart - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_Index - 1, Marketing_Depart_Column - 1)
                'assigning Ref theme
                Range("Master_p!A1").Offset(Master_Row_Index - 1, Master_Theme - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_Index - 1, Marketing_Theme_Column - 1)
                'assigning Ref desc
                Range("Master_p!A1").Offset(Master_Row_Index - 1, Master_Desc - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_Index - 1, Marketing_Desc_Column - 1)
                'assigning Ref type echantiliion
                Range("Master_p!A1").Offset(Master_Row_Index - 1, Master_Type_echantillion - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_Index - 1, Marketing_Type_echantillion_Column - 1)
                'assigning Ref taille
                Range("Master_p!A1").Offset(Master_Row_Index - 1, Master_Taille - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_Index - 1, Marketing_Taille_Column - 1)
                
                'assigning Ref qty
                Range("Master_p!A1").Offset(Master_Row_Index - 1, Master_Qty - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_Index - 1, Marketing_Qty_Column - 1)
                'assigning Ref keep ki
                Range("Master_p!A1").Offset(Master_Row_Index - 1, Master_Keep_KI - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_Index - 1, Marketing_Keep_KI_Column - 1)
                'assigning Ref type lavage
                Range("Master_p!A1").Offset(Master_Row_Index - 1, Master_Type_Lavage - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_Index - 1, Marketing_Type_Lavage_Column - 1)
                'assigning Ref colori
                Range("Master_p!A1").Offset(Master_Row_Index - 1, Master_Colori_Gmt_Dye - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_Index - 1, Marketing_Colori_Gmt_Dye_Column - 1)
                 'assigning Ref valeur
                Range("Master_p!A1").Offset(Master_Row_Index - 1, Master_Code_Valeur_ajouter - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_Index - 1, Marketing_Valeur_Ajouter_Column - 1)
                'assigning date request merc
                Range("Master_p!A1").Offset(Master_Row_Index - 1, Master_Date_Request_Merc - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_Index - 1, Marketing_Date_Request_Merc_Column - 1)
                'assigning date livraison
                Range("Master_p!A1").Offset(Master_Row_Index - 1, Master_Date_Livraison_Ech - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_Index - 1, Marketing_Date_Livraison_Ech_Column - 1)
                'assigning date livraison reviser
                Range("Master_p!A1").Offset(Master_Row_Index - 1, Master_Date_Livraison_Ech_Reviser - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_Index - 1, Marketing_Date_Livraison_Ech_Reviser_Column - 1)
                'assigning date comentaire merc
                Range("Master_p!A1").Offset(Master_Row_Index - 1, Master_Commentaires_Merc - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_Index - 1, Marketing_Commentaires_Merc_Column - 1)
                'assigning date comentaire client
                Range("Master_p!A1").Offset(Master_Row_Index - 1, Master_Commentaire_Client - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_Index - 1, Marketing_Commentaire_Client_Column - 1)
                'assigning date envoyer client
                Range("Master_p!A1").Offset(Master_Row_Index - 1, Master_Date_Envoyer_Client - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_Index - 1, Marketing_Date_Envoyer_Client_Column - 1)
                'assigning courier
                Range("Master_p!A1").Offset(Master_Row_Index - 1, Master_Courrier_No_Courier_Service - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_Index - 1, Marketing_Courrier_No_Courier_Service_Column - 1)
                'assigning week as per sample sent to merc
                Range("Master_p!A1").Offset(Master_Row_Index - 1, Master_Week - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_Index - 1, Marketing_Week_Column - 1)
                'assigning month as per sample sent to merc
                Range("Master_p!A1").Offset(Master_Row_Index - 1, Master_Month - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_Index - 1, Marketing_Month_Column - 1)
                'assigning year as per sample sent to merc
                Range("Master_p!A1").Offset(Master_Row_Index - 1, Master_Year - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_Index - 1, Marketing_Year_Column - 1)
                'assigning new orde control
                Range("Master_p!A1").Offset(Master_Row_Index - 1, Master_New_Order_Control_Ind - 1) = _
                Range("[Marketing_sample_planning.XLS]Marketing!A1").Offset(Row_Index - 1, Marketing_New_Order_Control_Ind_Column - 1)
                
                
                
                
        
skip_row:
             Row_Index = Row_Index + 1
            Master_Row_Index = Master_Row_Index + 1
         Loop


Call Check_Sort_data_after_update


Exit_Sub_2:


    ThisWorkbook.Activate
    Sheets(Current_ACTIVESHEET).Activate
    Range(Current_Selection).Select


        'HIDE CONNECTION SHEETS
        Sheets("CONNECTION").Visible = xlSheetHidden
        
        end_time = Time
    time_string = Format(end_time - start_time, " ss")
    
      Application.StatusBar = " Sample Master Data Updated in " & time_string & "Secs"
      
With Application
    .Cursor = xlDefault
    .EnableEvents = True
    .ScreenUpdating = True


End With
End Sub

And Here is the code that i have try adjusting with your proposal:

Sub Proposal_code_SamT()


With Application
    '.Cursor = xlWait
    .Cursor = xlDefault
    .EnableEvents = False
    .ScreenUpdating = False
End With




'INITIALSING CODE ON THE SPECIFIC WORKSHEET AND WORKBOOK.


    ThisWorkbook.Activate
    Current_ACTIVESHEET = ActiveSheet.Name
    Current_Selection = Selection.Address
    
    start_time = Time
    'Call SamT
   SampleMaster_file_accessible = False
    
    
    
    


    On Error GoTo After_File_Test
    
        Application.StatusBar = "Sample_Management << Connecting to Master Sample File>>"
        
        
        'OPENING THE Master Sample
        'SEARCH CONNECTION SHEETS
        Sheets("CONNECTION").Visible = True
        Sheets("Connection").Select
        
        
                    For Each CELL In Range("B3")


                        If CELL.Value <> "" Then


                            Workbooks.Open Filename:=CELL.Value, ReadOnly:=True
                        End If
                    Next CELL
 
                       
     
   SampleMaster_file_accessible = True


After_File_Test:
    If Not (SampleMaster_file_accessible) _
    Then
        MsgBox ("Sample Master File Not accessible;Connection Problem !! ")
        GoTo Exit_Sub_2
    End If


'READ COLUMN INDEX FROM MARKETING


Marketing_Client_Column = Range("Marketing_Client_Column").Column
Marketing_Colori_Gmt_Dye_Column = Range("Marketing_Colori_Gmt_Dye_Column").Column


Marketing_Commentaire_Client_Column = Range("Marketing_Commentaire_Client_Column").Column
Marketing_Commentaires_Merc_Column = Range("Marketing_Commentaires_Merc_Column").Column
Marketing_Courrier_No_Courier_Service_Column = Range("Marketing_Courrier_No_Courier_Service_Column").Column
Marketing_Date_Envoyer_Client_Column = Range("Marketing_Date_Envoyer_Client_Column").Column


Marketing_Date_Livraison_Ech_Column = Range("Marketing_Date_Livraison_Ech_Column").Column
Marketing_Date_Livraison_Ech_Reviser_Column = Range("Marketing_Date_Livraison_Ech_Reviser_Column").Column
Marketing_Date_Request_Merc_Column = Range("Marketing_Date_Request_Merc_Column").Column
Marketing_Depart_Column = Range("Marketing_Depart_Column").Column
Marketing_Desc_Column = Range("Marketing_Desc_Column").Column




Marketing_Keep_KI_Column = Range("Marketing_Keep_KI_Column").Column
Marketing_Marketing_Manager_Column = Range("Marketing_Marketing_Manager_Column").Column
Marketing_Merchandiser_Column = Range("Marketing_Merchandiser_Column").Column
Marketing_Month_Column = Range("Marketing_Month_Column").Column
Marketing_New_Order_Control_Ind_Column = Range("Marketing_New_Order_Control_Ind_Column").Column
Marketing_Qty_Column = Range("Marketing_Qty_Column").Column
Marketing_Ref_Client_Column = Range("Marketing_Ref_Client_Column").Column
Marketing_Ref_Karina_Column = Range("Marketing_Ref_Karina_Column").Column
Marketing_Saison_Column = Range("Marketing_Saison_Column").Column
Marketing_Statue_Ech_Auto_Column = Range("Marketing_Statue_Ech_Auto_Column").Column
Marketing_Taille_Column = Range("Marketing_Taille_Column").Column
Marketing_Theme_Column = Range("Marketing_Theme_Column").Column


Marketing_Type_echantillion_Column = Range("Marketing_Type_echantillion_Column").Column
Marketing_Type_Lavage_Column = Range("Marketing_Type_Lavage_Column").Column
Marketing_Valeur_Ajouter_Column = Range("Marketing_Valeur_Ajouter_Column").Column
Marketing_Week_Column = Range("Marketing_Week_Column").Column
Marketing_Year_Column = Range("Marketing_Year_Column").Column


Marketing_header_row = Range("Marketing_header_row").Row + 1


'REMOVING FILTER ON MARKETING FILE
Sheets("Marketing").Activate
ActiveSheet.Unprotect
If ActiveSheet.AutoFilterMode _
Then Selection.AutoFilter


'------------------------------------------------------------


'VERIFY IF Marketing file IS EMPTY
    Sheets("Marketing").Activate
    Market_Data_Row_Start = Marketing_header_row
    Market_Data_Row_End = Range("A65536").Offset(0, Marketing_Ref_Client_Column - 1).End(xlUp).Row


'------------------------------------------------------------


'VERIFY IF Marketing file IS EMPTY
    Sheets("Marketing").Activate
    Market_Data_Row_Start = Marketing_header_row
    Market_Data_Row_End = Range("A65536").Offset(0, Marketing_Ref_Client_Column - 1).End(xlUp).Row
    
    
'    If Market_Data_Row_End < Market_Data_Row_Start _
'    Then
'        MsgBox ("NO Data to Search")
'        GoTo Exit_Sub
'    End If
    
    'SORT DATA IN MARKETING FILE
    Sheets("Marketing").Activate
    
    Application.StatusBar = "Sample Analysis << Sorting Data on Marketing file>>"
    
    Range("Marketing!A" & Market_Data_Row_Start & ":IV" & Market_Data_Row_End).Sort _
        Key1:=Range("Marketing!A1").Offset(Market_Data_Row_Start - 1, Marketing_Ref_Client_Column - 1), _
        Order1:=xlAscending, _
        Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
        
     '-------------------------------------------------------------------------------------
        
        
     'NOW WORKING WITH THE Master File


    'REMOVING FILTER ON THE MASTER
    ThisWorkbook.Activate
    Sheets("Master_p").Activate
    ActiveSheet.Unprotect
    If ActiveSheet.AutoFilterMode _
    Then Selection.AutoFilter
    ActiveSheet.Unprotect
' reading column on master
        
Master_Client = Range("Master_Client").Column
Master_Marketing_Manager = Range("Master_Marketing_Manager").Column
Master_Merchandiser = Range("Master_Merchandiser").Column
Master_ref_client = Range("Master_ref_client").Column
Master_Ref_Karina = Range("Master_Ref_Karina").Column
Master_Saison = Range("Master_Saison").Column
Master_Depart = Range("Master_Depart").Column


 '------------------------------------------------------------------
 Master_Code_Valeur_ajouter = Range("Master_Code_Valeur_ajouter").Column
Master_Acces_Date = Range("Master_Acces_Date").Column
Master_Accessoires = Range("Master_Accessoires").Column
Master_Code_Tissu_1 = Range("Master_Code_Tissu_1").Column
Master_Code_Tissu_2 = Range("Master_Code_Tissu_2").Column
Master_Code_Tissu_3 = Range("Master_Code_Tissu_3").Column
Master_Code_Tissu_4 = Range("Master_Code_Tissu_4").Column
Master_Code_Tissu_5 = Range("Master_Code_Tissu_5").Column
Master_Colori_Gmt_Dye = Range("Master_Colori_Gmt_Dye").Column
Master_Commentaire = Range("Master_Commentaire").Column
Master_Commentaire_Acc = Range("Master_Commentaire_Acc").Column
Master_Commentaire_Client = Range("Master_Commentaire_Client").Column
Master_Commentaire_Fabric = Range("Master_Commentaire_Fabric").Column
Master_Commentaires_Development = Range("Master_Commentaires_Development").Column
Master_Commentaires_Merc = Range("Master_Commentaires_Merc").Column
Master_Commenttaire_Atelier = Range("Master_Commenttaire_Atelier").Column
Master_Courrier_No_Courier_Service = Range("Master_Courrier_No_Courier_Service").Column
Master_Date_Broid_Man_Ach = Range("Master_Date_Broid_Man_Ach").Column
Master_Date_Broid_Man_Exp = Range("Master_Date_Broid_Man_Exp").Column
Master_Date_Broid_MC_Ach = Range("Master_Date_Broid_MC_Ach").Column
Master_Date_Broid_MC_Exp = Range("Master_Date_Broid_MC_Exp").Column
Master_Date_Coupe_Exp = Range("Master_Date_Coupe_Exp").Column
Master_Date_Coupe_Rec = Range("Master_Date_Coupe_Rec").Column
Master_Date_Envoyer_Client = Range("Master_Date_Envoyer_Client").Column
Master_Date_Lancer_Atelier = Range("Master_Date_Lancer_Atelier").Column
Master_Date_lavage_Ach = Range("Master_Date_lavage_Ach").Column
Master_Date_Lavage_Exp = Range("Master_Date_Lavage_Exp").Column
Master_Date_Livraison_Ech = Range("Master_Date_Livraison_Ech").Column
Master_Date_Livraison_Ech_Reviser = Range("Master_Date_Livraison_Ech_Reviser").Column
Master_Date_Livrer_Reel_a_Merc = Range("Master_Date_Livrer_Reel_a_Merc").Column
Master_Date_Make_Up_Ach = Range("Master_Date_Make_Up_Ach").Column
Master_Date_Make_Up_Exp = Range("Master_Date_Make_Up_Exp").Column
Master_Date_Patron_Ach = Range("Master_Date_Patron_Ach").Column
Master_Date_Patron_Exp = Range("Master_Date_Patron_Exp").Column
Master_Date_Prevue_Sortie_Atelier = Range("Master_Date_Prevue_Sortie_Atelier").Column
Master_Date_Print_Ach = Range("Master_Date_Print_Ach").Column
Master_Date_Print_Exp = Range("Master_Date_Print_Exp").Column
Master_Date_QC_Ach = Range("Master_Date_QC_Ach").Column
Master_Date_QC_Exp = Range("Master_Date_QC_Exp").Column
Master_Date_Request_Merc = Range("Master_Date_Request_Merc").Column


Master_Desc = Range("Master_Desc").Column
Master_Fabrci_Date_Rec = Range("Master_Fabrci_Date_Rec").Column
Master_Fabric_Date_Exp = Range("Master_Fabric_Date_Exp").Column
Master_header_row = Range("Master_Header_Row").Column
Master_Keep_KI = Range("Master_Keep_KI").Column


Master_Month = Range("Master_Month").Column
Master_New_Order_Control_Ind = Range("Master_New_Order_Control_Ind").Column
Master_Opr_Make_Up = Range("Master_Opr_Make_Up").Column
Master_Patronnier = Range("Master_Patronnier").Column
Master_Process_Route = Range("Master_Process_Route").Column
Master_Punching_Date_Ach = Range("Master_Punching_Date_Ach").Column
Master_Punching_Date_Exp = Range("Master_Punching_Date_Exp").Column
Master_Qty = Range("Master_Qty").Column
Master_Qty_Coupe = Range("Master_Qty_Coupe").Column
Master_Qty_Livrer_Reel = Range("Master_Qty_Livrer_Reel").Column
Master_Statue_Ech_Auto = Range("Master_Statue_Ech_Auto").Column
Master_Supplier_1 = Range("Master_Supplier_1").Column
Master_Supplier_2 = Range("Master_Supplier_2").Column
Master_Supplier_3 = Range("Master_Supplier_3").Column
Master_Supplier_4 = Range("Master_Supplier_4").Column
Master_Supplier_5 = Range("Master_Supplier_5").Column
Master_Taille = Range("Master_Taille").Column
Master_Theme = Range("Master_Theme").Column
Master_Tissue_Concat = Range("Master_Tissue_Concat").Column
Master_Type_echantillion = Range("Master_Type_echantillion").Column
Master_Type_Lavage = Range("Master_Type_Lavage").Column
Master_Type_Tissu_1 = Range("Master_Type_Tissu_1").Column
Master_Type_Tissu_2 = Range("Master_Type_Tissu_2").Column
Master_Type_Tissu_3 = Range("Master_Type_Tissu_3").Column
Master_Type_Tissu_4 = Range("Master_Type_Tissu_4").Column
Master_Type_Tissu_5 = Range("Master_Type_Tissu_5").Column
Master_Valeur_Ajouter = Range("Master_Valeur_Ajouter").Column
Master_Week = Range("Master_Week").Column
Master_Year = Range("Master_Year").Column
'--------------------------------------------------------
Master_Gms2_5 = Range("Master_Gms2_5").Column
Master_Compo5 = Range("Master_Compo5").Column
Master_Gms2_4 = Range("Master_Gms2_4").Column
Master_Compo4 = Range("Master_Compo4").Column
Master_Gms2_3 = Range("Master_Gms2_3").Column
Master_Compo3 = Range("Master_Compo3").Column
Master_Gms2_2 = Range("Master_Gms2_2").Column
Master_Compo2 = Range("Master_Compo2").Column
Master_Gms2_1 = Range("Master_Gms2_1").Column
Master_Compo1 = Range("Master_Compo1").Column


        
 Master_header_row = Range("Master_Header_Row").Row + 1
        
''--------------------------------------------------------
    Row_Index = Marketing_header_row ' Read the start row on the marketing
    Master_Row_Index = Master_header_row  ' Read the start row on the master
''-------------------------------------------------------------------------


























'//////////////////////////////////////////////////////////////////////////////////////////////
Dim Marketing As Worksheet
Dim Master As Worksheet
 
Set Marketing = Workbooks("Marketing_sample_planning.XLS").Sheets("Marketing")
Set Master = ThisWorkbook.Sheets("Master_p")
 
Row_Index = Row_Index + 1
Master_Row_Index = Master_Row_Index + 1
 
Do Until Marketing.Cells(Row_Index, Marketing_Ref_Client_Column - 1) = ""
     
     
     'check if order number exist on the workbook to updated
    check_Order = Marketing.Cells(Row_Index, Marketing_Ref_Client_Column - 1)
     
    If check_Order = Master.Cells(Master_Row_Index, Master_ref_client - 1) Then
        GoTo skip_row
    End If
     
     'check if the order is new (NS) OR (LA) launch. If launch then no need to add the order number on the workbook.
    New_sample_status = Marketing.Cells(Row_Index, Marketing_New_Order_Control_Ind_Column - 1)
    If New_sample_status = "LA" Then
        GoTo skip_row
    End If
     
     
     
     'assigning Ref Client
    Master.Cells(Master_Row_Index, Master_ref_client - 1) = _
    Marketing.Cells(Row_Index, Marketing_Ref_Client_Column - 1)
     'assigning Ref kARINA
    Master.Cells(Master_Row_Index, Master_Ref - 1) = _
    Marketing.Cells(Row_Index, Marketing_Ref_Karina_Column - 1)
     'assigning Ref SAISON
    Master.Cells(Master_Row_Index, Master_Saison - 1) = _
    Marketing.Cells(Row_Index, Marketing_Saison_Column - 1)
     'assigning Ref MARKETING MANAGER
    Master.Cells(Master_Row_Index, Master_Marketing_Manager - 1) = _
    Marketing.Cells(Row_Index, Marketing_Marketing_Manager_Column - 1)
     'assigning Ref MERCHANDISER
    Master.Cells(Master_Row_Index, Master_Merchandiser - 1) = _
    Marketing.Cells(Row_Index, Marketing_Merchandiser_Column - 1)
     'assigning Ref CLIENT
    Master.Cells(Master_Row_Index, Master_Client - 1) = _
    Marketing.Cells(Row_Index, Marketing_Client_Column - 1)
     'assigning Ref depart
    Master.Cells(Master_Row_Index, Master_Depart - 1) = _
    Marketing.Cells(Row_Index, Marketing_Depart_Column - 1)
     'assigning Ref theme
    Master.Cells(Master_Row_Index, Master_Theme - 1) = _
    Marketing.Cells(Row_Index, Marketing_Theme_Column - 1)
     'assigning Ref desc
    Master.Cells(Master_Row_Index, Master_Desc - 1) = _
    Marketing.Cells(Row_Index, Marketing_Desc_Column - 1)
     'assigning Ref type echantiliion
    Master.Cells(Master_Row_Index, Master_Type_echantillion - 1) = _
    Marketing.Cells(Row_Index, Marketing_Type_echantillion_Column - 1)
     'assigning Ref taille
    Master.Cells(Master_Row_Index, Master_Taille - 1) = _
    Marketing.Cells(Row_Index, Marketing_Taille_Column - 1)
     'assigning Ref qty
    Master.Cells(Master_Row_Index, Master_Qty - 1) = _
    Marketing.Cells(Row_Index, Marketing_Qty_Column - 1)
     'assigning Ref type lavage
    Master.Cells(Master_Row_Index, Master_Type_Lavage - 1) = _
    Marketing.Cells(Row_Index, Marketing_Type_Lavage_Column - 1)
     'assigning Ref colori
    Master.Cells(Master_Row_Index, Master_Colori_Gmt_Dye - 1) = _
    Marketing.Cells(Row_Index, Marketing_Colori_Gmt_Dye_Column - 1)
     'assigning Ref valeur
    Master.Cells(Master_Row_Index, Master_Code_Valeur_ajouter - 1) = _
    Marketing.Cells(Row_Index, Marketing_Valeur_Ajouter_Column - 1)
     'assigning date request merc
    Master.Cells(Master_Row_Index, Master_Date_Request_Merc - 1) = _
    Marketing.Cells(Row_Index, Marketing_Date_Request_Merc_Column - 1)
     'assigning date livraison
     
skip_row:
    Row_Index = Row_Index + 1
    Master_Row_Index = Master_Row_Index + 1
Loop


Exit_Sub_2:


    ThisWorkbook.Activate
    Sheets(Current_ACTIVESHEET).Activate
    Range(Current_Selection).Select


        'HIDE CONNECTION SHEETS
        Sheets("CONNECTION").Visible = xlSheetHidden
        
        end_time = Time
    time_string = Format(end_time - start_time, " ss")
    
      Application.StatusBar = " Sample Master Data Updated in " & time_string & "Secs"
      
With Application
    .Cursor = xlDefault
    .EnableEvents = True
    .ScreenUpdating = True


End With
End Sub

In fact what i am guessing is to use Access as database to manupulate the data as this will be updated by different users and Excel as front end. BUT i do not have sound experience of manupulating access with Excel. If you can guide on these please, connection, saving , updating and deleting this will be of great help to me. AS the other reporting and analysis coding will be much manageable for me to do. This is only biggest problem am having with this project. The main aspest of this project is a follow up from marketing, Product development, sourcing dept and sample room where sample is created from Marketing which further update a master and from master everyone make follow up and updates the dates and situation of the samples. That is why i am thinking of using MS Access as database .

Many thanks in advance for your kind tips and helpful hand.

[SamT]

Take a look at the modules in this attachment for some better ideas about using so many variables.

Let me know which style you prefer. I use UltraEdit for this kind of work and can create the lists in a few minutes for you.

I have to run some chores, so I'll look at the actual code later.

[VISHAL120]

Hi SamT,

I really appreciate your help and advise. In fact what I do actually is I named each column as per the header that i have defined that is why may the name are long. Please find attached a sample of one of my file where i have named each column. I do so because if ever some of the user just insert a column accidentally ( which has occur in many cases with some of the user) this normally do not affect the data being manipulated as it will still be sending to the named define column.

I know this is very time consuming but now as you are advising to use ENUM as you mentioned may be this will be the best way to go with when i will have large data manipulation.

When opening the file i see some error on the module Modglobasl3 at Mkt_ Mkt_Manager_Col = 23 saying: compile error Invlaid Inside Enum.

thanks again for the help and advise.