Solved: Help to shortened code as its too complex for maintenance.

[VISHAL120]

[VISHAL120]

[VISHAL120]

i don't know what is happening am able to send my msg and the code. am trying again:

Hi ,

Am actually using this code below to transfer data from one workbook to another closed workbook which is formatted and arranged differently from the original workbook. And that is why I am addressing the cell directly to assign the data .

The problem is if I add one row for a new data to add I have to go to the code and change all the cell value which is very very time consuming and also difficult to do maintenance.

I have not found any other way of doing it right now and I needed help of how firstly the code can be shortened as I have had to break the code in different private sub as every time am having procedure too long.

Am actually addressing the cell from D25 to Q25 and from d329 to Q329.

Here is the code below together with an example of the new format sheet.

[VBA]
Set Data_Range_Search = WKSMANAGEMENTSHEET.Range("b4", Range("b65536").End(xlUp)).Find(what:=Me.txtPosNo.Value) ' look for order


'===>> CALLING ROUTINE TO OPEN THE TEMPLATE AND INITIALISING THE REPORT WORKSHEET.
Call OPEN_QTY_MANAGEMENT_TEMPLATE_REPORT

'INITIALISE THE WORKBOOK AND WROKSHEET,

Set WKBTEMPLATE = Application.Workbooks(QTY_MANAGEMENT_TEMPLATE)
Set WKSTEMPLATE = WKBTEMPLATE.Worksheets("REPORTS")

'==> WORKING WITH THE QUANTITY MANAGEMENT DATA ENTRY HERE



'Set Data_Range_Search = WKSMANAGEMENTSHEET.Range("b4", Range("b65536").End(xlUp)).Find(what:=Me.txtPosNo.Value) ' look for order

If Not Data_Range_Search Is Nothing Then


'Record is found at RQUERY.Row
LastRow = Data_Range_Search.Row


'UPDATE DATA ON FORM>>>>>>>>>>>>>>

With WKSTEMPLATE
'==>> ASSIGN 1ST COLORWAY DETAILS
'ASSIGN POS NUMBER
.Range("D2").Value = WKSMANAGEMENTSHEET.Cells(LastRow, 2)
'ASSIGN CUST
.Range("G2").Value = WKSMANAGEMENTSHEET.Cells(LastRow, 4)
'ASSIGN CUST TOL.
.Range("G3").Value = Format(WKSMANAGEMENTSHEET.Cells(LastRow, 5), "0.0%")
'ASSIGN COLORWAY
.Range("D23").Value = WKSMANAGEMENTSHEET.Cells(LastRow, 3)
'ASSIGN SIZES
.Range("D24").Value = WKSMANAGEMENTSHEET.Cells(LastRow, 9)
.Range("E24").Value = WKSMANAGEMENTSHEET.Cells(LastRow, 10)
.Range("F24").Value = WKSMANAGEMENTSHEET.Cells(LastRow, 11)
.Range("G24").Value = WKSMANAGEMENTSHEET.Cells(LastRow, 12)
.Range("H24").Value = WKSMANAGEMENTSHEET.Cells(LastRow, 13)
.Range("I24").Value = WKSMANAGEMENTSHEET.Cells(LastRow, 14)
.Range("J24").Value = WKSMANAGEMENTSHEET.Cells(LastRow, 15)
.Range("K24").Value = WKSMANAGEMENTSHEET.Cells(LastRow, 16)
.Range("L24").Value = WKSMANAGEMENTSHEET.Cells(LastRow, 17)
.Range("M24").Value = WKSMANAGEMENTSHEET.Cells(LastRow, 18)
.Range("N24").Value = WKSMANAGEMENTSHEET.Cells(LastRow, 19)
.Range("O24").Value = WKSMANAGEMENTSHEET.Cells(LastRow, 20)
.Range("P24").Value = WKSMANAGEMENTSHEET.Cells(LastRow, 21)
.Range("Q24").Value = WKSMANAGEMENTSHEET.Cells(LastRow, 22)

