Code to translate all workbook based on translation table

gubertu

Board Regular
Joined
May 24, 2015
Messages
101
Hi all,

I have a Workbook with one sheet called "Translation" with a translation table. There are 1.300 rows in this table, with words translated from English to Spanish, starting in cell "A1"

EnglishSpanish
AssetsActivo
LiabilitiesPasivo

<tbody>
</tbody>

What I need is a code that goes to every sheet and translate every word from English to Spanish.

I already have a code that do what I want, the problem is that for every sheet, the code checks the 1.300 rows of my translation table and then go to the next sheet and checks the 1.300 rows of my translation table and go to the next sheet, etc.

As I have 40 sheets, the process is taking to long and I´m looking for a way to do this quicker.

Any of you could improve this code or just give me another one that do what I need?

The code is the following.

Code:
Const lang1 As Long = 1    Const lang2 As Long = 2    Dim arr1    As Variant    Dim arr2    As Variant    Dim i       As Long    Dim lr      As Long    Dim ws      As Worksheet    Dim WS1 As Worksheet    Set WS1 = Sheets("Guide")            With ThisWorkbook.Worksheets("Translation")        lr = .Cells(.Rows.Count, 1).End(xlUp).Row        arr1 = .Range(.Cells(2, lang2), .Cells(lr, lang2))        arr2 = .Range(.Cells(2, lang1), .Cells(lr, lang1))    End With       For Each ws In ThisWorkbook.Worksheets        With ws            Select Case (ws.Name)                Case "Translation"                Case Else                    For i = 1 To lr - 1                        .Cells.SpecialCells(xlCellTypeConstants).Replace What:=arr1(i, 1), Replacement:=arr2(i, 1), LookAt:=xlWhole, _                            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _                            ReplaceFormat:=False                    Next            End Select        End With    Next
I hope you could help me.

Thanks in advance!
 
Last edited:

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,385
Office Version
2013
Platform
Windows
Here’s a suggestion:

  • Copy sheet "Translation" to another workbook then go back to your workbook.
  • Open replace dialog box (Ctrl+H) and set “Within” to “Workbook” then close it. This is to set the replace within all sheet all at once.
  • Revise your code so it won’t loop through sheet by sheet.
  • Also add “Application.ScreenUpdating = False” in the beginning of the code, and “Application.ScreenUpdating = True” in the end.
  • Run the code.
  • Copy back the sheet "Translation".

So basically you do the replace within the workbook (not sheet by sheet), it will be quicker but since the replace list has 3000 pair of words I think it’s still gonna be long.
 

gubertu

Board Regular
Joined
May 24, 2015
Messages
101
Many thanks for your help. I have one question.

Why I need to copy the sheet Translation to another WB? I have tried to do everything you proposed and still the process is too slow.

Thanks!
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,385
Office Version
2013
Platform
Windows
Why I need to copy the sheet Translation to another WB?
Because if you do the replace in all sheet then the content of the sheet Translation will also be replaced, so you need to copy it first to another workbook & then copy it back.

