VBA Script to pull certain cells from Marco enable workbook

[hiteshvarsani]

Hi,

We have around 300 macro enabled workbooks that are all in the same format and all have the same cells.

I would like to know the best way to extract all the answers from the Questionnaire and put them in a table in a master excel file.

I have tried the below but can only get a few cells as tables, to make things more interesting some of the spreadsheets have multiple tabs or each application, so again all in the same format:

Private Const sPath As String = "C:\Users\" 'CHANGE THIS TO YOUR DIRECTORY PATH

Sub LoopThroughFiles()

Dim sFile As String 'File Name
Dim sExt As String 'File extension you wish to open

sExt = "xlsm" 'Change this if extension is different

'loop through each file name and open it if the extension is correct
sFile = Dir(sPath)
Do Until sFile = ""
If Right(sFile, 4) = sExt Then GetInfo sFile
sFile = Dir
Loop


End Sub

Private Sub GetInfo(sFile As String)

Dim wbFrom As Workbook 'workbook to copy the data from
Dim iRow As Integer 'row number of next empty row

On Error GoTo errHandle

Application.EnableEvents = False
Application.ScreenUpdating = False

Set wbFrom = Workbooks.Open(sPath & sFile)


wbFrom.Sheets("App Item (1)").Range("C4:C10").Copy 'Copy A1:A10
iRow = Me.Range("A" & Rows.Count).End(xlUp).Row + 1 'Get an empty row in this workbook
Me.Range("A" & iRow).PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True 'paste copied cells
wbFrom.Close (False)

Application.EnableEvents = True
Application.ScreenUpdating = True
Set wbFrom = Nothing

Exit Sub
errHandle:
MsgBox Err.Description
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

any help would be much appreciated.

Thanks

Hitesh