Select Case statement skipping the cases

  • Thread starter Thread starter Legacy 93538
  • Start date Start date
L

Legacy 93538

Guest
Hi

I am using Excel 2010 and I am having trouble with a select case loop statement macro i have writen it works and runs without errors except its either skipping the select case statement or its not find the files.

Rich (BB code): 
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

Does anyone know why it is either skipping the select case statement or its not find the files because i am not sure?

Thanks

Jessicaseymour
 
Sort by date Sort by votes
Ok i just ran the new code you gave me and it works with out error and it loops through all the files and its copying the data however its not pasting the data into any sheet.

I stepped through it and when it gets to the Select case it passes through it without performing the task within the Case statement.
 
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Nearly there then :) From what I can find it would appear that you can't use wildcards in Case, but you can with If Like, so maybe this one will work.

Code: 
Sub RunCodeOnAllXLSFiles()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
Dim Nrow As Long, Nrow1 As Long, Nrow2 As Long, Nrow3 As Long, Nrow4 As Long, Nrow5 As Long
Dim strF As String, strFldr As String, strFile As String, strFile1 As String, strFile2 As String
Dim strFile3 As String, strFile4 As String, strFile5 As String, i As String, l As String
Dim wbResults As Workbook, wbGCT As Workbook
        
        strFldr = "C:\Documents and Settings\SeymourJ\My Documents\Tasks"
        strFile = "*Graphing_MTH_Actual_Curr_Year*.CSV"
        strFile1 = "*Graphing_MTH_Actual_Prev_Year*.CSV"
        strFile2 = "*Graphing_YTD_Actual_Curr_Year*.CSV"
        strFile3 = "*Graphing_YTD_Actual_Prev_Year*.CSV"
        strFile4 = "*Graphing_R12_Actual_Curr_Year*.CSV"
        strFile5 = "*Graphing_R12_Actual_Prev_Year*.CSV"
Nrow = 2: Nrow1 = 2: Nrow2 = 2: Nrow3 = 2: Nrow4 = 2: Nrow5 = 2
    i = Range("B7").Value: l = Range("B8").Value
        Set wbGCT = Workbooks.Open("C:\Documents and Settings\SeymourJ\Desktop\GraphingChartTemplate.xlsx")
        Application.Workbooks.Open "C:\Documents and Settings\SeymourJ\Desktop\Actual_Participation_02_2011.xls"
        Workbooks("Actual_Participation_02_2011.xls").Sheets(1).Range("A2:A1000").Copy Destination:=Workbooks("GraphingChartTemplate.xlsx").Sheets("Graphing").Range("B3")
        Workbooks("Actual_Participation_02_2011.xls").Close
ActiveWorkbook.Sheets("Settings").Select
    Range("B6").Value = i: Range("B7").Value = l
ChDir strFldr
strF = Dir("Graphing_*_Actual_*_Year*.csv")
Do While strF <> ""
Set wbResults = Workbooks.Open(strFldr & "\" & strF)
        wbResults.Sheets(1).Range("A2:M14").Copy
If wbResults.Name Like strFile Then
        wbGCT.Sheets("MT H").Cells(Nrow, 2).PasteSpecial
        Nrow = Nrow + 14
    ElseIf wbResults.Name Like strFile1 Then
        wbGCT.Sheets("MT HPrevious").Cells(Nrow1, 2).PasteSpecial
        Nrow1 = Nrow1 + 14
    ElseIf wbResults.Name Like strFile2 Then
        wbGCT.Sheets("YT D").Cells(Nrow2, 2).PasteSpecial
        Nrow2 = Nrow2 + 14
    ElseIf wbResults.Name Like strFile3 Then
        wbGCT.Sheets("YT DPrevious").Cells(Nrow3, 2).PasteSpecial
        Nrow3 = Nrow3 + 14
    ElseIf wbResults.Name Like strFile4 Then
        wbGCT.Sheets("R1 2").Cells(Nrow4, 2).PasteSpecial
        Nrow4 = Nrow4 + 14
    ElseIf wbResults.Name Like strFile5 Then
        wbGCT.Sheets("R1 2Previous").Cells(Nrow5, 2).PasteSpecial
        Nrow5 = Nrow5 + 14
End If
                        wbResults.Close SaveChanges:=False
                Application.StatusBar = strFile
   strF = Dir
Loop
                
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
End Sub

I'm starting to run out of ideas.
 
Upvote 0
Hi

THANK YOU SO much!!

It works perfectly!!!

Thank you jasonb75 soo much for your help you are a lifesaver!

Thanks to everyone who help me!!

:biggrin: :biggrin: :biggrin: :rofl:
 
Upvote 0
You're welcome, I'm just glad we got it working before I did run out of ideas entirely lol.
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
Simplyfying My Macro
Replies
2
Views
342
ian0886
I
N
  • Question Question
print out previous line
Replies
0
Views
539
newbie453
N
T
  • Question Question
Run-time error '-2147418113 (8000ffff)': Automation error Catastrophic failur
Replies
4
Views
3K
TTUK
T
F
  • Question Question
Case with like
Replies
7
Views
836
GTO
G
P
  • Question Question
Trying to use VBA to Save a CSV File
Replies
1
Views
492
JLGWhiz
JLGWhiz

Forum statistics

Threads
1,224,561
Messages
6,179,522
Members
452,923
Latest member
JackiG

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top