Posts by Paulandreaoc

All the information from the report to the template, both files have the same information but in the "report" is disorganized because is exacly how I obtained from the internet and it has to be with the structure of the template.

Hi guys

I need to create a macro that allows me to copy the information from an Excel file called "Report" to an Excel file called "Template". The idea is that the macro copies the information contained in the general data and preliminar report (from the Report) in the first sheet of the "Template" following the template created (the copied information has to be in columns A to L because from N the cells have already formulas that change automatically), and copying the information below the title comments (from the Report) in the second sheet of the "template" file.

I have no idea where to start. I uploaded the excel files.

If someone can help me I would be really grateful

Thank you in advance

You're right, I have done it without using VBA, just using Find and Replace options

Thank you.

Hi guys,

I have this column of information in a worksheet called "Report" in the column "J" I would like to develop a macro to change the writing of the column content for example if is written UNI_EN_ISO_IEC_17025_2005 change it for UNI EN ISO/IEC 17025:2005 and if is written UNI_EN_ISO_IEC_17025_2018 change it for UNI EN ISO/IEC 17025:200518, but I am not able to do it.

I am struggling because this column "J" does not have always the same leght so I don't know how to difine the range.

Thank you in advance

this is the column:

Hi guys, I have this line code to let an user choose and open multiples files on excel, but if the user don't choose anything or close the window the code give me an error en el next line to that one, someone know how can I do to delete that error?

Thank you in advance

File_Name = Application.GetOpenFilename("Excel Files (*.xl*),*.xl*", , "Select Excel Files To Consolidate", , True)

Hi guys, I would like to know if it is possible to graph two Pivot Tables in one Pivot Chart? If so, how can I do it?

Thank you in advance.

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

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")

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

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

The DataBase is always have been in a different workbook

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

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

it's happening something unusual, in B4 where have to be the Code of the lab, the macro just give me the code when the cell B4 is in text format in other cases B4 does not put the code.

Do you know what can I do in this case?

Here is is

It works for the inspectors, but not for the laboratories, because I have like 200 laboratories I have to replace for the code

As I said you before the inspectors names in sheet Anagrafica audit can be in cells a12:a17, a12:25 etc, there is not a specific range because in every file can vary, for that reason when I made the code I never specify the range, for example in this lines of code I made that anywhere where is the name "Pier Giorgio Spazzini" replace it for the number 6.

Worksheets("Anagrafica audit").Cells.replace What:="Pier Giorgio SPAZZINI", Replacement:="6"
Worksheets("report stampabile").Cells.replace What:="Pier Giorgio SPAZZINI", Replacement:="6"

Yeah, the code clean that part because is no useful to me, it does not matter if in that part there are inspectors names or not bacause is not useful to me that part, for that reason I removed it.

I mean they can be in cells a21:a3o like in the example I gave you, but as this cells are after the header I don't need it

they can be place in any cell starting from A12 and finishing before the header "PERSONE CONVOLTI DEL CAB", you can see in my code right before the header I remove all, because I don't need that part

The names and laboratories I have wrote in the code are some of the DataBase, this was the code I made before having the complete Database with all the names and laboratories I need to codified, The idea is replace all the names and laboratories if they exists.