I have tried to do everything you proposed and still the process is too slow.
How big is your data? How many rows in each sheet?
Can you show us your new code?
<!--[if gte mso 9]><xml> <o:OfficeDocumentSettings> <o:RelyOnVML/> <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-US</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="false" DefSemiHidden="false" DefQFormat="false" DefPriority="99" LatentStyleCount="371"> <w:LsdException Locked="false" Priority="0" QFormat="true" Name="Normal"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 1"/> <w:LsdException Locked="false" Priority="9" SemiHidden="true" UnhideWhenUsed="true" QFormat="true" Name="heading 2"/> <w:LsdException Locked="false" Priority="9" SemiHidden="true" UnhideWhenUsed="true" QFormat="true" Name="heading 3"/> <w:LsdException Locked="false" Priority="9" SemiHidden="true" UnhideWhenUsed="true" QFormat="true" Name="heading 4"/> <w:LsdException Locked="false" Priority="9" SemiHidden="true" UnhideWhenUsed="true" QFormat="true" Name="heading 5"/> <w:LsdException Locked="false" Priority="9" SemiHidden="true" UnhideWhenUsed="true" QFormat="true" Name="heading 6"/> <w:LsdException Locked="false" Priority="9" SemiHidden="true" UnhideWhenUsed="true" QFormat="true" Name="heading 7"/> <w:LsdException Locked="false" Priority="9" SemiHidden="true" UnhideWhenUsed="true" QFormat="true" Name="heading 8"/> <w:LsdException Locked="false" Priority="9" SemiHidden="true" UnhideWhenUsed="true" QFormat="true" Name="heading 9"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="index 1"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="index 2"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="index 3"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="index 4"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="index 5"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="index 6"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="index 7"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="index 8"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="index 9"/> <w:LsdException Locked="false" Priority="39" SemiHidden="true" UnhideWhenUsed="true" Name="toc 1"/> <w:LsdException Locked="false" Priority="39" SemiHidden="true" UnhideWhenUsed="true" Name="toc 2"/> <w:LsdException Locked="false" Priority="39" SemiHidden="true" UnhideWhenUsed="true" Name="toc 3"/> <w:LsdException Locked="false" Priority="39" SemiHidden="true" UnhideWhenUsed="true" Name="toc 4"/> <w:LsdException Locked="false" Priority="39" SemiHidden="true" UnhideWhenUsed="true" Name="toc 5"/> <w:LsdException Locked="false" Priority="39" SemiHidden="true" UnhideWhenUsed="true" Name="toc 6"/> <w:LsdException Locked="false" Priority="39" SemiHidden="true" UnhideWhenUsed="true" Name="toc 7"/> <w:LsdException Locked="false" Priority="39" SemiHidden="true" UnhideWhenUsed="true" Name="toc 8"/> <w:LsdException Locked="false" Priority="39" SemiHidden="true" UnhideWhenUsed="true" Name="toc 9"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Normal Indent"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="footnote text"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="annotation text"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="header"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="footer"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="index heading"/> <w:LsdException Locked="false" Priority="35" SemiHidden="true" UnhideWhenUsed="true" QFormat="true" Name="caption"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="table of figures"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="envelope address"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="envelope return"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="footnote reference"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="annotation reference"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="line number"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="page number"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="endnote reference"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="endnote text"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="table of authorities"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="macro"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="toa heading"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="List"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="List Bullet"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="List Number"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="List 2"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="List 3"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="List 4"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="List 5"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="List Bullet 2"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="List Bullet 3"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="List Bullet 4"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="List Bullet 5"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="List Number 2"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="List Number 3"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="List Number 4"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="List Number 5"/> <w:LsdException Locked="false" Priority="10" QFormat="true" Name="Title"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Closing"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Signature"/> <w:LsdException Locked="false" Priority="1" SemiHidden="true" UnhideWhenUsed="true" Name="Default Paragraph Font"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Body Text"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Body Text Indent"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="List Continue"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="List Continue 2"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="List Continue 3"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="List Continue 4"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="List Continue 5"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Message Header"/> <w:LsdException Locked="false" Priority="11" QFormat="true" Name="Subtitle"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Salutation"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Date"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Body Text First Indent"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Body Text First Indent 2"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Note Heading"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Body Text 2"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Body Text 3"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Body Text Indent 2"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Body Text Indent 3"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Block Text"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Hyperlink"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="FollowedHyperlink"/> <w:LsdException Locked="false" Priority="22" QFormat="true" Name="Strong"/> <w:LsdException Locked="false" Priority="20" QFormat="true" Name="Emphasis"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Document Map"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Plain Text"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="E-mail Signature"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="HTML Top of Form"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="HTML Bottom of Form"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Normal (Web)"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="HTML Acronym"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="HTML Address"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="HTML Cite"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="HTML Code"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="HTML Definition"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="HTML Keyboard"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="HTML Preformatted"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="HTML Sample"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="HTML Typewriter"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="HTML Variable"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Normal Table"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="annotation subject"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="No List"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Outline List 1"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Outline List 2"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Outline List 3"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table Simple 1"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table Simple 2"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table Simple 3"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table Classic 1"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table Classic 2"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table Classic 3"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table Classic 4"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table Colorful 1"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table Colorful 2"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table Colorful 3"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table Columns 1"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table Columns 2"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table Columns 3"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table Columns 4"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table Columns 5"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table Grid 1"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table Grid 2"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table Grid 3"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table Grid 4"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table Grid 5"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table Grid 6"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table Grid 7"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table Grid 8"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table List 1"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table List 2"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table List 3"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table List 4"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table List 5"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table List 6"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table List 7"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table List 8"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table 3D effects 1"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table 3D effects 2"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table 3D effects 3"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table Contemporary"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table Elegant"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table Professional"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table Subtle 1"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table Subtle 2"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table Web 1"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table Web 2"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table Web 3"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Balloon Text"/> <w:LsdException Locked="false" Priority="39" Name="Table Grid"/> <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true" Name="Table Theme"/> <w:LsdException Locked="false" SemiHidden="true" Name="Placeholder Text"/> <w:LsdException Locked="false" Priority="1" QFormat="true" Name="No Spacing"/> <w:LsdException Locked="false" Priority="60" Name="Light Shading"/> <w:LsdException Locked="false" Priority="61" Name="Light List"/> <w:LsdException Locked="false" Priority="62" Name="Light Grid"/> <w:LsdException Locked="false" Priority="63" Name="Medium Shading 1"/> <w:LsdException Locked="false" Priority="64" Name="Medium Shading 2"/> <w:LsdException Locked="false" Priority="65" Name="Medium List 1"/> <w:LsdException Locked="false" Priority="66" Name="Medium List 2"/> <w:LsdException Locked="false" Priority="67" Name="Medium Grid 1"/> <w:LsdException Locked="false" Priority="68" Name="Medium Grid 2"/> <w:LsdException Locked="false" Priority="69" Name="Medium Grid 3"/> <w:LsdException Locked="false" Priority="70" Name="Dark List"/> <w:LsdException Locked="false" Priority="71" Name="Colorful Shading"/> <w:LsdException Locked="false" Priority="72" Name="Colorful List"/> <w:LsdException Locked="false" Priority="73" Name="Colorful Grid"/> <w:LsdException Locked="false" Priority="60" Name="Light Shading Accent 1"/> <w:LsdException Locked="false" Priority="61" Name="Light List Accent 1"/> <w:LsdException Locked="false" Priority="62" Name="Light Grid Accent 1"/> <w:LsdException Locked="false" Priority="63" Name="Medium Shading 1 Accent 1"/> <w:LsdException Locked="false" Priority="64" Name="Medium Shading 2 Accent 1"/> <w:LsdException Locked="false" Priority="65" Name="Medium List 1 Accent 1"/> <w:LsdException Locked="false" SemiHidden="true" Name="Revision"/> <w:LsdException Locked="false" Priority="34" QFormat="true" Name="List Paragraph"/> <w:LsdException Locked="false" Priority="29" QFormat="true" Name="Quote"/> <w:LsdException Locked="false" Priority="30" QFormat="true" Name="Intense Quote"/> <w:LsdException Locked="false" Priority="66" Name="Medium List 2 Accent 1"/> <w:LsdException Locked="false" Priority="67" Name="Medium Grid 1 Accent 1"/> <w:LsdException Locked="false" Priority="68" Name="Medium Grid 2 Accent 1"/> <w:LsdException Locked="false" Priority="69" Name="Medium Grid 3 Accent 1"/> <w:LsdException Locked="false" Priority="70" Name="Dark List Accent 1"/> <w:LsdException Locked="false" Priority="71" Name="Colorful Shading Accent 1"/> <w:LsdException Locked="false" Priority="72" Name="Colorful List Accent 1"/> <w:LsdException Locked="false" Priority="73" Name="Colorful Grid Accent 1"/> <w:LsdException Locked="false" Priority="60" Name="Light Shading Accent 2"/> <w:LsdException Locked="false" Priority="61" Name="Light List Accent 2"/> <w:LsdException Locked="false" Priority="62" Name="Light Grid Accent 2"/> <w:LsdException Locked="false" Priority="63" Name="Medium Shading 1 Accent 2"/> <w:LsdException Locked="false" Priority="64" Name="Medium Shading 2 Accent 2"/> <w:LsdException Locked="false" Priority="65" Name="Medium List 1 Accent 2"/> <w:LsdException Locked="false" Priority="66" Name="Medium List 2 Accent 2"/> <w:LsdException Locked="false" Priority="67" Name="Medium Grid 1 Accent 2"/> <w:LsdException Locked="false" Priority="68" Name="Medium Grid 2 Accent 2"/> <w:LsdException Locked="false" Priority="69" Name="Medium Grid 3 Accent 2"/> <w:LsdException Locked="false" Priority="70" Name="Dark List Accent 2"/> <w:LsdException Locked="false" Priority="71" Name="Colorful Shading Accent 2"/> <w:LsdException Locked="false" Priority="72" Name="Colorful List Accent 2"/> <w:LsdException Locked="false" Priority="73" Name="Colorful Grid Accent 2"/> <w:LsdException Locked="false" Priority="60" Name="Light Shading Accent 3"/> <w:LsdException Locked="false" Priority="61" Name="Light List Accent 3"/> <w:LsdException Locked="false" Priority="62" Name="Light Grid Accent 3"/> <w:LsdException Locked="false" Priority="63" Name="Medium Shading 1 Accent 3"/> <w:LsdException Locked="false" Priority="64" Name="Medium Shading 2 Accent 3"/> <w:LsdException Locked="false" Priority="65" Name="Medium List 1 Accent 3"/> <w:LsdException Locked="false" Priority="66" Name="Medium List 2 Accent 3"/> <w:LsdException Locked="false" Priority="67" Name="Medium Grid 1 Accent 3"/> <w:LsdException Locked="false" Priority="68" Name="Medium Grid 2 Accent 3"/> <w:LsdException Locked="false" Priority="69" Name="Medium Grid 3 Accent 3"/> <w:LsdException Locked="false" Priority="70" Name="Dark List Accent 3"/> <w:LsdException Locked="false" Priority="71" Name="Colorful Shading Accent 3"/> <w:LsdException Locked="false" Priority="72" Name="Colorful List Accent 3"/> <w:LsdException Locked="false" Priority="73" Name="Colorful Grid Accent 3"/> <w:LsdException Locked="false" Priority="60" Name="Light Shading Accent 4"/> <w:LsdException Locked="false" Priority="61" Name="Light List Accent 4"/> <w:LsdException Locked="false" Priority="62" Name="Light Grid Accent 4"/> <w:LsdException Locked="false" Priority="63" Name="Medium Shading 1 Accent 4"/> <w:LsdException Locked="false" Priority="64" Name="Medium Shading 2 Accent 4"/> <w:LsdException Locked="false" Priority="65" Name="Medium List 1 Accent 4"/> <w:LsdException Locked="false" Priority="66" Name="Medium List 2 Accent 4"/> <w:LsdException Locked="false" Priority="67" Name="Medium Grid 1 Accent 4"/> <w:LsdException Locked="false" Priority="68" Name="Medium Grid 2 Accent 4"/> <w:LsdException Locked="false" Priority="69" Name="Medium Grid 3 Accent 4"/> <w:LsdException Locked="false" Priority="70" Name="Dark List Accent 4"/> <w:LsdException Locked="false" Priority="71" Name="Colorful Shading Accent 4"/> <w:LsdException Locked="false" Priority="72" Name="Colorful List Accent 4"/> <w:LsdException Locked="false" Priority="73" Name="Colorful Grid Accent 4"/> <w:LsdException Locked="false" Priority="60" Name="Light Shading Accent 5"/> <w:LsdException Locked="false" Priority="61" Name="Light List Accent 5"/> <w:LsdException Locked="false" Priority="62" Name="Light Grid Accent 5"/> <w:LsdException Locked="false" Priority="63" Name="Medium Shading 1 Accent 5"/> <w:LsdException Locked="false" Priority="64" Name="Medium Shading 2 Accent 5"/> <w:LsdException Locked="false" Priority="65" Name="Medium List 1 Accent 5"/> <w:LsdException Locked="false" Priority="66" Name="Medium List 2 Accent 5"/> <w:LsdException Locked="false" Priority="67" Name="Medium Grid 1 Accent 5"/> <w:LsdException Locked="false" Priority="68" Name="Medium Grid 2 Accent 5"/> <w:LsdException Locked="false" Priority="69" Name="Medium Grid 3 Accent 5"/> <w:LsdException Locked="false" Priority="70" Name="Dark List Accent 5"/> <w:LsdException Locked="false" Priority="71" Name="Colorful Shading Accent 5"/> <w:LsdException Locked="false" Priority="72" Name="Colorful List Accent 5"/> <w:LsdException Locked="false" Priority="73" Name="Colorful Grid Accent 5"/> <w:LsdException Locked="false" Priority="60" Name="Light Shading Accent 6"/> <w:LsdException Locked="false" Priority="61" Name="Light List Accent 6"/> <w:LsdException Locked="false" Priority="62" Name="Light Grid Accent 6"/> <w:LsdException Locked="false" Priority="63" Name="Medium Shading 1 Accent 6"/> <w:LsdException Locked="false" Priority="64" Name="Medium Shading 2 Accent 6"/> <w:LsdException Locked="false" Priority="65" Name="Medium List 1 Accent 6"/> <w:LsdException Locked="false" Priority="66" Name="Medium List 2 Accent 6"/> <w:LsdException Locked="false" Priority="67" Name="Medium Grid 1 Accent 6"/> <w:LsdException Locked="false" Priority="68" Name="Medium Grid 2 Accent 6"/> <w:LsdException Locked="false" Priority="69" Name="Medium Grid 3 Accent 6"/> <w:LsdException Locked="false" Priority="70" Name="Dark List Accent 6"/> <w:LsdException Locked="false" Priority="71" Name="Colorful Shading Accent 6"/> <w:LsdException Locked="false" Priority="72" Name="Colorful List Accent 6"/> <w:LsdException Locked="false" Priority="73" Name="Colorful Grid Accent 6"/> <w:LsdException Locked="false" Priority="19" QFormat="true" Name="Subtle Emphasis"/> <w:LsdException Locked="false" Priority="21" QFormat="true" Name="Intense Emphasis"/> <w:LsdException Locked="false" Priority="31" QFormat="true" Name="Subtle Reference"/> <w:LsdException Locked="false" Priority="32" QFormat="true" Name="Intense Reference"/> <w:LsdException Locked="false" Priority="33" QFormat="true" Name="Book Title"/> <w:LsdException Locked="false" Priority="37" SemiHidden="true" UnhideWhenUsed="true" Name="Bibliography"/> <w:LsdException Locked="false" Priority="39" SemiHidden="true" UnhideWhenUsed="true" QFormat="true" Name="TOC Heading"/> <w:LsdException Locked="false" Priority="41" Name="Plain Table 1"/> <w:LsdException Locked="false" Priority="42" Name="Plain Table 2"/> <w:LsdException Locked="false" Priority="43" Name="Plain Table 3"/> <w:LsdException Locked="false" Priority="44" Name="Plain Table 4"/> <w:LsdException Locked="false" Priority="45" Name="Plain Table 5"/> <w:LsdException Locked="false" Priority="40" Name="Grid Table Light"/> <w:LsdException Locked="false" Priority="46" Name="Grid Table 1 Light"/> <w:LsdException Locked="false" Priority="47" Name="Grid Table 2"/> <w:LsdException Locked="false" Priority="48" Name="Grid Table 3"/> <w:LsdException Locked="false" Priority="49" Name="Grid Table 4"/> <w:LsdException Locked="false" Priority="50" Name="Grid Table 5 Dark"/> <w:LsdException Locked="false" Priority="51" Name="Grid Table 6 Colorful"/> <w:LsdException Locked="false" Priority="52" Name="Grid Table 7 Colorful"/> <w:LsdException Locked="false" Priority="46" Name="Grid Table 1 Light Accent 1"/> <w:LsdException Locked="false" Priority="47" Name="Grid Table 2 Accent 1"/> <w:LsdException Locked="false" Priority="48" Name="Grid Table 3 Accent 1"/> <w:LsdException Locked="false" Priority="49" Name="Grid Table 4 Accent 1"/> <w:LsdException Locked="false" Priority="50" Name="Grid Table 5 Dark Accent 1"/> <w:LsdException Locked="false" Priority="51" Name="Grid Table 6 Colorful Accent 1"/> <w:LsdException Locked="false" Priority="52" Name="Grid Table 7 Colorful Accent 1"/> <w:LsdException Locked="false" Priority="46" Name="Grid Table 1 Light Accent 2"/> <w:LsdException Locked="false" Priority="47" Name="Grid Table 2 Accent 2"/> <w:LsdException Locked="false" Priority="48" Name="Grid Table 3 Accent 2"/> <w:LsdException Locked="false" Priority="49" Name="Grid Table 4 Accent 2"/> <w:LsdException Locked="false" Priority="50" Name="Grid Table 5 Dark Accent 2"/> <w:LsdException Locked="false" Priority="51" Name="Grid Table 6 Colorful Accent 2"/> <w:LsdException Locked="false" Priority="52" Name="Grid Table 7 Colorful Accent 2"/> <w:LsdException Locked="false" Priority="46" Name="Grid Table 1 Light Accent 3"/> <w:LsdException Locked="false" Priority="47" Name="Grid Table 2 Accent 3"/> <w:LsdException Locked="false" Priority="48" Name="Grid Table 3 Accent 3"/> <w:LsdException Locked="false" Priority="49" Name="Grid Table 4 Accent 3"/> <w:LsdException Locked="false" Priority="50" Name="Grid Table 5 Dark Accent 3"/> <w:LsdException Locked="false" Priority="51" Name="Grid Table 6 Colorful Accent 3"/> <w:LsdException Locked="false" Priority="52" Name="Grid Table 7 Colorful Accent 3"/> <w:LsdException Locked="false" Priority="46" Name="Grid Table 1 Light Accent 4"/> <w:LsdException Locked="false" Priority="47" Name="Grid Table 2 Accent 4"/> <w:LsdException Locked="false" Priority="48" Name="Grid Table 3 Accent 4"/> <w:LsdException Locked="false" Priority="49" Name="Grid Table 4 Accent 4"/> <w:LsdException Locked="false" Priority="50" Name="Grid Table 5 Dark Accent 4"/> <w:LsdException Locked="false" Priority="51" Name="Grid Table 6 Colorful Accent 4"/> <w:LsdException Locked="false" Priority="52" Name="Grid Table 7 Colorful Accent 4"/> <w:LsdException Locked="false" Priority="46" Name="Grid Table 1 Light Accent 5"/> <w:LsdException Locked="false" Priority="47" Name="Grid Table 2 Accent 5"/> <w:LsdException Locked="false" Priority="48" Name="Grid Table 3 Accent 5"/> <w:LsdException Locked="false" Priority="49" Name="Grid Table 4 Accent 5"/> <w:LsdException Locked="false" Priority="50" Name="Grid Table 5 Dark Accent 5"/> <w:LsdException Locked="false" Priority="51" Name="Grid Table 6 Colorful Accent 5"/> <w:LsdException Locked="false" Priority="52" Name="Grid Table 7 Colorful Accent 5"/> <w:LsdException Locked="false" Priority="46" Name="Grid Table 1 Light Accent 6"/> <w:LsdException Locked="false" Priority="47" Name="Grid Table 2 Accent 6"/> <w:LsdException Locked="false" Priority="48" Name="Grid Table 3 Accent 6"/> <w:LsdException Locked="false" Priority="49" Name="Grid Table 4 Accent 6"/> <w:LsdException Locked="false" Priority="50" Name="Grid Table 5 Dark Accent 6"/> <w:LsdException Locked="false" Priority="51" Name="Grid Table 6 Colorful Accent 6"/> <w:LsdException Locked="false" Priority="52" Name="Grid Table 7 Colorful Accent 6"/> <w:LsdException Locked="false" Priority="46" Name="List Table 1 Light"/> <w:LsdException Locked="false" Priority="47" Name="List Table 2"/> <w:LsdException Locked="false" Priority="48" Name="List Table 3"/> <w:LsdException Locked="false" Priority="49" Name="List Table 4"/> <w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark"/> <w:LsdException Locked="false" Priority="51" Name="List Table 6 Colorful"/> <w:LsdException Locked="false" Priority="52" Name="List Table 7 Colorful"/> <w:LsdException Locked="false" Priority="46" Name="List Table 1 Light Accent 1"/> <w:LsdException Locked="false" Priority="47" Name="List Table 2 Accent 1"/> <w:LsdException Locked="false" Priority="48" Name="List Table 3 Accent 1"/> <w:LsdException Locked="false" Priority="49" Name="List Table 4 Accent 1"/> <w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark Accent 1"/> <w:LsdException Locked="false" Priority="51" Name="List Table 6 Colorful Accent 1"/> <w:LsdException Locked="false" Priority="52" Name="List Table 7 Colorful Accent 1"/> <w:LsdException Locked="false" Priority="46" Name="List Table 1 Light Accent 2"/> <w:LsdException Locked="false" Priority="47" Name="List Table 2 Accent 2"/> <w:LsdException Locked="false" Priority="48" Name="List Table 3 Accent 2"/> <w:LsdException Locked="false" Priority="49" Name="List Table 4 Accent 2"/> <w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark Accent 2"/> <w:LsdException Locked="false" Priority="51" Name="List Table 6 Colorful Accent 2"/> <w:LsdException Locked="false" Priority="52" Name="List Table 7 Colorful Accent 2"/> <w:LsdException Locked="false" Priority="46" Name="List Table 1 Light Accent 3"/> <w:LsdException Locked="false" Priority="47" Name="List Table 2 Accent 3"/> <w:LsdException Locked="false" Priority="48" Name="List Table 3 Accent 3"/> <w:LsdException Locked="false" Priority="49" Name="List Table 4 Accent 3"/> <w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark Accent 3"/> <w:LsdException Locked="false" Priority="51" Name="List Table 6 Colorful Accent 3"/> <w:LsdException Locked="false" Priority="52" Name="List Table 7 Colorful Accent 3"/> <w:LsdException Locked="false" Priority="46" Name="List Table 1 Light Accent 4"/> <w:LsdException Locked="false" Priority="47" Name="List Table 2 Accent 4"/> <w:LsdException Locked="false" Priority="48" Name="List Table 3 Accent 4"/> <w:LsdException Locked="false" Priority="49" Name="List Table 4 Accent 4"/> <w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark Accent 4"/> <w:LsdException Locked="false" Priority="51" Name="List Table 6 Colorful Accent 4"/> <w:LsdException Locked="false" Priority="52" Name="List Table 7 Colorful Accent 4"/> <w:LsdException Locked="false" Priority="46" Name="List Table 1 Light Accent 5"/> <w:LsdException Locked="false" Priority="47" Name="List Table 2 Accent 5"/> <w:LsdException Locked="false" Priority="48" Name="List Table 3 Accent 5"/> <w:LsdException Locked="false" Priority="49" Name="List Table 4 Accent 5"/> <w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark Accent 5"/> <w:LsdException Locked="false" Priority="51" Name="List Table 6 Colorful Accent 5"/> <w:LsdException Locked="false" Priority="52" Name="List Table 7 Colorful Accent 5"/> <w:LsdException Locked="false" Priority="46" Name="List Table 1 Light Accent 6"/> <w:LsdException Locked="false" Priority="47" Name="List Table 2 Accent 6"/> <w:LsdException Locked="false" Priority="48" Name="List Table 3 Accent 6"/> <w:LsdException Locked="false" Priority="49" Name="List Table 4 Accent 6"/> <w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark Accent 6"/> <w:LsdException Locked="false" Priority="51" Name="List Table 6 Colorful Accent 6"/> <w:LsdException Locked="false" Priority="52" Name="List Table 7 Colorful Accent 6"/> </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:8.0pt; mso-para-margin-left:0cm; line-height:107%; 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;} </style> <![endif]-->
 

