First VBA project summary file please help

[LRA546]

Hey Guys,

This is my first time using VBA so I'd be really grateful for any help/advice. I've done a few online tutorials so have learnt the basics but this is my first project as such.

I'm looking to create a code which is able to copy data out of one file and place into a summary folder.

For example, I have files such as 1056, 1057 through to 1186. Within these workbooks, I need to copy data out of a sheet which the name is velocity and the data is in cells C617. It would great to also put the file name above the data. The data needs copied into a new workbook which is called summary.

Any help to get me up and running with this project would be greatly appreciated,

Thank you

[Dave]

Here's some code to get U started. U will need to adjust your file path to suit. The info will come from the Velocity sheet C1:C17 and be placed in the summary workbook sheet1 "A1" (if the code is placed in sheet code and run from the summary workbook.) If you need to run code from a separate wb then that's different. This will get U started. If this works, it's not that hard to put your file names into an array then loop though the array to get the data from all files placed into your summary wb. U will need to specify where all this data goes in the summary wb. HTH. Dave

Sub test()
Dim FilDir As Object, fso As Object
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set fso = CreateObject("scripting.filesystemobject")
'***change File path to your file
Set FilDir = fso.GetFile(ThisWorkbook.Path & "\yourfoldername\1056.xlsm")
workbooks.Open filename:=FilDir
'***change range to suit
workbooks(FilDir.Name).Sheets("Velocity").Range("C6:C17").Copy _
    Destination:=ThisWorkbook.Sheets("Sheet1").Range("A1")
Application.CutCopyMode = False
workbooks(FilDir.Name).Close SaveChanges:=False
Set FilDir = Nothing
Set fso = Nothing
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

ps. Welcome to this forum!

[LRA546]

Hi Dave, thanks so much for getting back to me!

I seem to be having a problem the file path location, the workbook I'm trying to extract data from is a macro-enabled file would this be an issue?

[Dave]

Put this code in one of your source wbs....

Private Sub GetWBFilepath()
MsgBox Application.ActiveWorkbook.FullName
End Sub

Replace this part of the posted code with the message box contents as shown below.....
change this...

Set FilDir = fso.GetFile(ThisWorkbook.Path & "\yourfoldername\1056.xlsm")

to this..

Set FilDir = fso.GetFile(" Place msgbox output here ")

The problem is usually a file path thing, The macro enable shouldn't matter.
Dave