transpose data extracted into row address obtained from vlookup at start off column and there after next column for each value in master sheet A:A

  • Anyone who can help with this, will be appreciated!!!


    I'm apologizing first, not very skilled at VBA, rather just excel skilled. My VBA knowledge is based from example codes googled from forums such as OzGrid :)

    If my code is a bit untidy, please I'm still learning...


    Regarding my project below...I have modified a code, that I've used before to extract data from multiple worksheets into a summary sheet, as once off register sheet. The code below is however intended to run on weekly basis.


    My "master" sheet is (in a separate workbook with all the employee's yearly info, each employee on different worksheet with numerous and lengthy formulas) called "Shift Hours Per Employee", I need to import weekly info from .xlsb files from specific folder. (This should be from the earliest date first working through to the newest date as a once-off, thereafter it will be done weekly and the date order should not really matter) - I haven't managed to do a code for the date order yet.


    The catch here is, I'm trying to do a loop where, with a vlookup formula, obtain the row address per employee code from the "Shift Hours Per Employee" sheet column A, transposing the extracted data from the .xlsb files into that row of the vlookup result (that matches the employee number) starting off at the first available column and thereafter proceeding to the next available column.


    Please help

  • Hi Roy


    I have uploaded the .xlsb file, but I keep on getting "The parameter "className" is missing or invalid" error with the Master Workbook(where the VBA code is in and a whole lot of excel formulas), any suggestions? I have tried zipping it, it gives the same error. (File size 11,188KB)


    WEEKLY TS 5 -11 FEB 2021.xlsb

  • I'm not sure what that error is.


    I've looked at the workbook that you have uploaded and I cannot see how VLOOKUP can work with that data.

  • Hi Roy, if I can explain a bit.


    I had to take out most of the info from the master sheet. The weekly .xlsb sheet contains info pulled from biometric system, which currently is not able to be incorporated into (let's call it for this purpose, "financial & payroll system") and therefor all the info from the .xlsb files have to be manually captured on financial system. This is a timeous effort, and as my formulas in the master sheet is extremely lengthy and a bit complex, I am looking for a way to have the user simply click button to "update" the weekly data (or even vba code to update when workbook is open, either way that would be the easiest with little or no effort and least time consuming.


    I need to, for the first run, import all the year to date data (or sit for a few weeks every night data capturing), and there after do a weekly import run, for weekly wages. The tricky part here is, I really don't want to change the code every time with a new appointment or when an employee resigns. The weekly biometrics will guaranteed not be exactly the same for every workweek of the month, let alone financial year, nor would the weekly shift allocations be the same for each individual. My master sheet has a vlookup formula already that contains specific worktime schedules with rates dependent on the work hours attached to it. That all needs to be considered when importing the weekly data.


    The biometrics info is set in a specific layout. The alternative here is to either (i think the term here is "hard code") for every individual a worksheet page and change the code every time there is a staff or wage change. And I have ventured down this avenue, believe me, it's just too much repetitive coding and with my limited skills and knowledge, being great at excel does not mean vba is easy! And I am good at solving puzzles and finding solutions, but this one has me stumped for weeks now.


    My idea is to run an import weekly where the master file do a lookup with employee code in the weekly .xlbs files in set folder and path, looping through the data in the weekly .xlsb files, matching employee code from the master file to the weekly data and when the vlookup result is found from the master file, to use that row and column address from the master file, to do a lookup for the weekly shift allocation as well as hours worked as recorded in the weekly (biometrics) .xlsb file, and transposing that "looked up data" in the row determined addresses in the master file (there the reason for finding start-off column as I have a master summary sheet with 365 days - yeah really - I just couldn't figure a better way to get the data to one place and then through to each individual's worksheets which also has extremely lengthy and complex formulas in)


    I really don't want to fiddle in the code if and when there is a staff or wage change, the data needs to be automatically adjusted to import accordingly, without me or any user making unnecessary (&mistakes) in code.


    I assume all is clear as mud now, but I hope you understand what I'm trying to achieve?




    The idea here is to in

  • My apology, my mistake, I wanted to say further, the final part of all this would be to export, info after all calculations done, a .csv file into our financial system.