'ASSIGN POS QTY
.Range("D25").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 1, 9)
.Range("E25").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 1, 10)
.Range("F25").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 1, 11)
.Range("G25").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 1, 12)
.Range("H25").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 1, 13)
.Range("I25").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 1, 14)
.Range("J25").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 1, 15)
.Range("K25").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 1, 16)
.Range("L25").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 1, 17)
.Range("M25").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 1, 18)
.Range("N25").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 1, 19)
.Range("O25").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 1, 20)
.Range("P25").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 1, 21)
.Range("Q25").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 1, 22)

'ASSIGN ACCEPTED QTY BY CUST.
.Range("D26").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 2, 9)
.Range("E26").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 2, 10)
.Range("F26").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 2, 11)
.Range("G26").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 2, 12)
.Range("H26").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 2, 13)
.Range("I26").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 2, 14)
.Range("J26").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 2, 15)
.Range("K26").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 2, 16)
.Range("L26").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 2, 17)
.Range("M26").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 2, 18)
.Range("N26").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 2, 19)
.Range("O26").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 2, 20)
.Range("P26").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 2, 21)
.Range("Q26").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 2, 22)
'ASSIGN KNIT QTY
.Range("D27").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 3, 9)
.Range("E27").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 3, 10)
.Range("F27").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 3, 11)
.Range("G27").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 3, 12)
.Range("H27").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 3, 13)
.Range("I27").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 3, 14)
.Range("J27").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 3, 15)
.Range("K27").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 3, 16)
.Range("L27").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 3, 17)
.Range("M27").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 3, 18)
.Range("N27").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 3, 19)
.Range("O27").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 3, 20)
.Range("P27").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 3, 21)
.Range("Q27").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 3, 22)
'ADJUSTED KNIT TO READ FROM ANOTHER SHEET.
'ASSIGN % KNIT V/S POS.
.Range("D29").Value = Format(WKSMANAGEMENTSHEET.Cells(LastRow + 4, 9), "0.0%")
.Range("E29").Value = Format(WKSMANAGEMENTSHEET.Cells(LastRow + 4, 10), "0.0%")
.Range("F29").Value = Format(WKSMANAGEMENTSHEET.Cells(LastRow + 4, 11), "0.0%")
.Range("G29").Value = Format(WKSMANAGEMENTSHEET.Cells(LastRow + 4, 12), "0.0%")
.Range("H29").Value = Format(WKSMANAGEMENTSHEET.Cells(LastRow + 4, 13), "0.0%")
.Range("I29").Value = Format(WKSMANAGEMENTSHEET.Cells(LastRow + 4, 14), "0.0%")
.Range("J29").Value = Format(WKSMANAGEMENTSHEET.Cells(LastRow + 4, 15), "0.0%")
.Range("K29").Value = Format(WKSMANAGEMENTSHEET.Cells(LastRow + 4, 16), "0.0%")
.Range("L29").Value = Format(WKSMANAGEMENTSHEET.Cells(LastRow + 4, 17), "0.0%")
.Range("M29").Value = Format(WKSMANAGEMENTSHEET.Cells(LastRow + 4, 18), "0.0%")
.Range("N29").Value = Format(WKSMANAGEMENTSHEET.Cells(LastRow + 4, 19), "0.0%")
.Range("O29").Value = Format(WKSMANAGEMENTSHEET.Cells(LastRow + 4, 20), "0.0%")
.Range("P29").Value = Format(WKSMANAGEMENTSHEET.Cells(LastRow + 4, 21), "0.0%")
.Range("Q29").Value = Format(WKSMANAGEMENTSHEET.Cells(LastRow + 4, 22), "0.0%")
'ASSIGN ACC QTY.
.Range("D30").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 5, 9)
.Range("E30").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 5, 10)
.Range("F30").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 5, 11)
.Range("G30").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 5, 12)
.Range("H30").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 5, 13)
.Range("I30").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 5, 14)
.Range("J30").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 5, 15)
.Range("K30").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 5, 16)
.Range("L30").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 5, 17)
.Range("M30").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 5, 18)
.Range("N30").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 5, 19)
.Range("O30").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 5, 20)
.Range("P30").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 5, 21)
.Range("Q30").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 5, 22)
'ASSIGN QTY CAN SHIP BASED ON MIN ACC, KNIT QTY
.Range("D31").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 6, 9)
.Range("E31").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 6, 10)
.Range("F31").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 6, 11)
.Range("G31").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 6, 12)
.Range("H31").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 6, 13)
.Range("I31").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 6, 14)
.Range("J31").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 6, 15)
.Range("K31").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 6, 16)
.Range("L31").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 6, 17)
.Range("M31").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 6, 18)
.Range("N31").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 6, 19)
.Range("O31").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 6, 20)
.Range("P31").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 6, 21)
.Range("Q31").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 6, 22)
'ASSIGN QTY REC. WASH.
.Range("D32").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 7, 9)
.Range("E32").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 7, 10)
.Range("F32").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 7, 11)
.Range("G32").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 7, 12)
.Range("H32").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 7, 13)
.Range("I32").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 7, 14)
.Range("J32").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 7, 15)
.Range("K32").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 7, 16)
.Range("L32").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 7, 17)
.Range("M32").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 7, 18)
.Range("N32").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 7, 19)
.Range("O32").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 7, 20)
.Range("P32").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 7, 21)
.Range("Q32").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 7, 22)
'ASSIGN QTY STILL TO REC. WASH
.Range("D33").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 8, 9)
.Range("E33").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 8, 10)
.Range("F33").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 8, 11)
.Range("G33").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 8, 12)
.Range("H33").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 8, 13)
.Range("I33").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 8, 14)
.Range("J33").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 8, 15)
.Range("K33").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 8, 16)
.Range("L33").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 8, 17)
.Range("M33").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 8, 18)
.Range("N33").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 8, 19)
.Range("O33").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 8, 20)
.Range("P33").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 8, 21)
.Range("Q33").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 8, 22)
'ASSIGN QTY ISSUE FINAL
.Range("D34").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 9, 9)
.Range("E34").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 9, 10)
.Range("F34").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 9, 11)
.Range("G34").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 9, 12)
.Range("H34").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 9, 13)
.Range("I34").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 9, 14)
.Range("J34").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 9, 15)
.Range("K34").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 9, 16)
.Range("L34").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 9, 17)
.Range("M34").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 9, 18)
.Range("N34").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 9, 19)
.Range("O34").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 9, 20)
.Range("P34").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 9, 21)
.Range("Q34").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 9, 22)
'ASSIGN QTY STILL TO ISSUE FINAL
.Range("D35").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 10, 9)
.Range("E35").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 10, 10)
.Range("F35").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 10, 11)
.Range("G35").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 10, 12)
.Range("H35").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 10, 13)
.Range("I35").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 10, 14)
.Range("J35").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 10, 15)
.Range("K35").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 10, 16)
.Range("L35").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 10, 17)
.Range("M35").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 10, 18)
.Range("N35").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 10, 19)
.Range("O35").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 10, 20)
.Range("P35").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 10, 21)
.Range("Q35").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 10, 22)
'ASSIGN QTY REC. MD
.Range("D36").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 11, 9)
.Range("E36").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 11, 10)
.Range("F36").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 11, 11)
.Range("G36").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 11, 12)
.Range("H36").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 11, 13)
.Range("I36").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 11, 14)
.Range("J36").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 11, 15)
.Range("K36").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 11, 16)
.Range("L36").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 11, 17)
.Range("M36").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 11, 18)
.Range("N36").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 11, 19)
.Range("O36").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 11, 20)
.Range("P36").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 11, 21)
.Range("Q36").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 11, 22)
'ASSIGN QTY STILL TO REC. MD
.Range("D37").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 12, 9)
.Range("E37").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 12, 10)
.Range("F37").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 12, 11)
.Range("G37").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 12, 12)
.Range("H37").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 12, 13)
.Range("I37").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 12, 14)
.Range("J37").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 12, 15)
.Range("K37").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 12, 16)
.Range("L37").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 12, 17)
.Range("M37").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 12, 18)
.Range("N37").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 12, 19)
.Range("O37").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 12, 20)
.Range("P37").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 12, 21)
.Range("Q37").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 12, 22)
'LEFTOVER ADJUSTEMENT TO BE READ FORM ANOTHER WORKSHEETS
'ASSIGN PACKING LIST
.Range("D39").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 14, 9)
.Range("E39").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 14, 10)
.Range("F39").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 14, 11)
.Range("G39").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 14, 12)
.Range("H39").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 14, 13)
.Range("I39").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 14, 14)
.Range("J39").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 14, 15)
.Range("K39").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 14, 16)
.Range("L39").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 14, 17)
.Range("M39").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 14, 18)
.Range("N39").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 14, 19)
.Range("O39").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 14, 20)
.Range("P39").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 14, 21)
.Range("Q39").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 14, 22)
'ASSIGN % SHORT SHIP V/S KNIT.
.Range("D40").Value = Format(WKSMANAGEMENTSHEET.Cells(LastRow + 15, 9), "0.0%")
.Range("E40").Value = Format(WKSMANAGEMENTSHEET.Cells(LastRow + 15, 10), "0.0%")
.Range("F40").Value = Format(WKSMANAGEMENTSHEET.Cells(LastRow + 15, 11), "0.0%")
.Range("G40").Value = Format(WKSMANAGEMENTSHEET.Cells(LastRow + 15, 12), "0.0%")
.Range("H40").Value = Format(WKSMANAGEMENTSHEET.Cells(LastRow + 15, 13), "0.0%")
.Range("I40").Value = Format(WKSMANAGEMENTSHEET.Cells(LastRow + 15, 14), "0.0%")
.Range("J40").Value = Format(WKSMANAGEMENTSHEET.Cells(LastRow + 15, 15), "0.0%")
.Range("K40").Value = Format(WKSMANAGEMENTSHEET.Cells(LastRow + 15, 16), "0.0%")
.Range("L40").Value = Format(WKSMANAGEMENTSHEET.Cells(LastRow + 15, 17), "0.0%")
.Range("M40").Value = Format(WKSMANAGEMENTSHEET.Cells(LastRow + 15, 18), "0.0%")
.Range("N40").Value = Format(WKSMANAGEMENTSHEET.Cells(LastRow + 15, 19), "0.0%")
.Range("O40").Value = Format(WKSMANAGEMENTSHEET.Cells(LastRow + 15, 20), "0.0%")
.Range("P40").Value = Format(WKSMANAGEMENTSHEET.Cells(LastRow + 15, 21), "0.0%")
.Range("Q40").Value = Format(WKSMANAGEMENTSHEET.Cells(LastRow + 15, 22), "0.0%")
'ASSIGN STILL TO REC. PACK LIST
.Range("D41").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 16, 9)
.Range("E41").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 16, 10)
.Range("F41").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 16, 11)
.Range("G41").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 16, 12)
.Range("H41").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 16, 13)
.Range("I41").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 16, 14)
.Range("J41").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 16, 15)
.Range("K41").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 16, 16)
.Range("L41").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 16, 17)
.Range("M41").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 16, 18)
.Range("N41").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 16, 19)
.Range("O41").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 16, 20)
.Range("P41").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 16, 21)
.Range("Q41").Value = WKSMANAGEMENTSHEET.Cells(LastRow + 16, 22)
'>> CALL ROUTINE FOR ADJUSTED KNIT HERE
'Call DATA_FROM_ADJUSTED_KNIT

