pulling lastrow data from multiple files

[gordsky]

Hi all,

Im trying to pull the data from the last row in Column E of multiple files within a folder. I was initially looking to do this via vba but as I dont want to "open" each workbook individually it was suggest that power query may be the way forward. I am new to power query but have managed to work out how to pull all the files and even managed to work out how to get rid of the columns etc i dont need. what I cant work out is how to get the data from just the last row (which would be variable) of each workbook.
Can someone give me some pointers please.

 

[johnnyL]

Does each workbook only have 1 sheet?

 

[gordsky]

Does each workbook only have 1 sheet?

Yes each work book has only one sheet

 

[johnnyL]

Any blank cells within the data in column E?

 

[gordsky]

Any blank cells within the data in column E?

No blank cells after row 22. Above row 22 is form set up. So basically looking to find last row after row 22 of each workbook

 

[johnnyL]

What location are you wanting to store the last cell value of column E of all the workbooks?

 

[gordsky]

What location are you wanting to store the last cell value of column E of all the workbooks?

Would be in column b of the new workbook

 

[johnnyL]

Another question on the sheet names, You said only 1 sheet in each workbook. Are all of them going to have the same sheet name? If so, what is the sheet name?

 

[gordsky]

Another question on the sheet names, You said only 1 sheet in each workbook. Are all of them going to have the same sheet name? If so, what is the sheet name?

Hi yes they all have the same sheet name and it will be "HrsWRK"

 

[johnnyL]

Ok, Give me a little bit so I can try to put together something for you.