markes1
09-15-2009, 11:06 AM
Hello, I need to copy a specific sheet (CONSTANT gPA) from multiple workbooks in multiple subdirectories and paste (special) that sheet to the workbook where my code is located (which is at the root, C:\2010 Budget\).
The code below walks through these subdirectories well enough, but it does not "Open" any of the workbooks. Here is the line/code for opening the workbook:
Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
To give more context here is a larger code snippet:
For i = 1 To 50
If FileOrDirExists("C:\2010 BUDGET\" & stateHold(i) & Application.PathSeparator) Then
With Application.FileSearch
.NewSearch
.LookIn = "C:\2010 BUDGET\" & stateHold(i) & Application.PathSeparator
.FileType = msoFileTypeExcelWorkbooks
.Filename = "*.xls"
If .Execute() > 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count ' Loop through all.
Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
If IsNumeric(Mid(wbResults.Name, 3, 4)) Then
For Each wks In Worksheets
Select Case wks.Name
Case gPA
wks.Unprotect
wks.Copy
Workbooks("2010 BUDGET TEMPLATE-ACCT-TEST.xls").Activate
Sheets.Add.Name = wbResults.Name & " - GPA"
Sheets(wbResults.Name & " - GPA").Select
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End Select
Next wks
End If
Workbooks(wbResults.Name).Activate
wbResults.Close SaveChanges:=True
Next lCount
End If
End With
End If
Next i
I am using Excel '07 on Vista, and I have tried to run this macro as both a '07 and '03 xls. Where do you think I am going wrong here?
Thanks in advance.
The code below walks through these subdirectories well enough, but it does not "Open" any of the workbooks. Here is the line/code for opening the workbook:
Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
To give more context here is a larger code snippet:
For i = 1 To 50
If FileOrDirExists("C:\2010 BUDGET\" & stateHold(i) & Application.PathSeparator) Then
With Application.FileSearch
.NewSearch
.LookIn = "C:\2010 BUDGET\" & stateHold(i) & Application.PathSeparator
.FileType = msoFileTypeExcelWorkbooks
.Filename = "*.xls"
If .Execute() > 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count ' Loop through all.
Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
If IsNumeric(Mid(wbResults.Name, 3, 4)) Then
For Each wks In Worksheets
Select Case wks.Name
Case gPA
wks.Unprotect
wks.Copy
Workbooks("2010 BUDGET TEMPLATE-ACCT-TEST.xls").Activate
Sheets.Add.Name = wbResults.Name & " - GPA"
Sheets(wbResults.Name & " - GPA").Select
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End Select
Next wks
End If
Workbooks(wbResults.Name).Activate
wbResults.Close SaveChanges:=True
Next lCount
End If
End With
End If
Next i
I am using Excel '07 on Vista, and I have tried to run this macro as both a '07 and '03 xls. Where do you think I am going wrong here?
Thanks in advance.