Hi all,
I have some code in wb1 to open each wb, copy the ws then close the wb one by one within the folder I specify. It then runs some other code to manipulate the data.
[VBA] z = ActiveWorkbook.Sheets.Count
Set wb1 = ThisWorkbook
Set ff = CreateObject("Shell.Application"). _
BrowseForFolder(0, "Please select a folder", 0, "c:\\")
If Not ff Is Nothing Then
GetFolder = ff.Items.Item.Path
Else
GetFolder = vbNullString
End If
MyFolder = GetFolder
If MyFolder = vbNullString Then
MsgBox "No folder selected. Please select a folder containing the PHAST output.", vbCritical
Exit Sub
End If
With Application.FileSearch
.NewSearch
.LookIn = MyFolder
.FileType = msoFileTypeExcelWorkbooks
.Execute
For i = 1 To .FoundFiles.Count
Set wb2 = Workbooks.Open(.FoundFiles(i))
Sheets(1).Copy After:=Workbooks(wb1.Name).Sheets(z)
If ActiveSheet.Name = "DYNAMIC RESULTS" Then
Size = Cells(12, 1)
Parts = Cells(22, 2)
If Size = "Base Case" Then
Size = "2mm"
End If
ActiveSheet.Name = Size & "-" & Parts & "ppm"
End If
If ActiveSheet.Name = "MAXIMUM CONCENTRATION " Then
Size = Cells(12, 1)
Parts = Cells(20, 3)
ActiveSheet.Name = Size & "-" & Parts & "ppm"
End If
If ActiveSheet.Name = "DETAILED DISPERSION REPORT" Then
Size = Cells(11, 1)
If Size = "Base Case" Then
Size = "2mm"
End If
ActiveSheet.Name = Size
End If
Application.CutCopyMode = False
Windows(wb2.Name).Close
z = z + 1
Next i
End With
[/VBA]
I would like to make wb1 a template which gets called from another workbook (eg mainwb). I would like mainwb to get the user to select a folder which will contain a number of subfolders (eg sub1,sub2,etc). In each subfolder there is 25 workbooks. Upon selecting the main folder I want the macro to open the template wb1, send the subfolder name to that template so that it then runs the original code it has. It would be good if it would save wb1 in the main folder but named using the subfolder the information came from - however I wouldn't mind if it prompted the user to save the file manually. Then move onto the next subfolder repeating the process for each of the subfolders.
Does this make sense? and if so would it be possible?
Regards
Craig