I have a question about adjusting one of the macros I have in my conversion xlsm files. Should I start a different post or asking in this one is fine?
I have the macro code below look within my xlsm file and replaces text. It has a different excel file it uses to know what to replace. I am having an issue that it is replacing items it should not. I have attached the xls files it uses to know what to replace and the raw data file that it searches.
The issue is it is searching all the columns. I only need it to search column D for "Call ReplaceAllSheets(Worksheets("Data").Range("A1"))". Then for both "Call ReplaceAllSheets(Worksheets("Data").Range("D1")) and Call ReplaceAllSheets(Worksheets("Data").Range("G1")) it only needs to look in column C. It is replacing items in the wrong column. How can I restrict it to only search column D for products and only search column C for customers?
Option Explicit
Sub Replaces()
Dim wbData As Workbook
Application.ScreenUpdating = False
'delete Data is if still exists
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Data").Delete
Application.DisplayAlerts = True
On Error GoTo 0
'open Replaces workbook and copy data sheet in
Workbooks.Open Filename:="C:\FlexibakeConversions\FlexreplacedataInvoice.xlsx" ' <<<<<<<<<<<<<<<<<
Set wbData = ActiveWorkbook
wbData.Worksheets("Data").Copy Before:=ThisWorkbook.Worksheets(1)
wbData.Close False
ThisWorkbook.Activate
'do the replaces
Call ReplaceAllSheets(Worksheets("Data").Range("A1"))
Call ReplaceAllSheets(Worksheets("Data").Range("D1"))
Call ReplaceAllSheets(Worksheets("Data").Range("G1"))
'get rid of Data
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Data").Delete
Application.DisplayAlerts = True
On Error GoTo 0
Application.ScreenUpdating = True
End Sub
'this sub is Private so that it's only usable in this module
Private Sub ReplaceAllSheets(R As Range)
Dim i As Long
Dim ws As Worksheet
Dim r1 As Range
Set r1 = R.CurrentRegion
If r1.Rows.Count < 2 Then Exit Sub
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "Data" Then GoTo GetNextSheet
If ws.UsedRange.Cells.Count < 2 Then GoTo GetNextSheet
For i = 2 To r1.Rows.Count
ws.UsedRange.Cells.Replace What:=r1.Cells(i, 1).Value, Replacement:=r1.Cells(i, 2).Value, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next i
GetNextSheet:
Next
End Sub