Forum Discussion

Rita2020's avatar
Rita2020
Copper Contributor
Mar 10, 2020

VBA code for specific import

Hello Everyone:

I am painfully new to VBA and am trying to figure out how to import only specific items from an attendance template, into a new master that contains only the employee name and scheduled hours. The actual has many more columns, for the year. I don't have the original to show you, but did a very small sample:

The employee name is offset by one from scheduled hours and I only want those two rows. Each supervisor has their own template, with 10 to 15 employees, and there are at least 25 supervisors. I tried a query from a folder and managed to combine the files, but the data clean up is so arduous and I know there is a better way. The original template is protected, so I can't go in and just identify the rows and columns I want to copy. Can you please help me write a macro?

Thank you,

Rita

Macros and VBA

3 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Mar 10, 2020

    Rita2020 You mention that you "managed to combine all the files". May I assume that you thus have one large file (that you can change) with all employees' attendance in the layout that you gave in your example?

    If so, and when your version of Excel recognises the FILTER function, there is an easy way to achieve this, without VBA.

    On your combined file, move the content of cell A1 over to B1 and insert one cell in A1, shifting all cells in column A down one row. This will align the name with the scheduled hours. Then, in a new sheet you can created a few header rows, similar to above and enter the FILTER formula as shown. Sit back and see what happens. Have attached a file replicating your example. Now, let me know if this works for you or not.

    • Rita2020's avatar
      Rita2020
      Copper Contributor
      Mar 10, 2020

      Riny_van_Eekelen That absolutely will work and I will try today. So I can get rid of unwanted data in columns in transform and edit, and probably most of the null rows. And will move the name cells down by one once I have loaded the combined data.

      If an employee's hours change, I want to be able to update my new table. Is it possible to refresh without my new sheet losing all of it's formatting?

      Thank you for your help.

Resources