Consulting

Results 1 to 4 of 4

Thread: First VBA project summary file please help

  1. #1

    Lightbulb First VBA project summary file please help

    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

  2. #2
    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 
    
    
    Formatting tags added by mark007
    ps. Welcome to this forum!

  3. #3
    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?

  4. #4
    Put this code in one of your source wbs....
    Private Sub GetWBFilepath() 
        MsgBox Application.ActiveWorkbook.FullName 
    End Sub 
    
    
    Formatting tags added by mark007
    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") 
    
    
    Formatting tags added by mark007
    to this..
    Set FilDir = fso.GetFile(" Place msgbox output here ") 
    
    
    Formatting tags added by mark007
    The problem is usually a file path thing, The macro enable shouldn't matter.
    Dave

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •