Doubt If This Is Possible - But Here Goes

[LearningVBA]

I have approximatly 300 workbooks sent to me each month, all of which are saved to a holding folder (one folder for each month of the year). Is there an automatic way in which the files can be opened up in turn and the cells B33:AZ 5033 can be copied and pasted back directly as values before closing and saving the workbook. The worksheet is called "Summary".

I've had a look at old posts but cannot see what I'm after.

 

[lozzablake]

Yes you can, but some questions first:

1) how do you know which folder you are going to access
2) where do you want the data pasted to?

this is an example of a macro that opens all the files in a particular folder and gets the date they were last modified. It would be easy to change this for what you want:

Sub GetMostRecentFile()
'macro to get all the dates of each file
'requires reference set to Microsoft Scripting runtime
    
    Dim FileSys As FileSystemObject
    Dim objFile As File
    Dim myFolder
    Dim i As Integer

        
    'set path for files - change for your folder
    Const myDir As String = "C:\Temp\"
    
    'set up filesys objects
    Set FileSys = New FileSystemObject
    Set myFolder = FileSys.GetFolder(myDir)
        
    
    'loop through each file and get date last modified. Output on WorkSheet
    i = 1
    For Each objFile In myFolder.Files
        With ThisWorkbook.Sheets("Sheet1")   'change for your sheet name
            .Cells(i, 1) = objFile.Name
            .Cells(i, 2) = objFile.DateLastModified
            i = i + 1
        End With
        
    Next objFile
    
            
    Set FileSys = Nothing
    Set myFolder = Nothing
End Sub