VBA to retrieve last non-blank data in a column for multiple workbooks

[gliceljoy]

Hi folks,

I am kinda desperate for some help since I am having a hard time manually encoding 200+ excel templates into a masterfile. A colleague of mine suggested that I should try to run a VBA/Macro for it however I can't really understand the language that much.

The situation is I have to retrieve data for some specific fields. I have already found a code to retrieve data of multiple workbooks that were on a fixed cell on all template (example: a specific data that are same for all templates are found on worksheet 1 Cell C44). I found a code online that works all I have to do is to change the range and the path. My problem now is that there is a field wherein it is not fixed/not the same for all templates. The only similarity that they have is that they are all in worksheet 2 and is the last non-blank data of Column F.

I used codes from below link as reference in retrieving the fixed cells. There is also a code there for retrieving the last non-blank data however I think it is only good for 1 workbook/file and not for multiple workbooks/files within a folder/path like the fixed cells ones.

https://msdn.microsoft.com/en-us/li...ultipleWorkbooks_MergingRangefromAllWorkbooks

I really hope you guys could assist or suggest any alternatives to me. Anything would be much appreciated.

Thank you very much!

Sincerely,
Gjoy

P.S. if I failed to explain it clearly do comment it and I'll try to explain better I am not quite sure if it is clear for everyone

 

[gliceljoy]

Help

 

[bhos123]

Sub Macro2()

Dim fs As FileSearch
Dim i As Integer
Dim wbk As Workbook
Dim currentbook As Workbook
Dim lastrow As Long
Dim obj As Object

Set obj = CreateObject("Excel.Application")
Set currentbook = obj.Workbooks.Open("C:\Users\gbuday\Desktop\Oktatás\Excel\start.xlsx")

Set fs = Application.FileSearch

With fs
.LookIn = ThisWorkbook.Path
.Filename = "*.xls"
For i = 1 To .Execute()
Set wbk = Workbooks.Open(.FoundFiles(i))
lastrow = .Cells(.Rows.Count, "F").End(xlUp).Row
currentbook.Sheets("Sheet1").Cells(i, 1).Value = wbk
currentbook.Sheets("Sheet1").Cells(i, 2).Value = wbk.Sheets("worksheet 2").Cells(lastrow, 6).Value

wbk.Close
Next i
End With

End Sub


Change the currentworkbook .filename ..lookin Sheet1 as per your files. Hope this works

 

[gliceljoy]

Hi Bhos,

Thank you for helping me out.

I can't make the code run, I am having runtime error 1004 the document may be read only or encrypred.

I am not quite sure about encrypted but I know that the files on the folder are not read only.

Any suggestions how can I proceed?

Regards,
Gjoy

 

[bhos123]

Not sure man..I just gave a try. Not very expert in these areas.