'ASSIGN 2 COLORWAY

'///////////////////////////////////////////////////////////////////////
'ASSIGN 2 COLORWAY

LastRow2 = Data_Range_Search.Row + 17
If WKSMANAGEMENTSHEET.Cells(LastRow2, 2) <> Data_Range_Search Then
GoTo NEXT_SCENARIO

Else[/VBA]

I will be very grateful if someone can just explain me on how I do can do it in another way which will be easier to change and maintain later.

Many thanks for the help in advance.

[Bob Phillips]

[VBA]Set Data_Range_Search = WKSMANAGEMENTSHEET.Range("b4", Range("b65536").End(xlUp)).Find(what:=Me.txtPosNo.Value) ' look for order


'===>> CALLING ROUTINE TO OPEN THE TEMPLATE AND INITIALISING THE REPORT WORKSHEET.
Call OPEN_QTY_MANAGEMENT_TEMPLATE_REPORT

'INITIALISE THE WORKBOOK AND WROKSHEET,

Set WKBTEMPLATE = Application.Workbooks(QTY_MANAGEMENT_TEMPLATE)
Set WKSTEMPLATE = WKBTEMPLATE.Worksheets("REPORTS")

'==> WORKING WITH THE QUANTITY MANAGEMENT DATA ENTRY HERE



