Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Mar 2015
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Run macro in another worksheet (Advanced )

    Hey guys,

    I asked a similar question on this website, but no response --> hopefully here someone is able to help me and I hope i did not break any forum rule!

    I have this idea in my head, and I think that Excel could solve this for me, easily.


    So I am working on this reporting tool and now I want make per person a sheet export.

    The good news is that we already have these sheets per person, and the idea is to add another tab into that file via macro.
    So my idea is to have this sheet (Mainsheet) where I define values in a list and the macro should go through them and based on the value it should do ‘things’. I put on this Mainsheet, the path where the file is located, the exact filename, the value that the new tab should have, the data that it needs to be copied from Mainsheet tab and the macro that it should run. The final step is than to run a macro that which is located in the opened file (John) and close the file and go to next...


    So basically the macro should open another workbook, create new tab and rename it, copy values and finally run a macro that is located in that open workbook.
    In the attachment you will find a zip file that includes the mainsheet with the run report button and the three other reports that the button uses to copy/transfer the data.
    Attached Files Attached Files

  2. #2
    New Lounger
    Join Date
    Mar 2015
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This is how far I came when I modified the recorded macro:

    Code:
    Sub RunReport() 
         
         '
        Windows("ReportingTool.xlsx").Activate 
        Range("D2").Select 
        Selection.Copy 
         
        Workbooks.Open Filename:="C:\ExcelReports\John.xltm" 
         'Windows("John").Activate
        Sheets.Add After:=Sheets(Sheets.Count) 
        Sheets("Sheet2").Select 
        Sheets("Sheet2").Name = "jan-15" 
        Application.CutCopyMode = False 
        ActiveCell.FormulaR1C1 = "" 
         
        Windows("ReportingTool.xlsx").Activate 
        Sheets("John").Select 
        Range("A1:G15").Select 
        Selection.Copy 
         'Windows("John").Activate
        Windows("John.xlsx").Activate 
        ActiveSheet.Paste 
         
        Sheets("Sheet1").Select 
        Windows("ReportingTool.xlsx").Activate 
        Windows("John").Activate 
        Windows("ReportingTool.xlsx").Activate 
         
         
    End Sub
    Last edited by RetiredGeek; 2015-03-25 at 12:08. Reason: Changed Quote tags to Code tags.

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Oddy

    ..welcome to the Lounge!
    Will have a look at your request.

    zeddy

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Oddy

    Unzip the attached.
    It contains [ReportingTool-rz1.xlsm] together with three sample files.
    The sample files [Alex.xlsb], [John.xlsb] and [Peter.xlsb] are NOT template .xltm files or .xlsx files.
    To have macros in them, they can be .xlsm or .xlsb.
    I chose .xlsb because I prefer that format (smaller file size, less network traffic, faster loading).

    The example processing file [ReportingTool-rz1.xlsm]
    ..does not close files after processing,
    ..does not check that files you list for processing actually exist in the folder you specify
    ..does not check if any of the specified files are already open or not
    ..that the specified files actually contain the macros you list
    etc etc

    ..but it does do what you asked for.
    Put the files in c:\ExcelReports (or adjust accordingly)

    zeddy
    Attached Files Attached Files

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Oddy,

    Here's another take on the code with checks for missing files and if the Sheet tab already exists before it is created.

    Code:
    Option Explicit
    
    Sub RunReport()
         
       Dim wkbBase    As Workbook
       Dim shtMain    As Worksheet
       Dim wkbLoaded  As Workbook
       Dim sht        As Worksheet
       Dim lCurRow    As Long
       Dim zFName     As String
       Dim zMacro     As String
       Dim zNewSht    As String
       Dim zFType     As String  '*** Set to either .xlsb or .xlsm ***
       Dim bError     As Boolean
    
       
    '*** Setup ***
       Set wkbBase = ActiveWorkbook
       Set shtMain = Sheets("MainSheet")
       shtMain.Activate
       lCurRow = 2
       zFType = ".xlsm"
       Application.ScreenUpdating = False
       
    '**** Start Main Processing Loop ***
    
       Do
          zFName = Cells(lCurRow, 2).Value & _
                   Cells(lCurRow, 3).Value & _
                   zFType
                   
          bError = False
          On Error GoTo ErrFileNotFound
          Set wkbLoaded = Workbooks.Open(Filename:=zFName)
          On Error GoTo 0  '*** Clear Error Trap ***
          
          If Not bError Then
          
            zNewSht = Format(shtMain.Cells(lCurRow, 4).Value, "mmm-yy")
          
            For Each sht In wkbLoaded.Sheets
               If sht.Name = zNewSht Then
                 bError = True
                 MsgBox "Sheet: " & zNewSht & " already exists in" & vbCrLf & _
                        "Workbook: " & wkbLoaded.Name, _
                        vbOKOnly + vbCritical, _
                        "Error: Duplicate Sheet Name Detected:"
                 Exit For
               End If
            Next sht
          
          End If
          
          If Not bError Then
          
            Sheets.Add After:=Sheets(Sheets.Count)
            ActiveSheet.Name = zNewSht
            ActiveCell.FormulaR1C1 = ""
         
            wkbBase.Activate
            Sheets(Cells(lCurRow, 3).Value).Select
            Range("A1:G15").Select
            Selection.Copy
        
        
            wkbLoaded.Activate
            ActiveSheet.Paste
            Application.CutCopyMode = False
            Sheets("Sheet1").Select
            zMacro = shtMain.Cells(lCurRow, 3).Value & zFType & "!" & _
                     shtMain.Cells(lCurRow, 5).Value
            Application.Run zMacro
            DoEvents
          
          End If  'Not bShtError
          
          wkbBase.Activate
          [a1].Select   'Clear Selection
          shtMain.Activate
          
          With wkbLoaded
              If Not (wkbLoaded Is Nothing) Then
                Application.DisplayAlerts = False
                .Save
                .Close
                Application.DisplayAlerts = True
              End If
          End With
          
          Set wkbLoaded = Nothing    'Clear file object
          
          lCurRow = lCurRow + 1  'Move to next file
          
        Loop While Cells(lCurRow, 1).Value <> ""
        
    '*** End Main Pocessing Loop ***
    
        Application.ScreenUpdating = True
        
        GoTo Exit_RunReport
        
    ErrFileNotFound:
    
       bError = True
       MsgBox "Workbook: " & zFName & vbCrLf & _
              "does not exist!", _
              vbOKOnly + vbCritical, _
                        "Error: Invalid file specification:"
      
       Resume Next
    
    Exit_RunReport:
        
    End Sub
    Test Files: RGVersion.zip
    Note: You'll have to change the file paths in col B as I changed them for my test environment.

    HTH
    Last edited by RetiredGeek; 2015-03-25 at 15:53.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    where's oddy?

    zeddy

  7. #7
    New Lounger
    Join Date
    Mar 2015
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by zeddy View Post
    where's oddy?

    zeddy
    Hey Guys,

    Many thanks guys for the work! I’m really amazed that there are people that have a big heart and willing to give a men a helping hand! Thanks, I really appreciate it.

    While I was coding myself in the meantime (recording macros) and trying other example codes, I found a “better” way to solve my solution, at least I think. I am not asking you guys to code for me, but asking if my new solution is possible in excel and is it efficient. I will try myself first and if I cannot solve it or code inefficient code, than your help is much appreciated of course

    So I have this ONE workbook called “ReportingTool” and it has many tabs. These tabs are already there and filled.

    The first tab consist of all the data that needs to be distributed over the other tabs in the ReportingTool. The other tabs are tabs that are populated by the first tab, based on the name of the person. These tabs get the name of the person, month and year.

    There is also a tab that include a template and this should also be copied to all the other tabs with names.

    My idea was to create 2 buttons. The first button should do the following steps:

    1. Create tabs in the current workbook based on the names of the first sheet
    2. Copy the data from the first sheet to all the other tabs
    3. Copy from the template sheet a selection to all the just created tabs
    4. Perform a macro on all the just created tabs


    Once this is finished the person in charge can review the tabs and check if it’s correct and than press the second button.

    The second button should do the following steps:

    1. Export all the created tabs to a predefined folder
    2. Reset all the tabs by deleting it or empty the data of it.


    My question is this possible and are my steps logic and efficient?
    Last edited by oddy73; 2015-03-26 at 12:12.

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi oddy

    So, to put your example into ways I can understand...

    You have a Master sheet which contains a list of targets, and a list of assassins.
    You want to create a tab sheet for each assassin, with their hit list for each month?

    No, sorry, I meant to say you have a list of courses, and a list of teachers.
    You want to create a tab sheet for each teacher with their monthly assigments and classes?

    Or is it a workshop, with a Master list of trucks booked in for service, and you want the job list for each mechanic for the next month?

    Actually, I think what you are proposing sounds OK, and it might be logical, and it might be efficient.
    But we would know better if we had some 'anonymised' samples.

    zeddy

  9. #9
    New Lounger
    Join Date
    Mar 2015
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Zeddy,

    The reason why I was so mysterious was to not overload you guys with unimportant information.

    Well basically it all comes down to the following:

    I automate a process which downloads data from two different systems, one from the absence and holiday system and one from the project administration system. The idea behind this is to combined these two data and distribute per person in a timesheet per month.

    So I have this list of employees and one employee can have multiply instances (holidays/sicknesses).

    And I have this template (tab template) which consist of monthly calendar that can be populated with the data via a macro. This macro needs to be run for each tab individually (per person). It’s a simple macro, it copies data to the calendar.

    The button at the first sheet should perform the following action:
    • copy data from the first sheet via a loop and export it per person (+the month)
    • run a macro on the created sheet
    • this should continue till the loop is finished
    • and then export each created sheet to a folder via dialog box, in one instance all the tabs


    So currently I am working on this code and try to go further, see attachment, but no success
    Attached Files Attached Files

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi oddy,

    Will have a look at this and report back.

    zeddy

  11. #11
    New Lounger
    Join Date
    Mar 2015
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    Hereby a new version of my reporting tool. I have modified the source data in the tabsheet "merge".
    As you can see in the attachment i have two tabs, one named merge and one cal_template.

    In the merge tab on the left you see two tables, both of them are downloaded from other systems. Via the merge button once merge them. For the reporting tool We have to use the merged table that start from column L and further.
    (I have some issue with the color coded dates, but its not a show stopper)


    The "Run report" button should perform the following actions:
    1 - Copy via loop a range of data that belong to a unique name, which is in the first row we find - Rob -
    This means create a new sheet, name it Rob and copy the data M2:R3 to the new sheet.

    2 - The second step should copy the cal_template sheet into the Rob sheet

    3 - The third step is to run a particular macro on this Rob sheet

    4 - The final step is that the rob sheet should be exported to a folder (how? that is not important right now)

    I tried many codes to loop through the names and copy the content, but duo to duplicate names in column L, the many different codes give me error that a sheet called Rob already exist.

    I hope I am clear to what I want to achieve.

    Thanks in advance!
    Attached Files Attached Files

  12. #12
    New Lounger
    Join Date
    Mar 2015
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hey guys,

    I found my solution. Many thanks for all your inputs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •