Find and Replace

[kapfrank]

I found the next code to do this

Dim ws As Worksheet 

For Each ws In ActiveWorkbook.Worksheets 
    ws.Cells.Replace What:="per *", Replacement:="", LookAt:=xlPart 
Next ws

So I rewrote this to

    Workbooks("VCS.xls").Worksheets("Invoer").Delete

    Workbooks("VCS - (leeg) v3.2.xls").Worksheets("Invoer").Copy before:=Workbooks("VCS.xls").Sheets(3)
    
    Dim ws As Worksheet

    For Each ws In Workbooks("VCS.xls").Worksheets
        ws.Cells.Replace What:="=#VERW!", Replacement:="=INVOER!", LookAt:=xlPart
    Next ws

But it’s not working

What should happen
- I deleted the worksheet 'Invoer', from the file “VCS.xls”
- I inserted the worksheet 'Invoer', from the file “VCS - (leeg) v3.2.xls.xls”
- i know there are worksheets that have an error message =#VERW! (because of the removal of the worksheet ‘Invoer’)
- So I want to replace this with =INVOER!

This should happen in different worksheets but all the errors are in the same range = Range("A5:E204").

What I’m doing wrong?

 

[stanleydgromjr]

kapfrank,

You should be able to copy the external data directly into/on top of "INVOER".

Try this FIRST on a TEST copy of your workbook.

How about:

Sub CopyExternalData()

    'Workbooks("VCS - (leeg) v3.2.xls").Worksheets("Invoer").Copy before:=Workbooks("VCS.xls").Sheets("INVOER")
    Workbooks("VCS - (leeg) v3.2.xls").Worksheets("Invoer").Cells.Copy Desitnation:=Sheets("INVOER").Cells
    Application.CutCopyMode = False

End Sub

Have a great day,
Stan

 

[kapfrank]

I solved the problem!

    Workbooks("VCS.xls").Worksheets("INVOER").Name = "temp"
    Workbooks("VCS - (leeg) v3.2.xls").Worksheets("Invoer").Unprotect "bmv"
    Workbooks("VCS - (leeg) v3.2.xls").Worksheets("Invoer").Copy Before:=Workbooks("VCS.xls").Sheets(4)
    
    Sheets("temp").Select
    Range("A6:G205").Select
    Selection.Copy
    Sheets("INVOER").Select
    Range("A6").Select
    ActiveSheet.Paste
    
    Sheets("VERPLICHT").Select
    Range("A5:E204").Select
    Selection.Replace What:="temp", Replacement:="INVOER", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    
    Sheets("DIEMACO").Select
    Range("A5:F204").Select
    Selection.Replace What:="temp", Replacement:="INVOER", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    
    Sheets("GLOCK").Select
    Range("A5:F204").Select
    Selection.Replace What:="temp", Replacement:="INVOER", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    
    Sheets("MAG").Select
    Range("A5:E204").Select
    Selection.Replace What:="temp", Replacement:="INVOER", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    
    Sheets("VMO").Select
    Range("A5:E204").Select
    Selection.Replace What:="temp", Replacement:="INVOER", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    
    Sheets(".50").Select
    Range("A5:E204").Select
    Selection.Replace What:="temp", Replacement:="INVOER", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    
    Sheets("Niv II-III-IV").Select
    Range("A5:E204").Select
    Selection.Replace What:="temp", Replacement:="INVOER", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    
    Workbooks("VCS.xls").Worksheets("temp").Delete

Is there a way to shorten this?

 

[stanleydgromjr]

kapfrank

This new code has not been tested.

Try this FIRST on a TEST copy of your workbook.

    Workbooks("VCS.xls").Worksheets("INVOER").Name = "temp"
    Workbooks("VCS - (leeg) v3.2.xls").Worksheets("Invoer").Unprotect "bmv"
    Workbooks("VCS - (leeg) v3.2.xls").Worksheets("Invoer").Copy Before:=Workbooks("VCS.xls").Sheets(4)
    
    Sheets("temp").Select
    Range("A6:G205").Select
    Selection.Copy
    Sheets("INVOER").Select
    Range("A6").Select
    ActiveSheet.Paste

    'This new code has not been tested
    Dim j As Integer
    Dim wksArray As Variant
    wksArray = Array("VERPLICHT", "DIEMACO", "GLOCK", "MAG", "VMO", ".50", "Niv II-III-IV")

    For j = LBound(wksArray) To UBound(wksArray)
        Sheets(wksArray(j)).Select
        Range("A5:E204").Select
        Selection.Replace What:="temp", Replacement:="INVOER", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    Next j


    Workbooks("VCS.xls").Worksheets("temp").Delete

Have a great day,
Stan

 

[kapfrank]

Stan, thank you.