'Set Data_Range_Search = WKSMANAGEMENTSHEET.Range("b4", Range("b65536").End(xlUp)).Find(what:=Me.txtPosNo.Value) ' look for order

If Not Data_Range_Search Is Nothing Then


'Record is found at RQUERY.Row
LastRow = Data_Range_Search.Row


'UPDATE DATA ON FORM>>>>>>>>>>>>>>

With WKSTEMPLATE
'==>> ASSIGN 1ST COLORWAY DETAILS
'ASSIGN POS NUMBER
.Range("D2").Value = WKSMANAGEMENTSHEET.Cells(LastRow, 2)
'ASSIGN CUST
.Range("G2").Value = WKSMANAGEMENTSHEET.Cells(LastRow, 4)
'ASSIGN CUST TOL.
.Range("G3").Value = Format(WKSMANAGEMENTSHEET.Cells(LastRow, 5), "0.0%")
'ASSIGN COLORWAY
.Range("D23").Value = WKSMANAGEMENTSHEET.Cells(LastRow, 3)
'ASSIGN SIZES
WKSMANAGEMENTSHEET.Cells(LastRow, 9).Resize(, 14).Copy
.Range("D24:Q24").PasteSpecial Paste:=xlPasteValues

'ASSIGN POS QTY
WKSMANAGEMENTSHEET.Cells(LastRow + 1, 9).Resize(, 14).Copy
.Range("D25:Q25").PasteSpecial Paste:=xlPasteValues