gubertu

Board Regular
Joined
May 24, 2015
Messages
101
Hello,

this is my full code. It is excluding the sheet "Translation" so I think I do not need to copy it in another WB. I also included ScreenUpdating and others codes to make it faster.

My WB has 40 sheets and the rows are different in every sheet. Not more than 250 rows in every sheet.

Hope you could help me.

Thanks!




Code:
Sub Change_language()
 
    
    Const lang1 As Long = 1
    Const lang2 As Long = 2
    Dim arr1    As Variant
    Dim arr2    As Variant
    Dim i       As Long
    Dim lr      As Long
    Dim ws      As Worksheet
    Dim WS1 As Worksheet
    Set WS1 = Sheets("Guide")
        
Application.ScreenUpdating = False
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False


    With ThisWorkbook.Worksheets("Traslation")
        lr = .Cells(.Rows.Count, 1).End(xlUp).Row
        arr1 = .Range(.Cells(2, lang2), .Cells(lr, lang2))
        arr2 = .Range(.Cells(2, lang1), .Cells(lr, lang1))
    End With
    
   For Each ws In ThisWorkbook.Worksheets
        With ws
            Select Case (ws.Name)
                Case "Traslation"
                Case Else
                    For i = 1 To lr - 1
                        .Cells.SpecialCells(xlCellTypeConstants).Replace What:=arr1(i, 1), Replacement:=arr2(i, 1), LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                            ReplaceFormat:=False
                    Next
            End Select
        End With
    Next
    
