Using a macro in multiple files and call another workbook

Paulandreaoc · May 25th 2020

Hi guys, I have this macro that runs perfectly but I would like to use it in multiple files at the same time because right now just runs one at the same time, also I would like a little help because my "DataBase" is in another workbook is not in a sheet of the workbook I am working in, and right now to use the info in the "DataBase" I am adding a need sheet (in my actual workbook) with it, this is not the ideal, the ideal is write in the macro that the "Database" is in another workbook.

I hope you can help me, thank you in advance

Code

Sub replace2()
    
    
    Application.ScreenUpdating = False
    Const str As String = "PERSONE COINVOLTE DEL CAB"
    Dim rCl As Range, ws As Worksheet, LastRow As Long, dbWS As Worksheet, Val As String, arr As Variant, arr2 As Variant, i As Long, x As Long
    Set dbWS = Sheets("DataBase")
    arr = dbWS.Range("A2", dbWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 3).Value
    arr2 = dbWS.Range("D2", dbWS.Range("D" & Rows.Count).End(xlUp)).Resize(, 2).Value
    Worksheets("Giudizio sintetico").Visible = False
    Worksheets("Riepilogo").Visible = False
    
    
    With Sheets("Anagrafica audit")
        Set rCl = .UsedRange.Find(str, LookIn:=xlValues, lookat:=xlWhole)
        LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        .Range("A" & rCl.Row + 2 & ":C" & LastRow).ClearContents
        For i = 1 To UBound(arr2, 1)
            .Range("B4").replace "?" & arr2(i, 1), arr2(i, 2)
        Next i
        .Range("b3:c3").UnMerge
        .Range("b3").ClearContents
        .Range("b5:c5").UnMerge
        .Range("b5").ClearContents
        For i = 1 To UBound(arr, 1)
            Val = arr(i, 2) & " " & arr(i, 1)
            .Cells.replace Val, arr(i, 3)
            Sheets("report stampabile").Cells.replace Val, arr(i, 3)
        Next i
    End With
        
    With Sheets("report stampabile")
        For x = 14 To 420 Step 10
            .Range("P" & x & ":S" & x).UnMerge
            .Range("P" & x).ClearContents
        Next x
    End With
    Sheets("DataBase").Delete
    Application.ScreenUpdating = True
End Sub

Display More

**royUK** · May 26th 2020

Why have the database in a separate workbook?

Paulandreaoc · May 26th 2020

The DataBase is always have been in a different workbook

**royUK** · May 26th 2020

Where are all the sheets referred to in the macro?

You need to explicitly refer to the workbook in your ode.

What is the workbook with the code in used for?

Paulandreaoc · May 26th 2020

The workbook code is use to replace names and numbers for codes, this is one of the files and in the other workbook is the DataBase

**royUK** · May 26th 2020

It doe't look like that code is actually amending anything in the database workbook.

Paulandreaoc · May 26th 2020

I know, the code just work if I add a sheet in the file with the DataBase info, this is one of the things I need to change

**royUK** · May 26th 2020

I can't see a sheet being added.

The code that you posted appears to remove formatting, etc on two sheets in the main workbook.I'm not really sure why you need to adda sheet.

Code

Sub replace2()




Application.ScreenUpdating = False
Const str As String = "PERSONE COINVOLTE DEL CAB"
Dim rCl As Range, ws As Worksheet, LastRow As Long, dbWS As Worksheet, Val As String, arr As Variant, arr2 As Variant, i As Long, x As Long
''/// this defines the database sheet in the DtaBase WorkBook
Set dbWS = Worksheets("DataBase").Sheets("DataBase")
arr = dbWS.Range("A2", dbWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 3).Value
arr2 = dbWS.Range("D2", dbWS.Range("D" & Rows.Count).End(xlUp)).Resize(, 2).Value


On Error GoTo exit_proc
Worksheets("Giudizio sintetico").Visible = False
Worksheets("Riepilogo").Visible = False




With Sheets("Anagrafica audit")
Set rCl = .UsedRange.Find(str, LookIn:=xlValues, lookat:=xlWhole)
LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
.Range("A" & rCl.Row + 2 & ":C" & LastRow).ClearContents
For i = 1 To UBound(arr2, 1)
.Range("B4").Replace "?" & arr2(i, 1), arr2(i, 2)
Next i
.Range("b3:c3").UnMerge
.Range("b3").ClearContents
.Range("b5:c5").UnMerge
.Range("b5").ClearContents
For i = 1 To UBound(arr, 1)
Val = arr(i, 2) & " " & arr(i, 1)
.Cells.Replace Val, arr(i, 3)
Sheets("report stampabile").Cells.Replace Val, arr(i, 3)
Next i
End With


With Sheets("report stampabile")
For x = 14 To 420 Step 10
.Range("P" & x & ":S" & x).UnMerge
.Range("P" & x).ClearContents
Next x
End With
''///why delete the database sheet
Sheets("DataBase").Delete
exit_proc:
Application.ScreenUpdating = True
End Sub

Display More

Paulandreaoc · May 26th 2020

Quote from royUK
If you can see in the next line of code, dbWS is a sheet from the file I am codifying, but actually is not (I have to add it because the macro does not run without that sheet) but "DataBase" is in another workbook what I wantis do not add the sheet of DataBase in every file I am modifying, but call that workbook in my macro.
Code
Set dbWS = Sheets("DataBase")

Paulandreaoc · May 26th 2020

when I use //Set dbWS = Worksheets("DataBase").Sheets("DataBase")// it does not work how I wrote above

**royUK** · May 26th 2020

I can't understand why you need to refer to the database sheet in the other workbook for this code.

Using a macro in multiple files and call another workbook

Participate now!

Share