Find and Replace

kapfrank

Board Regular
Joined
Nov 16, 2005
Messages
112
I found the next code to do this
Code:
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
Code:
    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?
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Joined
Jul 30, 2006
Messages
3,656
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:

Code:
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

Board Regular
Joined
Nov 16, 2005
Messages
112
I solved the problem!
Code:
    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?
 
Joined
Jul 30, 2006
Messages
3,656
kapfrank

This new code has not been tested.

Try this FIRST on a TEST copy of your workbook.

Code:
    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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,596
Messages
5,625,714
Members
416,130
Latest member
galgozzi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top