Run a macro on several Workbooks

[Amy2011]

Hi,

I am trying to run a macro on several different workbooks that don't have consistent filenames.

I have a master folder

H:\SubjectData\

which then has a subject folder
AO223\

which then contains 4 spreadsheets.

I have been able to do this before by typing in the path and filename into the spreadsheet, but I have 25 subject folders (so25*4 spreadsheets) in total that i want to run the macro over so trying to avoid having to type in each workbook filename!

Any help is much appreciated (trying to do things way above my VBA skill level!!)

 

[Amy2011]

Sorry beaucaire... to be honest when I first posted I thought what I was doing was going to be simpler than what it was!

I have one folder G:\Subject Data
in this folder is 23 seperate subject folders... all of which contain 4 workbooks.

Each of the workbooks has 1 sheet of data (the workbooks and sheet names are all varied)

For each of the 4*23 workbooks i want to copy a range of data that is already existing... and paste it into a new worksheet (which i need to create).
I should clarify here that I am wanting to do this within one spreadsheet.. so copy the existing data, create a second worksheet and paste the data... then repeat for all spreadsheet within that subject data master folder
Hopefully that makes a bit more sense - let me know if it doesn't... in the meantime ill try fidling and using the guys suggestions above!

 

[jbeaucaire]

Understood. So, you were going to list the Subject Folders on Sheet1 in column A, that's what the original macro utilizes.

Then, for each subject folder listed in column A, open all the files found in that constructed folder, copy the same range from each sheet to a new "compilation workbook" where each copied range goes onto a new sheet of its own.

Yes?

 

[Amy2011]

Understood. So, you were going to list the Subject Folders on Sheet1 in column A, that's what the original macro utilizes.
Yep! Have done this.

Then, for each subject folder listed in column A, open all the files found in that constructed folder, (Yep!)

copy the same range from each sheet to a new "compilation workbook" where each copied range goes onto a new sheet of its own.
For now, I would just like to create a new sheet within each workbook that I can paste the copied range (from that specific workbook).
Yes?

 

[jbeaucaire]

Um, you're copying the same range of cells from the master workbook (where this macro is running from) and putting it onto a new sheet in every workbook opened?

OK, so, what is the name of the sheet in the master workbook, the range of cells?

Also, does the "added sheet" in the found files need to be named something specific?

What would really make it a cinch is if you turned on your macro recorder, let it record you going the the sheet, selecting the cells, opening a file, adding a sheet, pasting in the data, naming the sheet and saving the file. The code generated would answer most of my questions.

 

[Amy2011]

Okay I don't think i'm making myself very clear (either that or im misunderstanding what you're saying!! if so - sorry!!!)

If i was to do this one sheet at a time..
I would open the workbook, select the range of cells I require
create a second worksheet, paste the selected cells
save the workbook and close.

(This is the code i recorded for that as you asked )
Sub running()

'
' running Macro
'
'
    Range("A2:N2").Select
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ChDir "G:\Running\AC017"
    ActiveWorkbook.SaveAs Filename:="G:\Running\AC017\Results_Bare 10.xlsm", _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub

BUT i want to be able to do this for multiple workbooks where the sheet names are not consistent!

 

[jbeaucaire]

when a workbook is opened, are there multiple sheets? How do we choose a sheet to copy from?

The newly added sheet doesn't require a specific name, just whatever gets added is fine?

 

[Amy2011]

When the workbook is opened there is only 1 sheet

And the newly added sheet should be 'NormData'

 

[jbeaucaire]

So, I assume this is a one-off thing, and if the NormData sheet exists in any of these workbooks then you wouldn't want the code to "run again", so this checks for that. As long as NormData doesn't exist, it creates it and copied the data from the one sheet onto it.

Option Explicit

Sub ProcessAllFilesInSubjectFolders()
Dim MyPath As String, fName As String
Dim wbOPEN As Workbook
Dim MySubjects As Range, Subj As Range

MyPath = "H:\SubjectData\"
Set MySubjects = ThisWorkbook.Sheets("Sheet1") _
            .Range("A:A").SpecialCells(xlConstants)

For Each Subj In MySubjects
    fName = Dir(MyPath & Subj & "\*.xls")   'get first filename for this folder
    
    Do While Len(fName) > 0                 'process each file one at a time
        
        Set wbOPEN = Workbooks.Open(MyPath & Subj & fName)
        With wbOPEN
            'check to see if the NormData sheet has already been created
            If Not Evaluate("ISREF(NormData!A1)") Then
                .Sheets(1).Range("A2:N2").Copy
                .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "NormData"
                .Sheets("NormData").Range("A1").PasteSpecial xlPasteAll
                .Close True     'closes and saves
            Else
                .Close False    'close, no change since sheet already existed
            End If
        End With
        
        fName = Dir         'get next filename
    Loop
Next Subj

End Sub