'ASSIGN ACCEPTED QTY BY CUST.
WKSMANAGEMENTSHEET.Cells(LastRow + 2, 9).Resize(, 14).Copy
.Range("D26:Q26").PasteSpecial Paste:=xlPasteValues

'ASSIGN KNIT QTY
WKSMANAGEMENTSHEET.Cells(LastRow + 3, 9).Resize(, 14).Copy
.Range("D27:Q27").PasteSpecial Paste:=xlPasteValues

'ADJUSTED KNIT TO READ FROM ANOTHER SHEET.
'ASSIGN % KNIT V/S POS.
WKSMANAGEMENTSHEET.Cells(LastRow + 4, 9).Resize(, 14).Copy
Range("D29:Q29").PasteSpecial Paste:=xlPasteValues
Range("D29:Q29").NumberFormat = "0.0%"
'ASSIGN ACC QTY.
WKSMANAGEMENTSHEET.Cells(LastRow + 5, 9).Resize(, 14).Copy
.Range("D30:Q30").PasteSpecial Paste:=xlPasteValues

'ASSIGN QTY CAN SHIP BASED ON MIN ACC, KNIT QTY
WKSMANAGEMENTSHEET.Cells(LastRow + 6, 9).Resize(, 14).Copy
.Range("D31:Q31").PasteSpecial Paste:=xlPasteValues
'ASSIGN QTY REC. WASH.
WKSMANAGEMENTSHEET.Cells(LastRow + 7, 9).Resize(, 14).Copy
.Range("D32:Q32").PasteSpecial Paste:=xlPasteValues

'ASSIGN QTY STILL TO REC. WASH
WKSMANAGEMENTSHEET.Cells(LastRow + 8, 9).Resize(, 14).Copy
.Range("D33:Q33").PasteSpecial Paste:=xlPasteValues

'ASSIGN QTY ISSUE FINAL
WKSMANAGEMENTSHEET.Cells(LastRow + 9, 9).Resize(, 14).Copy
.Range("D34:Q34").PasteSpecial Paste:=xlPasteValues

'ASSIGN QTY STILL TO ISSUE FINAL
WKSMANAGEMENTSHEET.Cells(LastRow + 10, 9).Resize(, 14).Copy
.Range("D35:Q35").PasteSpecial Paste:=xlPasteValues

