Select Case statement skipping the cases

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
hI

Ok i understand the numeric thing i have changed the code and the template to highlight the correct sheets for each case and also added the case the number in to the template workbook.

should i add an if statement around the loop to check whether strFile is ""?
 
Upvote 0

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Yes - no point trying to open a non-existent file. ;)
 
Upvote 0
Hi

I have added in if statements so it check strFile but it not runs the if statement in the first case statement and then goes straight to End Select. Have i put the if statement in the wrong place?

This is the code i have now

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.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
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")
lNextrow = 2
ActiveWorkbook.Sheets("MTH").Select
Range("A1").Select
MsgBox ActiveCell.Value
Select Case ActiveCell.Value
Case 1
  If Len(strFile) > 0 Then
   Do
        Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile)
        Set wsMyCsvSheet = wbCsv.Sheets(1)
        With Workbooks("GraphingChartTemplate.xlsx").Sheets("MTH")
            wsMyCsvSheet.Range("A2:M14").Copy
            Workbooks("GraphingChartTemplate.xlsx").Sheets("MTH").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
  End If
    ActiveWorkbook.Sheets("MTHPrevious").Select
    Range("A1").Select
    
Case 2
  If Len(strFile1) > 0 Then
    Do
        Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile1)
        Set wsMyCsvSheet = wbCsv.Sheets(1)
        With Workbooks("GraphingChartTemplate.xlsx").Sheets("MTHPrevious")
            wsMyCsvSheet.Range("A2:M14").Copy
            Workbooks("GraphingChartTemplate.xlsx").Sheets("MTHPrevious").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
  End If
    ActiveWorkbook.Sheets("YTD").Select
    Range("A1").Select
    
Case 3
  If Len(strFile2) > 0 Then
    Do
        Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile2)
        Set wsMyCsvSheet = wbCsv.Sheets(1)
        With Workbooks("GraphingChartTemplate.xlsx").Sheets("YTD")
            wsMyCsvSheet.Range("A2:M14").Copy
            Workbooks("GraphingChartTemplate.xlsx").Sheets("YTD").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
  End If
    
    ActiveWorkbook.Sheets("YTDPrevious").Select
    Range("A1").Select
    
Case 4
  If Len(strFile3) > 0 Then
    Do
        Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile3)
        Set wsMyCsvSheet = wbCsv.Sheets(1)
        With Workbooks("GraphingChartTemplate.xlsx").Sheets("YTDPrevious")
            wsMyCsvSheet.Range("A2:M14").Copy
            Workbooks("GraphingChartTemplate.xlsx").Sheets("YTDPrevious").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
  End If
    
    ActiveWorkbook.Sheets("R12").Select
    Range("A1").Select
    
Case 5
  If Len(strFile4) > 0 Then
    Do
        Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile4)
        Set wsMyCsvSheet = wbCsv.Sheets(1)
        With Workbooks("GraphingChartTemplate.xlsx").Sheets("R12")
            wsMyCsvSheet.Range("A2:M14").Copy
            Workbooks("GraphingChartTemplate.xlsx").Sheets("R12").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
  End If
    
    ActiveWorkbook.Sheets("R12Previous").Select
    Range("A1").Select
    
Case 6
  If Len(strFile5) > 0 Then
    Do
        Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile5)
        Set wsMyCsvSheet = wbCsv.Sheets(1)
        With Workbooks("GraphingChartTemplate.xlsx").Sheets("R12Previous")
            wsMyCsvSheet.Range("A2:M14").Copy
            Workbooks("GraphingChartTemplate.xlsx").Sheets("R12Previous").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 If
End Select
End Sub
 
Upvote 0
What are you expecting it to do then?

your select case is much the same as

Code: 
if activecell .value = 1 then
do this
elseif activecell.value = 2 then
do this
etc

You have 1 activecell, which means 1 value, which executes 1 case based on that value.
 
Last edited:
Upvote 0
Hi

Its kinda complicated what i am trying to do. its difficult to explain but i'll try.

I need to make the macro loop through the folder "strFldr" and depending on the file name variable (strFile, strFile1, strFile2, strFile3, strFile4, strFile5) paste the data into different worksheets within the template workbook.

So that it does the following:
If the filename is "Graphing_MTH_Actual_Curr_Year" & "*.csv" paste data on the MTH Sheet
If the filename is "Graphing_MTH_Actual_Prev_Year" & "*.csv" paste data on the MTHPrevious Sheet

If the filename is "Graphing_YTD_Actual_Curr_Year" & "*.csv" paste data on the YTD Sheet
If the filename is "Graphing_YTD_Actual_Prev_Year" & "*.csv" paste data on the YTDPrevious Sheet

If the filename is "Graphing_R12_Actual_Curr_Year" & "*.csv" paste data on the R12 Sheet
If the filename is "Graphing_R12_Actual_Prev_Year" & "*.csv" paste data on the R12Previous Sheet

I tried having multiple If statments but it did not work so i was trying Select case statements.:confused:
 
Upvote 0
I am guessing from the slience your either helping (if so i am sorry for interrupting) or i have utterly confused everyone :confused:
 
Upvote 0
Maybe others will disagree, but from what I can see, this is a start again task.

I'm not 100% certain that this method would work, but to give you an idea, a quick template based on some code from Ozgrid. Still needs the rest of your code edited in (with a lot of alterations) which is going to take a while.

