Convert Many Invoice Formats to a Standard Format

[joeny0706]

I have two more task I need then I will be able to start uploading my Flexibake raw data using the process I do now.

Back in post 29 I asked
I have one task that I will need to do also. I have 7 different stores that I need to delete out before converting. They need to be removed before going into the accounting program. I have a module that deletes any rows with 0 in the qty column. What I would do is alter that module to delete any rows that have these stores in the name column. Since I think you are working on a new process to do all my converting I wanted to see if that is something I will need to do "create a module to delete these names" or will that be a task the new process will be able to do?

Also do you have any questions or anything I can do to help?

Attached are the store names that I will need to delete before doing the converting. This is only with the flexibake raw data. I will not need this task for any of the other raw data.
This task will need to happen when I am running the macro Paul created for me that fixes the credit identification numbers. I attached the CSV Fix xlsm file. If that can be altered to also delete out stores I dont need. These stores need to be removed before I enter the data into my current xlsm that does all the converting.



Also back in post 69 I asked this.

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 xlsx 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 xlsx 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




Thanks all for the help. This will get me going for now. My process is not the most efficient but for now it does allow me to complete the task I am assigned.