Application.ScreenUpdating = True
Application.EnableEvents = True
ActiveSheet.DisplayPageBreaks = True
Application.CutCopyMode = False


End Sub
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,385
Office Version
2013
Platform
Windows
Actually you missed the main point I mentioned earlier, i.e
• Revise your code so it won’t loop through sheet by sheet.

So again do these steps using the revised code below:
• Copy sheet "Translation" to another workbook then go back to your workbook.
• Open replace dialog box (Ctrl+H) and set “Within” to “Workbook” then close it. This is to set the replace within all sheet all at once.
• Run the code.
• Copy back the sheet "Translation".

Code:
[FONT=lucida console]
[COLOR=Royalblue]Sub[/COLOR] Change_language()
 
    
    [COLOR=Royalblue]Const[/COLOR] lang1 [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR] = [COLOR=crimson]1[/COLOR]
    [COLOR=Royalblue]Const[/COLOR] lang2 [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR] = [COLOR=crimson]2[/COLOR]
    [COLOR=Royalblue]Dim[/COLOR] arr1    [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Variant[/COLOR]
    [COLOR=Royalblue]Dim[/COLOR] arr2    [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Variant[/COLOR]
    [COLOR=Royalblue]Dim[/COLOR] i       [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
    [COLOR=Royalblue]Dim[/COLOR] lr      [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
    [COLOR=Royalblue]Dim[/COLOR] ws      [COLOR=Royalblue]As[/COLOR] Worksheet
    [COLOR=Royalblue]Dim[/COLOR] WS1 [COLOR=Royalblue]As[/COLOR] Worksheet
[I][COLOR=seagreen]'    Set WS1 = Sheets("Guide")[/COLOR][/I]
        
Application.ScreenUpdating = [COLOR=Royalblue]False[/COLOR]
Application.EnableEvents = [COLOR=Royalblue]False[/COLOR]
ActiveSheet.DisplayPageBreaks = [COLOR=Royalblue]False[/COLOR]


    [COLOR=Royalblue]With[/COLOR] ThisWorkbook.Worksheets([COLOR=brown]"Traslation"[/COLOR])
        lr = .Cells(.Rows.Count, [COLOR=crimson]1[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp).Row
        arr1 = .Range(.Cells([COLOR=crimson]2[/COLOR], lang1), .Cells(lr, lang2))
[I][COLOR=seagreen]'        arr2 = .Range(.Cells(2, lang1), .Cells(lr, lang1))[/COLOR][/I]
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]
    
                    [COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(arr1, [COLOR=crimson]1[/COLOR])
                        Cells.SpecialCells(xlCellTypeConstants).Replace What:=arr1(i, [COLOR=crimson]1[/COLOR]), Replacement:=arr1(i, [COLOR=crimson]2[/COLOR]), LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, MatchCase:=[COLOR=Royalblue]False[/COLOR], SearchFormat:=[COLOR=Royalblue]False[/COLOR], _
                            ReplaceFormat:=[COLOR=Royalblue]False[/COLOR]
                    [COLOR=Royalblue]Next[/COLOR]
    
Application.ScreenUpdating = [COLOR=Royalblue]True[/COLOR]
Application.EnableEvents = [COLOR=Royalblue]True[/COLOR]
ActiveSheet.DisplayPageBreaks = [COLOR=Royalblue]True[/COLOR]
Application.CutCopyMode = [COLOR=Royalblue]False[/COLOR]


[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]
 

gubertu

Board Regular
Joined
May 24, 2015
Messages
101
Hello,

I´ve been testing your code in the last few weeks and as I reduce some time, it is taking me 13 minutes to run all the code.

Do you know another way to translate a book of 40 sheets and 1.300 words in less time?

Thanks!
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,385
Office Version
2013
Platform
Windows
My WB has 40 sheets and the rows are different in every sheet. Not more than 250 rows in every sheet
.
How many column do you have in each sheet?
The words to be replaced, are they only in certain column or in all column? If they are only in certain column then we can limit the search on that column.
 

Forum statistics

Threads
1,082,641
Messages
5,366,717
Members
400,915
Latest member
NFlem

Some videos you may like

This Week's Hot Topics

Top