Code: 
Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
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"
On Error Resume Next
    Set wbCodeBook = ThisWorkbook
        With Application.FileSearch
            .NewSearch
            'Change path to suit
            .LookIn = strFolder
            .FileType = msoFileTypeExcelWorkbooks
            'Optional filter with wildcard
            .Filename = "Graphing_*_Actual_*_Year*.CSV"
                If .Execute > 0 Then 'Workbooks in folder
                    For lCount = 1 To .FoundFiles.Count 'Loop through all
                        'Open Workbook x and Set a Workbook variable to it
                        Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
                        
                        'DO YOUR CODE HERE
Select Case FoundFiles
Case strFile
Case strFile1
Case strFile2
Case strFile3
Case strFile4
Case strFile5
End Select
                        wbResults.Close SaveChanges:=False
                    Next lCount
                End If
        End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

Richard / Rory, any thoughts? Do you think the wildcard will work with Select Case?
 
Upvote 0
Hi

Breakthrough!!!

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.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
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")
lNextrow = 2
ActiveWorkbook.Sheets("MTH").Select
Range("A1").Select
Select Case ActiveCell.Value
Case 1
   If Len(strFile) = 0 Then
   ActiveWorkbook.Sheets("MTH").Select
   Range("A1").Select
   Else
   Do
        Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile)
        Set wsMyCsvSheet = wbCsv.Sheets(1)
        With Workbooks("GraphingChartTemplate.xlsx").Sheets("MTH")
            wsMyCsvSheet.Range("A2:M14").Copy
            Workbooks("GraphingChartTemplate.xlsx").Sheets("MTH").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
    ActiveWorkbook.Sheets("MTHPrevious").Select
    Range("A1").Select
    End If
    
Case 2
   If Len(strFile) = 0 Then
   ActiveWorkbook.Sheets("YTD").Select
   Range("A1").Select
   Else
    Do
        Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile1)
        Set wsMyCsvSheet = wbCsv.Sheets(1)
        With Workbooks("GraphingChartTemplate.xlsx").Sheets("MTHPrevious")
            wsMyCsvSheet.Range("A2:M14").Copy
            Workbooks("GraphingChartTemplate.xlsx").Sheets("MTHPrevious").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
    ActiveWorkbook.Sheets("YTD").Select
    Range("A1").Select
    End If
    
Case 3
   If Len(strFile) = 0 Then
   ActiveWorkbook.Sheets("YTDPrevious").Select
   Range("A1").Select
   Else
    Do
        Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile2)
        Set wsMyCsvSheet = wbCsv.Sheets(1)
        With Workbooks("GraphingChartTemplate.xlsx").Sheets("YTD")
            wsMyCsvSheet.Range("A2:M14").Copy
            Workbooks("GraphingChartTemplate.xlsx").Sheets("YTD").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
    
    ActiveWorkbook.Sheets("YTDPrevious").Select
    Range("A1").Select
    End If
    
Case 4
   If Len(strFile) = 0 Then
   ActiveWorkbook.Sheets("R12").Select
   Range("A1").Select
   Else
    Do
        Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile3)
        Set wsMyCsvSheet = wbCsv.Sheets(1)
        With Workbooks("GraphingChartTemplate.xlsx").Sheets("YTDPrevious")
            wsMyCsvSheet.Range("A2:M14").Copy
            Workbooks("GraphingChartTemplate.xlsx").Sheets("YTDPrevious").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
    
    ActiveWorkbook.Sheets("R12").Select
    Range("A1").Select
    End If
    
Case 5
   If Len(strFile) = 0 Then
   ActiveWorkbook.Sheets("R12Previous").Select
   Range("A1").Select
   Else
    Do
        Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile4)
        Set wsMyCsvSheet = wbCsv.Sheets(1)
        With Workbooks("GraphingChartTemplate.xlsx").Sheets("R12")
            wsMyCsvSheet.Range("A2:M14").Copy
            Workbooks("GraphingChartTemplate.xlsx").Sheets("R12").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
    
    ActiveWorkbook.Sheets("R12Previous").Select
    Range("A1").Select
    End If
    
Case 6
   If Len(strFile) = 0 Then
   ActiveWorkbook.Sheets("Home").Select
   Range("A1").Select
   Else
    Do
        Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile5)
        Set wsMyCsvSheet = wbCsv.Sheets(1)
        With Workbooks("GraphingChartTemplate.xlsx").Sheets("R12Previous")
            wsMyCsvSheet.Range("A2:M14").Copy
            Workbooks("GraphingChartTemplate.xlsx").Sheets("R12Previous").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 If
    
End Select
End Sub


I have been mucking around i got to loop but only when the line highlighted in red is commented out. Whens its commented out it loops the same folder over and over until i pres Esc but when it is not commented out it produces an error saying "Invalid Procedure call or argument"
 
Upvote 0
Think you're still going to have problems with that, you're looping within the case, you can only have 1 case per pass of the code so the rest will still get skipped.

If you look at my last post, the Select Case is within the loop, not the loop within the Select Case.

Then the Case is selected based on the name of the currently opened file.
 
Upvote 0
Hi

Ok but with that code how do i get it to paste the data into the correct worksheet?
 
Upvote 0
You must log in or register to reply here.

Similar threads

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

Forum statistics

Threads
1,215,004
Messages
6,122,659
Members
449,091
Latest member
peppernaut

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