[HELP!] Collating specific range of data from different workbooks.

[cheryl111]

Hi, I'm new here. Not new to excel, but totally a newbie for macro and VBA.

I need to collate the data from different files and it I heard that VBA will do the job, however, I am clueless about the codes.

we have a standard template for all the files. Within each file, it contains 50 worksheets of data and one "Main" sheet which returns and name & tab number of each data for easy navigation.

Problem now is the fact that one file can only contain 50 sets of data, one manager may have more than 3 files. And for them to find one data, they have to open 3 files and search one by one.

We hope that we can collate in a new workbook where it gives the name of the data and also a clickable link to the destination of that data.

What we need is just getting the data from the sheet "Main" range A1:B51 from the different files.

So I did something like this and apparently it doesn't make any sense:

Sub Firstattempt()
    Dim FileNames As Variant
'   Get the filenames
    FileNames = Application.GetOpenFilename(, , , , True)
    
    F = Dir(FileNames)
    
    If Array(FileNames) Then
    Application.Workbooks(F).Worksheets("Main").Range("A1:B51").Select
    Selection.Copy
    ActiveSheet.Paste
    End If
    
End Sub

Can someone help me with the codes?
Thank You.

 

[Tinbendr]

See if this article is what you're looking for.

If not, look at the author's website for more options.