Hi

I have created this macro in Excel 2010 which runs without error. However it doesn't produce the results it should and i am not sure why.

Option Explicit

Sub AverageGraph()
Dim i As String
Dim l As String
Dim wbCsv As Workbook
Dim wsMyCsvSheet As Worksheet
Dim lNextrow As Long
Dim strFile As String
Dim strFile1 As String
Dim strFile2 As String
Dim strFile3 As String
Dim strFile4 As String
Dim strFile5 As String
Dim strFldr As String

i = Range("B7").Value
l = Range("B8").Value

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Application.Workbooks.Open "C:\Documents and Settings\SeymourJ\Desktop\GraphingChartTemplate.xl sx"

Application.Workbooks.Open "C:\Documents and Settings\SeymourJ\Desktop\Actual_Participation_02_ 2011.xls"

Workbooks("Actual_Participation_02_2011.xls").Shee ts(1).Range("A2:A1000").Copy Destination:=Workbooks("GraphingChartTemplate.xlsx ").Sheets("Graphing").Range("B3")

Workbooks("Actual_Participation_02_2011.xls").Clos e

ActiveWorkbook.Sheets("Settings").Select

Range("B6").Value = i
Range("B7").Value = l

strFldr = "C:\Documents and Settings\SeymourJ\My Documents\Tasks"

strFile = Dir(strFldr & "Graphing_MTH_Actual_Curr_Year" & "*.CSV")
strFile1 = Dir(strFldr & "Graphing_MTH_Actual_Prev_Year" & "*.CSV")
strFile2 = Dir(strFldr & "Graphing_YTD_Actual_Curr_Year" & "*.CSV")
strFile3 = Dir(strFldr & "Graphing_YTD_Actual_Prev_Year" & "*.CSV")
strFile4 = Dir(strFldr & "Graphing_R12_Actual_Curr_Year" & "*.CSV")
strFile5 = Dir(strFldr & "Graphing_R12_Actual_Prev_Year" & "*.CSV")


Application.Calculation = xlCalculationManual

lNextrow = 2

Select Case ActiveCell.Value

Case 1
Do
Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile)
Set wsMyCsvSheet = wbCsv.Sheets(1)
With Workbooks("GraphingChartTemplate.xlsx").Sheets("MT H")
wsMyCsvSheet.Range("A2:M14").Copy
Workbooks("GraphingChartTemplate.xlsx").Sheets("MT H").Cells(lNextrow, 2).PasteSpecial

End With
lNextrow = lNextrow + 14

'close it
wbCsv.Close

'go to next file
strFile = Dir
Application.StatusBar = strFile
Loop Until Len(strFile) = 0

Case 2
Do
Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile1)
Set wsMyCsvSheet = wbCsv.Sheets(1)
With Workbooks("GraphingChartTemplate.xlsx").Sheets("MT HPrevious")
wsMyCsvSheet.Range("A2:M14").Copy
Workbooks("GraphingChartTemplate.xlsx").Sheets("MT HPrevious").Cells(lNextrow, 2).PasteSpecial

End With
lNextrow = lNextrow + 14

'close it
wbCsv.Close

'go to next file
strFile1 = Dir
Application.StatusBar = strFile1
Loop Until Len(strFile1) = 0

Case 3

Do
Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile2)
Set wsMyCsvSheet = wbCsv.Sheets(1)
With Workbooks("GraphingChartTemplate.xlsx").Sheets("YT D")
wsMyCsvSheet.Range("A2:M14").Copy
Workbooks("GraphingChartTemplate.xlsx").Sheets("YT D").Cells(lNextrow, 2).PasteSpecial

End With
lNextrow = lNextrow + 14

'close it
wbCsv.Close

'go to next file
strFile2 = Dir
Application.StatusBar = strFile2
Loop Until Len(strFile2) = 0

Case 4

Do
Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile3)
Set wsMyCsvSheet = wbCsv.Sheets(1)
With Workbooks("GraphingChartTemplate.xlsx").Sheets("YT DPrevious")
wsMyCsvSheet.Range("A2:M14").Copy
Workbooks("GraphingChartTemplate.xlsx").Sheets("YT DPrevious").Cells(lNextrow, 2).PasteSpecial

End With
lNextrow = lNextrow + 14

'close it
wbCsv.Close

'go to next file
strFile3 = Dir
Application.StatusBar = strFile3
Loop Until Len(strFile3) = 0

Case 5

Do
Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile4)
Set wsMyCsvSheet = wbCsv.Sheets(1)
With Workbooks("GraphingChartTemplate.xlsx").Sheets("R1 2")
wsMyCsvSheet.Range("A2:M14").Copy
Workbooks("GraphingChartTemplate.xlsx").Sheets("R1 2").Cells(lNextrow, 2).PasteSpecial

End With
lNextrow = lNextrow + 14

'close it
wbCsv.Close

'go to next file
strFile4 = Dir
Application.StatusBar = strFile4
Loop Until Len(strFile4) = 0

Case 6

Do
Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile5)
Set wsMyCsvSheet = wbCsv.Sheets(1)
With Workbooks("GraphingChartTemplate.xlsx").Sheets("R1 2Previous")
wsMyCsvSheet.Range("A2:M14").Copy
Workbooks("GraphingChartTemplate.xlsx").Sheets("R1 2Previous").Cells(lNextrow, 2).PasteSpecial

End With
lNextrow = lNextrow + 14

'close it
wbCsv.Close

'go to next file
strFile5 = Dir
Application.StatusBar = strFile5
Loop Until Len(strFile5) = 0

End Select

End Sub

It seems to either ignore the select case statement or it can not find the files within the loop. I am not very good with select case statements so i most likley have written it wrong.

Can anyone help explain why its not working?

Thank you

Jeskit