0

I have the following code that arranges data from "Sheet1" in multiple workbooks that are located (saved) in a folder named "TestVerificari". The following code (attached) works grate, but I have to run-it manually for every workbook. Is there a way to insert this code in a loop and run-it a single time for all workbooks saved in that folder? Thank you very much!

Sub VerificariCE()
'
' Macro1 Macro
'

'
    Rows("1:5").Select

    Selection.Delete Shift:=xlUp

    Columns("A:D").Select

    Selection.Delete Shift:=xlToLeft

    Range("A1:B50000").UnMerge

    Columns(2).Delete

    Selection.UnMerge

    Columns("B:B").Select

    Columns("C:D").Select

    Range("C50000").Activate

    Selection.Delete Shift:=xlToLeft

  '''''''''''''''''''''''''''''''''''
    Range("D1").Select

    Selection.Copy

    Range("G1").Select

    ActiveSheet.Paste

    Range("G2").Select

    ActiveCell.FormulaR1C1 = _
        "=TRIM(MID(SUBSTITUTE(RC[-2], """""""", REPT("" "", 999)), 2999, 999))"

    Range("G2").Select

    Selection.AutoFill Destination:=Range("G2:G50000")

    Range("G2:G50000").Select

    Columns("B:B").EntireColumn.AutoFit

    Columns("A:A").EntireColumn.AutoFit

    Columns("G:G").EntireColumn.AutoFit

    Selection.AutoFilter

    ActiveSheet.Range("$A$1:$M$50000").AutoFilter Field:=1, 

Criteria1:=Array( _

        "AutorizareExport", "CautarePF", "CautarePJ", 

"DescarcaRaportClient", _

        "GenereazaRaportClient", "InapoiLaCautare", "InrolareClientNouPF", _

        "IstoricDocumente", "Meniu_DocumentReport", 

"ModificareDateClientPF", _

        "ModificareDateClientPJ", "ModificarePFInitiativaBancii", 

"PaginaCautare", "="), _

    Operator:=xlFilterValues

    Rows("2:2").Select

    Range(Selection, Selection.End(xlDown)).Select

    Selection.Delete Shift:=xlUp


End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Fane
  • 27
  • 9
  • Yes it is possible but first you must remove all `.Activate` and `.Select` statements of your code according to: [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). • Then please [edit] your question and update the code. – Pᴇʜ Apr 01 '19 at 12:57
  • I managed to write this code using Record macro, I have something in mind but don't know how to write the code. Help needed! – Fane Apr 01 '19 at 13:29
  • Actually the Macro Recorder is a good tool to get an idea of the code. But it uses a lot `.Select` which will soon or later cause issues and makes your code slow and long. Please read the link I gave you and try to adopt this technique for your code. You will need to put in some efforts, as we can assist you but we cannot do all the work for you. Give it a try and then add to your question what you hava achieved. – Pᴇʜ Apr 01 '19 at 13:32
  • Fane, getting started in a coding language can be quite daunting, but the first step is to break the code down line by line and 'read' it. VBA reads each line from the top of the code one at a time. If you don't know what a line does you can Google it. (So for example Sub defines the start of your code, VarificationiCE() is the name. Lines that are greyed out with (') are comments. .etc.) Read through the linked thread with the reasons to avoid select as well to modify your code. Feel free to respond here and ask me questions as I have some free time today to answer. – Emily Alden Apr 01 '19 at 14:09
  • Hi Emily, sorry for my late reply, unfortunately now I have to finish another task, but starting next week I will focus on this code and reply to you. Thank you! – Fane Apr 03 '19 at 08:09

0 Answers0