Run a macro inside another macro

  • I am working in a project, what I have to do is let the user choose multiple excel files and then from every single file run a macro that take the information in some specific cells of the files and put that information in a new excel file, all the information that I need have to be in the same sheet of the new excel file.


    Right now I have two macros that works separated: one for choose and open the excel files and another one to run the macro that take the information, but I need to unified these two and make them just one code. If someone can help me I really appreciate it.


    First macro:

    Second Macro:


    Edited once, last by royUK: add code tags ().

  • Your first macro opens one or more workbooks and copies the used range of Sheet1 from each workbook to Sheet1 of the workbook containing the macros. The second macro processes some data using two worksheets, "ExtractedData" and "report stampabile". I can't see how the two macros are related. I think that it would be easier to help and test possible solutions if you could attach a copy of the file containing the macros and a copy of one or two files that are being opened. Explain in detail what you want to do referring to specific cells, rows, columns, sheets and workbooks using a few examples from your data (de-sensitized if necessary).

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Both macros are related because each of the open worbooks have to run the macro 2, I can attach two of the open files I have to analize.

    What I want to do is create a Macro that when I run it let me choose multiple files and for each file copy the information I want in a new excel file

  • Sheet1 in the opened files is "ExtractedData". Do you want to run macro 2 on each opened file before the "ExtractedData" sheet (Sheet1) is copied to the workbook containing the macro?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • What I want is that the big macro (macro 1 and 2 working together) creates the sheet call "extracted data" in a new excel file, for that first we have to choose the files that I need to open and then run the macro 2 in each of them and finally creates a sheet1 "ExtractedFData" in that new open file with the information that I have extracted with macro 2.

  • Working on it. I'll get back to you as soon as I can.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Code
    1. If Worksheets(Sheets(r)).Range("P5") = "OSS" Or "NC" Or "COM" Then

    In this line of code, r is set to start at 6 so Sheets(r) would be Sheets(6). Would this be the first "Rilievo" sheet? If so, the first "Rilievo" sheet would be Sheets(5) not Sheets(6). Does this make sense?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • OK. I'm doing some major revisions on the macros, so I'll get back to you as soon as I can.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Try:

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • It did not work, there is not any error, I just did not receive anything in Sheet1.


    Maybe is better eliminate the "extracted data sheet" in the files that I choose to open and work with this kind of files, and create the "Extracted Data" in the new excel file.

  • Place the macro in a new, blank workbook and run it from there. I tested it with the 2 files you posted and it worked exactly as your requested.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • The attached file shows the results of the macro using the 2 files you posted. Try it by clearing all the data in Sheet1 and then run the macro in Module1.

    Files

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • You are very welcome. :)

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hi Mumps, I am trying to add some information to the code you gave me and I am a little confuse, maybe you can help me this time too:

    1. I would like to add a column (in column A) calls "Laboratory" that extracts the information of cell P2 of the "report stampabile"

    2. To add a column (in column B) calls "Evaluation Date" that extracts the infomation of cell I3 from "report stampabile"

    3. To add a column (in column C) calls "Technical Functionary" that extract the informationof cell E18 from "Rilievo 1"

    4. To add a column (in column F) calls "Inspector 2" that extract the information of d14,d24,d34 etc from "report stampabile"


    I hope you can help me, thank you

  • In your source files you have many ranges with merged cells. You should not use merged cells because they almost always create problems for Excel macros. In the first macro I suggested, the merged cells were not causing any problems but in the revised macro below, they are causing problems. In all of your source files, you will have to unmerge the cells in columns P:S to the right of all occurrences of the text "Firma Direzione Tecnica" before you run the new macro. Make sure you unmerge the cells in the columns and the 2 rows.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.