'ASSIGN QTY REC. MD
WKSMANAGEMENTSHEET.Cells(LastRow + 11, 9).Resize(, 14).Copy
.Range("D36:Q36").PasteSpecial Paste:=xlPasteValues

'ASSIGN QTY STILL TO REC. MD
WKSMANAGEMENTSHEET.Cells(LastRow + 12, 9).Resize(, 14).Copy
.Range("D37:Q37").PasteSpecial Paste:=xlPasteValues

'LEFTOVER ADJUSTEMENT TO BE READ FORM ANOTHER WORKSHEETS
'ASSIGN PACKING LIST
WKSMANAGEMENTSHEET.Cells(LastRow + 14, 9).Resize(, 14).Copy
.Range("D39:Q39").PasteSpecial Paste:=xlPasteValues

'ASSIGN % SHORT SHIP V/S KNIT.
WKSMANAGEMENTSHEET.Cells(LastRow + 15, 9).Resize(, 14).Copy
Range("D40:Q40").PasteSpecial Paste:=xlPasteValues
Range("D40:Q40").NumberFormat = "0.0%"

'ASSIGN STILL TO REC. PACK LIST
WKSMANAGEMENTSHEET.Cells(LastRow + 16, 9).Resize(, 14).Copy
.Range("D41:Q41").PasteSpecial Paste:=xlPasteValues

'>> CALL ROUTINE FOR ADJUSTED KNIT HERE
'Call DATA_FROM_ADJUSTED_KNIT

'ASSIGN 2 COLORWAY

'///////////////////////////////////////////////////////////////////////
'ASSIGN 2 COLORWAY

LastRow2 = Data_Range_Search.Row + 17
If WKSMANAGEMENTSHEET.Cells(LastRow2, 2) <> Data_Range_Search Then
GoTo NEXT_SCENARIO

Else
[/VBA]

[VISHAL120]

Hi XLD,

Thanks you so much its work perfect. i will start changing the codes as you have show to me.

Once again this one also it needed help where i have address the cell one by one.

i tried using the resize method as you show here but has not been able to make it working.

this one add 3 different row values to display in in one cell.

here below is the code am using actually :

[VBA]With WKSTEMPLATE

'==>> ASSIGN 1ST COLORWAY DETAILS
'
'ASSIGN ADJUSTED KNIT QTY + SHORTKNIT + UNKNITTED QTY

LASTROW2 = lastrow1 + 3
If WKSMANAGEMENTSHEET.Cells(LASTROW11, 2) <> Data_Range_Search Then
GoTo NEXT_SCENARIO

Else

.Range("D42").Value = WKSMANAGEMENTSHEET.Cells(LASTROW2 + 6, 9) + WKSMANAGEMENTSHEET.Cells(LASTROW2 + 7, 9) _
+ WKSMANAGEMENTSHEET.Cells(LASTROW2 + 8, 9)

