retrieve data in column from many excel sheets/files

[Flicker]

Hello,

I have many excel files. Each file have 6-7 sheets. Those are template excel file which the format of each column is similar.

I want to retrieve only some specific cell from some sheets from those many excel file.

Could somebody point me an idea how I could start that? Currently, I use manual process --> Open an excel file, click a sheet and copy column to another excel file and select other sheet, copy again. Close first excel file and so on..

I know some basic excel formular but I cannot use VBA / Macro yet. Maybe this is a good time for me to learn some.

Regards,
Flicker

 

[Sandeep Warrier]

This code will loop through all Excel files in a particular folder. It will open an Excel file, copy the data in Column A, go to your main file, paste it in column A. You can modify it to suit your purpose. You need to use the code from your main file.

Sub LoopThroughFolder()
 
    Dim i As Long
    Dim wbResults As Workbook
    Dim wbCodeBook As Workbook
    Dim stThisBookName As String
 
    On Error Resume Next
    i = 1
    stThisBookName = ""
    stWorkBookName = ""
    Set wbCodeBook = ThisWorkbook
    stThisBookName = wbCodeBook.Name
    With Application.FileSearch
 
        .NewSearch
        .LookIn = "C:\Documents and Settings\sandeepw\Desktop\Excel Sheets"
        .FileType = msoFileTypeExcelWorkbooks
 
        If .Execute > 0 Then
 
            For i = 1 To .FoundFiles.Count
 
                Set wbResults = Workbooks.Open(.FoundFiles(i))
                Range("A1", Range("A65536").End(xlUp)).Select
                Selection.Copy
                Windows(stThisBookName).Activate
                Range("A1").Select
                If ActiveCell.Value = "" Then
 
                    ActiveSheet.Paste
 
                Else
 
                    If ActiveCell.Offset(1, 0).Value = "" Then
 
                        ActiveCell.Offset(1, 0).Select
                        ActiveSheet.Paste
 
                    Else
 
                        ActiveCell.End(xlDown).Select
                        ActiveCell.Offset(1, 0).Select
                        ActiveSheet.Paste
 
                    End If
 
                End If
 
                wbResults.Close SaveChanges:=True
 
            Next i
 
        End If
 
    End With
 
    On Error GoTo 0
 
End Sub