PDA

View Full Version : Loop for spefic condition not working



VISHAL120
08-19-2015, 02:29 PM
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
08-19-2015, 03:59 PM
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
08-19-2015, 11:20 PM
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
08-20-2015, 05:51 AM
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
08-21-2015, 01:26 AM
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
08-21-2015, 06:17 AM
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
08-21-2015, 07:06 AM
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
08-21-2015, 07:16 AM
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
08-21-2015, 07:26 AM
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
08-23-2015, 10:17 PM
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
08-25-2015, 01:25 PM
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
08-25-2015, 10:50 PM
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.