.Range("E42").Value = WKSMANAGEMENTSHEET.Cells(LASTROW2 + 6, 10) + WKSMANAGEMENTSHEET.Cells(LASTROW2 + 7, 10) _
+ WKSMANAGEMENTSHEET.Cells(LASTROW2 + 8, 10)
.Range("F42").Value = WKSMANAGEMENTSHEET.Cells(LASTROW2 + 6, 11) + WKSMANAGEMENTSHEET.Cells(LASTROW2 + 7, 11) _
+ WKSMANAGEMENTSHEET.Cells(LASTROW2 + 8, 11)
.Range("G42").Value = WKSMANAGEMENTSHEET.Cells(LASTROW2 + 6, 12) + WKSMANAGEMENTSHEET.Cells(LASTROW2 + 7, 12) _
+ WKSMANAGEMENTSHEET.Cells(LASTROW2 + 8, 12)
.Range("H42").Value = WKSMANAGEMENTSHEET.Cells(LASTROW2 + 6, 13) + WKSMANAGEMENTSHEET.Cells(LASTROW2 + 7, 13) _
+ WKSMANAGEMENTSHEET.Cells(LASTROW2 + 8, 13)
.Range("I42").Value = WKSMANAGEMENTSHEET.Cells(LASTROW2 + 6, 14) + WKSMANAGEMENTSHEET.Cells(LASTROW2 + 7, 14) _
+ WKSMANAGEMENTSHEET.Cells(LASTROW2 + 8, 14)
.Range("J42").Value = WKSMANAGEMENTSHEET.Cells(LASTROW2 + 6, 15) + WKSMANAGEMENTSHEET.Cells(LASTROW2 + 7, 15) _
+ WKSMANAGEMENTSHEET.Cells(LASTROW2 + 8, 15)
.Range("K42").Value = WKSMANAGEMENTSHEET.Cells(LASTROW2 + 6, 16) + WKSMANAGEMENTSHEET.Cells(LASTROW2 + 7, 16) _
+ WKSMANAGEMENTSHEET.Cells(LASTROW2 + 8, 16)
.Range("L42").Value = WKSMANAGEMENTSHEET.Cells(LASTROW2 + 6, 17) + WKSMANAGEMENTSHEET.Cells(LASTROW2 + 7, 17) _
+ WKSMANAGEMENTSHEET.Cells(LASTROW2 + 8, 17)
.Range("M42").Value = WKSMANAGEMENTSHEET.Cells(LASTROW2 + 6, 18) + WKSMANAGEMENTSHEET.Cells(LASTROW2 + 7, 18) _
+ WKSMANAGEMENTSHEET.Cells(LASTROW2 + 8, 18)
.Range("N42").Value = WKSMANAGEMENTSHEET.Cells(LASTROW2 + 6, 19) + WKSMANAGEMENTSHEET.Cells(LASTROW2 + 7, 19) _
+ WKSMANAGEMENTSHEET.Cells(LASTROW2 + 8, 19)
.Range("O42").Value = WKSMANAGEMENTSHEET.Cells(LASTROW2 + 6, 20) + WKSMANAGEMENTSHEET.Cells(LASTROW2 + 7, 20) _
+ WKSMANAGEMENTSHEET.Cells(LASTROW2 + 8, 20)
.Range("P42").Value = WKSMANAGEMENTSHEET.Cells(LASTROW2 + 6, 21) + WKSMANAGEMENTSHEET.Cells(LASTROW2 + 7, 21) _
+ WKSMANAGEMENTSHEET.Cells(LASTROW2 + 8, 21)
.Range("Q42").Value = WKSMANAGEMENTSHEET.Cells(LASTROW2 + 6, 22) + WKSMANAGEMENTSHEET.Cells(LASTROW2 + 7, 22) _
+ WKSMANAGEMENTSHEET.Cells(LASTROW2 + 8, 22)[/VBA]

thanks you again for your precious time and help sir.

[Bob Phillips]

[VBA]With WKSTEMPLATE

'==>> ASSIGN 1ST COLORWAY DETAILS
'
'ASSIGN ADJUSTED KNIT QTY + SHORTKNIT + UNKNITTED QTY

LASTROW2 = lastrow1 + 3
If WKSMANAGEMENTSHEET.Cells(LASTROW11, 2) <> Data_Range_Search Then
GoTo NEXT_SCENARIO

Else
For i = 1 To 14

.Cells(42, i + 3).Value = WKSMANAGEMENTSHEET.Cells(LASTROW2 + 6, i + 8) _
+ WKSMANAGEMENTSHEET.Cells(LASTROW2 + 7, i + 8) _
+ WKSMANAGEMENTSHEET.Cells(LASTROW2 + 8, i + 8)
Next i
[/VBA]

[VISHAL120]

Hi Xld,

thanks a lot sir.

will try and revert if i have any problem.

thanks again.

[VISHAL120]

Hi Xld,

thanks it works correctly.

many thanks again cheers.

[snb]

[VBA]For i = 1 To 14
.Cells(42, i + 3).Value = application.sum(WKSMANAGEMENTSHEET.Cells(LASTROW2 + 6, i + 8).resize(3))
Next [/VBA]

[VISHAL120]

HI snb,

thanks i will give this one too.

thanks agaiin for your precious time.