Hi,
In a recent post:
http://www.mrexcel.com/forum/showthread.php?t=633155
I asked a question about combining 3 subroutines and the answer given worked perfectly I'm happy to say.
However, when I attempted to add 'Supplier2' to the new combined sub, only NewSupplier updated correctly. The only update to Supplier2 was the heading 'Supplier2' inserted (correctly) into A1.
I know I've made a mistake or 2 in the code below, but cannot see where.
Also, I need to update comments for Supplier3 to Supplier10 in the same way so would appreciate advice on incorporating these into the code below.
many thanks
In a recent post:
http://www.mrexcel.com/forum/showthread.php?t=633155
I asked a question about combining 3 subroutines and the answer given worked perfectly I'm happy to say.
However, when I attempted to add 'Supplier2' to the new combined sub, only NewSupplier updated correctly. The only update to Supplier2 was the heading 'Supplier2' inserted (correctly) into A1.
I know I've made a mistake or 2 in the code below, but cannot see where.
Also, I need to update comments for Supplier3 to Supplier10 in the same way so would appreciate advice on incorporating these into the code below.
many thanks
Code:
<!--[if gte mso 9]><xml> <o:OfficeDocumentSettings> <o:AllowPNG/> </o:OfficeDocumentSettings> </xml><![endif]--><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:TrackMoves/> <w:TrackFormatting/> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:DoNotPromoteQF/> <w:LidThemeOther>EN-GB</w:LidThemeOther> <w:LidThemeAsian>X-NONE</w:LidThemeAsian> <w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:SplitPgBreakAndParaMark/> <w:EnableOpenTypeKerning/> <w:DontFlipMirrorIndents/> <w:OverrideTableStyleHps/> </w:Compatibility> <m:mathPr> <m:mathFont m:val="Cambria Math"/> <m:brkBin m:val="before"/> <m:brkBinSub m:val="--"/> <m:smallFrac m:val="off"/> <m:dispDef/> <m:lMargin m:val="0"/> <m:rMargin m:val="0"/> <m:defJc m:val="centerGroup"/> <m:wrapIndent m:val="1440"/> <m:intLim m:val="subSup"/> <m:naryLim m:val="undOvr"/> </m:mathPr></w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" DefUnhideWhenUsed="true" DefSemiHidden="true" DefQFormat="false" DefPriority="99" LatentStyleCount="267"> <w:LsdException Locked="false" Priority="0" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Normal"/> <w:LsdException Locked="false" Priority="9" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="heading 1"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 2"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 3"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 4"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 5"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 6"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 7"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 8"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 9"/> <w:LsdException Locked="false" Priority="39" Name="toc 1"/> <w:LsdException Locked="false" Priority="39" Name="toc 2"/> <w:LsdException Locked="false" Priority="39" Name="toc 3"/> <w:LsdException Locked="false" Priority="39" Name="toc 4"/> <w:LsdException Locked="false" Priority="39" Name="toc 5"/> <w:LsdException Locked="false" Priority="39" Name="toc 6"/> <w:LsdException Locked="false" Priority="39" Name="toc 7"/> <w:LsdException Locked="false" Priority="39" Name="toc 8"/> <w:LsdException Locked="false" Priority="39" Name="toc 9"/> <w:LsdException Locked="false" Priority="35" QFormat="true" Name="caption"/> <w:LsdException Locked="false" Priority="10" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Title"/> <w:LsdException Locked="false" Priority="1" Name="Default Paragraph Font"/> <w:LsdException Locked="false" Priority="11" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Subtitle"/> <w:LsdException Locked="false" Priority="22" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Strong"/> <w:LsdException Locked="false" Priority="20" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Emphasis"/> <w:LsdException Locked="false" Priority="59" SemiHidden="false" UnhideWhenUsed="false" Name="Table Grid"/> <w:LsdException Locked="false" UnhideWhenUsed="false" Name="Placeholder Text"/> <w:LsdException Locked="false" Priority="1" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="No Spacing"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 1"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 1"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 1"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 1"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 1"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 1"/> <w:LsdException Locked="false" UnhideWhenUsed="false" Name="Revision"/> <w:LsdException Locked="false" Priority="34" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="List Paragraph"/> <w:LsdException Locked="false" Priority="29" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Quote"/> <w:LsdException Locked="false" Priority="30" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Intense Quote"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 1"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 1"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 1"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 1"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 1"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 1"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 1"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 1"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 2"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 2"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 2"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 2"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 2"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 2"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 2"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 2"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 2"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 2"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 2"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 2"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 2"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 2"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 3"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 3"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 3"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 3"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 3"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 3"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 3"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 3"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 3"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 3"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 3"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 3"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 3"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 3"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 4"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 4"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 4"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 4"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 4"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 4"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 4"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 4"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 4"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 4"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 4"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 4"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 4"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 4"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 5"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 5"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 5"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 5"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 5"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 5"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 5"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 5"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 5"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 5"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 5"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 5"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 5"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 5"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 6"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 6"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 6"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 6"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 6"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 6"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 6"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 6"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 6"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 6"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 6"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 6"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 6"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 6"/> <w:LsdException Locked="false" Priority="19" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Subtle Emphasis"/> <w:LsdException Locked="false" Priority="21" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Intense Emphasis"/> <w:LsdException Locked="false" Priority="31" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Subtle Reference"/> <w:LsdException Locked="false" Priority="32" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Intense Reference"/> <w:LsdException Locked="false" Priority="33" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Book Title"/> <w:LsdException Locked="false" Priority="37" Name="Bibliography"/> <w:LsdException Locked="false" Priority="39" QFormat="true" Name="TOC Heading"/> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin-top:0cm; mso-para-margin-right:0cm; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0cm; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; mso-fareast-language:EN-US;} </style> <![endif]--> Sub NEWdo_allS1()
Dim lr As Long, r As Long
'update comments to Supplier 1
With Application
.ScreenUpdating = False
.CutCopyMode = False
.DisplayAlerts = False
.Calculation = xlCalculationManual
End With
With Worksheets("NewSupplier")
.Range("a2:c69").Value = Sheets("CompliancetoTC21CN").Range("b17:d84").Value
.Range("a70:a163").Value = Sheets("CompliancetoTCGeneric").Range("a16:a109").Value
.Range("b70:c163").Value = Sheets("CompliancetoTCGeneric").Range("c16:d109").Value
.Range("a164:a190").Value = Sheets("CompliancetoTCAppendices").Range("a16:a42").Value
.Range("b164:c190").Value = Sheets("CompliancetoTCAppendices").Range("c16:d42").Value
.Range("a191:a196").Value = Sheets("WLCEquipmentCosts").Range("C1:C1").Value
.Range("b191:c196").Value = Sheets("WLCEquipmentCosts").Range("c22:d27").Value
.Range("a197:a202").Value = Sheets("WLCSupportCosts").Range("C1:C1").Value
.Range("b197:c202").Value = Sheets("WLCSupportCosts").Range("c22:d27").Value
.Range("a203:a208").Value = Sheets("CommercialModelTechMileRemedy").Range("C1:C1").Value
.Range("b203:c208").Value = Sheets("CommercialModelTechMileRemedy").Range("c22:d27").Value
.Range("a209:a214").Value = Sheets("FreeTestEquipmentTraining").Range("C1:C1").Value
.Range("b209:c214").Value = Sheets("FreeTestEquipmentTraining").Range("c22:d27").Value
.Range("a215:a220").Value = Sheets("RFT").Range("C1:C1").Value
.Range("b215:c220").Value = Sheets("RFT").Range("c22:d27").Value
.Range("a221:a226").Value = Sheets("CommercialModelPaymentTerm").Range("C1:C1").Value
.Range("b221:c226").Value = Sheets("CommercialModelPaymentTerm").Range("c22:d27").Value
.Range("a227:a232").Value = Sheets("WarrantyandSupport").Range("C1:C1").Value
.Range("b227:c232").Value = Sheets("WarrantyandSupport").Range("c22:d27").Value
.Range("a233:a238").Value = Sheets("CompliancetoApp13").Range("C1:C1").Value
.Range("b233:c238").Value = Sheets("CompliancetoApp13").Range("c22:d27").Value
.Range("a239:a244").Value = Sheets("CompliancetoApp18").Range("C1:C1").Value
.Range("b239:c244").Value = Sheets("CompliancetoApp18").Range("c22:d27").Value
.Range("a245:a250").Value = Sheets("FinancialStability").Range("C1:C1").Value
.Range("b245:c250").Value = Sheets("FinancialStability").Range("c22:d27").Value
.Range("a251:a256").Value = Sheets("Supplier Watch").Range("C1:C1").Value
.Range("b251:c256").Value = Sheets("Supplier Watch").Range("c22:d27").Value
.Range("a257:a262").Value = Sheets("BCMApp14").Range("C1:C1").Value
.Range("b257:c262").Value = Sheets("BCMApp14").Range("c22:d27").Value
.Range("a263:a268").Value = Sheets("EnvironmentalCSRS13").Range("C1:C1").Value
.Range("b263:c268").Value = Sheets("EnvironmentalCSRS13").Range("c22:d27").Value
.Range("a269:a274").Value = Sheets("EnvironmentalCSRS18").Range("C1:C1").Value
.Range("b269:c274").Value = Sheets("EnvironmentalCSRS18").Range("c22:d27").Value
.Range("a275:a280").Value = Sheets("EnvironmentalCSRS19").Range("C1:C1").Value
.Range("b275:c280").Value = Sheets("EnvironmentalCSRS19").Range("c22:d27").Value
.Range("a281:a286").Value = Sheets("ClimateChangeQuestionnaire").Range("C1:C1").Value
.Range("b281:c286").Value = Sheets("ClimateChangeQuestionnaire").Range("c22:d27").Value
.Range("a287:a292").Value = Sheets("Architecture").Range("C1:C1").Value
.Range("b287:c292").Value = Sheets("Architecture").Range("c22:d27").Value
.Range("a293:a298").Value = Sheets("Roadmap").Range("C1:C1").Value
.Range("b293:c298").Value = Sheets("Roadmap").Range("c22:d27").Value
.Range("a299:a304").Value = Sheets("SolutionTimescales").Range("C1:C1").Value
.Range("b299:c304").Value = Sheets("SolutionTimescales").Range("c22:d27").Value
.Range("a305:a310").Value = Sheets("Network").Range("C1:C1").Value
.Range("b305:c310").Value = Sheets("Network").Range("c22:d27").Value
.Range("a311:a316").Value = Sheets("Support").Range("C1:C1").Value
.Range("b311:c316").Value = Sheets("Support").Range("c22:d27").Value
.Range("a317:a322").Value = Sheets("Reliability").Range("C1:C1").Value
.Range("b317:c322").Value = Sheets("Reliability").Range("c22:d27").Value
.Range("a323:a328").Value = Sheets("ElementManagement").Range("C1:C1").Value
.Range("b323:c328").Value = Sheets("ElementManagement").Range("c22:d27").Value
.Range("a329:a334").Value = Sheets("Operations").Range("C1:C1").Value
.Range("b329:c334").Value = Sheets("Operations").Range("c22:d27").Value
.Range("a335:a340").Value = Sheets("TestandIntegration").Range("C1:C1").Value
.Range("b335:c340").Value = Sheets("TestandIntegration").Range("c22:d27").Value
.Range("a335:a340").Value = Sheets("Security").Range("C1:C1").Value
.Range("b335:c340").Value = Sheets("Security").Range("c22:d27").Value
.Range("a347:a352").Value = Sheets("Criteria1").Range("C1:C1").Value
.Range("b347:c352").Value = Sheets("Criteria1").Range("c22:d27").Value
.Range("a353:a358").Value = Sheets("Criteria2").Range("C1:C1").Value
.Range("b353:c358").Value = Sheets("Criteria2").Range("c22:d27").Value
.Range("a359:a366").Value = Sheets("Criteria3").Range("C1:C1").Value
.Range("b359:c366").Value = Sheets("Criteria3").Range("c22:d27").Value
.Range("a365:a370").Value = Sheets("Criteria4").Range("C1:C1").Value
.Range("b365:c370").Value = Sheets("Criteria4").Range("c22:d27").Value
.Range("a371:a376").Value = Sheets("Criteria5").Range("C1:C1").Value
.Range("b371:c376").Value = Sheets("Criteria5").Range("c22:d27").Value
'delete blank rows in range Columns A to C
.Columns("B").SpecialCells(xlCellTypeBlanks).Offset(, -1).ClearContents
.Columns("C").SpecialCells(xlCellTypeBlanks).Offset(, -2).ClearContents
lr = .Cells(.Rows.Count, "A").End(xlUp).Row
For r = lr To 1 Step -1
If .Range("C" & r).Value = "" Then .Range("B" & r).Value = ""
If .Range("A" & r).Value = "" Then .Rows(r).Delete
Next r
For r = 370 To 1 Step -1
If .Range("A" & r).Value = "" Then .Rows(r).Delete
Next r
Worksheets("Summary Tab").Range("E3").FormulaR1C1 = _
"=MID(CELL(""filename"",NewSupplier!R[-2]C[-4]),FIND(""]"",CELL(""filename"",NewSupplier!R[-2]C[-4]),1)+1,255)"
.Range("A1:C1").EntireRow.Insert
With .Range("A1")
.FormulaR1C1 = "=('Summary Tab'!R3C5)"
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.Font.Bold = True
End With
End With
'update Supplier2 comments
With Worksheets("Supplier2")
.Range("a2:a69").Value = Sheets("CompliancetoTC21CN").Range("b17:b84").Value
.Range("b2:c69").Value = Sheets("CompliancetoTC21CN").Range("f17:g84").Value
.Range("a70:a163").Value = Sheets("CompliancetoTCGeneric").Range("a16:a109").Value
.Range("b70:b163").Value = Sheets("CompliancetoTCGeneric").Range("f16:g109").Value
.Range("a164:a190").Value = Sheets("CompliancetoTCAppendices").Range("a16:a42").Value
.Range("b164:c190").Value = Sheets("CompliancetoTCAppendices").Range("f16:g42").Value
.Range("a191:a196").Value = Sheets("WLCEquipmentCosts").Range("C1:C1").Value
.Range("f191:c196").Value = Sheets("WLCEquipmentCosts").Range("f22:g27").Value
.Range("a197:a202").Value = Sheets("WLCSupportCosts").Range("C1:C1").Value
.Range("f197:c202").Value = Sheets("WLCSupportCosts").Range("f22:g27").Value
.Range("a203:a208").Value = Sheets("CommercialModelTechMileRemedy").Range("C1:C1").Value
.Range("b203:g208").Value = Sheets("CommercialModelTechMileRemedy").Range("f22:g27").Value
.Range("a209:a214").Value = Sheets("FreeTestEquipmentTraining").Range("C1:C1").Value
.Range("b209:c214").Value = Sheets("FreeTestEquipmentTraining").Range("f22:g27").Value
.Range("a215:a220").Value = Sheets("RFT").Range("C1:C1").Value
.Range("b215:c220").Value = Sheets("RFT").Range("f22:g27").Value
.Range("a221:a226").Value = Sheets("CommercialModelPaymentTerm").Range("C1:C1").Value
.Range("b221:c226").Value = Sheets("CommercialModelPaymentTerm").Range("f22:g27").Value
.Range("a227:a232").Value = Sheets("WarrantyandSupport").Range("C1:C1").Value
.Range("b227:c232").Value = Sheets("WarrantyandSupport").Range("f22:g27").Value
.Range("a233:a238").Value = Sheets("CompliancetoApp13").Range("C1:C1").Value
.Range("b233:c238").Value = Sheets("CompliancetoApp13").Range("f22:g27").Value
.Range("a239:a244").Value = Sheets("CompliancetoApp18").Range("C1:C1").Value
.Range("b239:c244").Value = Sheets("CompliancetoApp18").Range("f22:g27").Value
.Range("a245:a250").Value = Sheets("FinancialStability").Range("C1:C1").Value
.Range("b245:c250").Value = Sheets("FinancialStability").Range("f22:g27").Value
.Range("a251:a256").Value = Sheets("Supplier Watch").Range("C1:C1").Value
.Range("b251:c256").Value = Sheets("Supplier Watch").Range("f22:g27").Value
.Range("a257:a262").Value = Sheets("BCMApp14").Range("C1:C1").Value
.Range("b257:c262").Value = Sheets("BCMApp14").Range("f22:g27").Value
.Range("a263:a268").Value = Sheets("EnvironmentalCSRS13").Range("C1:C1").Value
.Range("b263:c268").Value = Sheets("EnvironmentalCSRS13").Range("f22:g27").Value
.Range("a269:a274").Value = Sheets("EnvironmentalCSRS18").Range("C1:C1").Value
.Range("b269:c274").Value = Sheets("EnvironmentalCSRS18").Range("f22:g27").Value
.Range("a275:a280").Value = Sheets("EnvironmentalCSRS19").Range("C1:C1").Value
.Range("b275:c280").Value = Sheets("EnvironmentalCSRS19").Range("f22:g27").Value
.Range("a281:a286").Value = Sheets("ClimateChangeQuestionnaire").Range("C1:C1").Value
.Range("b281:c286").Value = Sheets("ClimateChangeQuestionnaire").Range("f22:g27").Value
.Range("a287:a292").Value = Sheets("Architecture").Range("C1:C1").Value
.Range("b287:c292").Value = Sheets("Architecture").Range("f22:g27").Value
.Range("a293:a298").Value = Sheets("Roadmap").Range("C1:C1").Value
.Range("b293:c298").Value = Sheets("Roadmap").Range("f22:g27").Value
.Range("a299:a304").Value = Sheets("SolutionTimescales").Range("C1:C1").Value
.Range("b299:c304").Value = Sheets("SolutionTimescales").Range("f22:g27").Value
.Range("a305:a310").Value = Sheets("Network").Range("C1:C1").Value
.Range("b305:c310").Value = Sheets("Network").Range("f22:g27").Value
.Range("a311:a316").Value = Sheets("Support").Range("C1:C1").Value
.Range("b311:c316").Value = Sheets("Support").Range("f22:g27").Value
.Range("a317:a322").Value = Sheets("Reliability").Range("C1:C1").Value
.Range("b317:c322").Value = Sheets("Reliability").Range("f22:g27").Value
.Range("a323:a328").Value = Sheets("ElementManagement").Range("C1:C1").Value
.Range("b323:c328").Value = Sheets("ElementManagement").Range("f22:g27").Value
.Range("a329:a334").Value = Sheets("Operations").Range("C1:C1").Value
.Range("b329:c334").Value = Sheets("Operations").Range("f22:g27").Value
.Range("a335:a340").Value = Sheets("TestandIntegration").Range("C1:C1").Value
.Range("b335:c340").Value = Sheets("TestandIntegration").Range("f22:g27").Value
.Range("a335:a340").Value = Sheets("Security").Range("C1:C1").Value
.Range("b335:c340").Value = Sheets("Security").Range("f22:g27").Value
.Range("a347:a352").Value = Sheets("Criteria1").Range("C1:C1").Value
.Range("b347:c352").Value = Sheets("Criteria1").Range("f22:g27").Value
.Range("a353:a358").Value = Sheets("Criteria2").Range("C1:C1").Value
.Range("b353:c358").Value = Sheets("Criteria2").Range("f22:g27").Value
.Range("a359:a366").Value = Sheets("Criteria3").Range("C1:C1").Value
.Range("b359:c366").Value = Sheets("Criteria3").Range("f22:g27").Value
.Range("a365:a370").Value = Sheets("Criteria4").Range("C1:C1").Value
.Range("b365:c370").Value = Sheets("Criteria4").Range("f22:g27").Value
.Range("a371:a376").Value = Sheets("Criteria5").Range("C1:C1").Value
.Range("b371:c376").Value = Sheets("Criteria5").Range("f22:g27").Value
'delete blank rows in range Columns A to C
.Columns("B").SpecialCells(xlCellTypeBlanks).Offset(, -1).ClearContents
.Columns("C").SpecialCells(xlCellTypeBlanks).Offset(, -2).ClearContents
lr = .Cells(.Rows.Count, "A").End(xlUp).Row
For r = lr To 1 Step -1
If .Range("C" & r).Value = "" Then .Range("B" & r).Value = ""
If .Range("A" & r).Value = "" Then .Rows(r).Delete
Next r
For r = 370 To 1 Step -1
If .Range("A" & r).Value = "" Then .Rows(r).Delete
Next r
Worksheets("Summary Tab").Range("h3").FormulaR1C1 = _
"=MID(CELL(""filename"",Supplier2!R[-2]C[-4]),FIND(""]"",CELL(""filename"",Supplier2!R[-2]C[-4]),1)+1,255)"
.Range("A1:C1").EntireRow.Insert
With .Range("A1")
.FormulaR1C1 = "=('Summary Tab'!R3C8)"
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.Font.Bold = True
End With
End With
End Sub
