Specifying File name with Arrays, pls help

[bDreamie]

Hi all, I'm trying to repeat a process for different files and didn't want to repeat the codes. Therefore I'm trying to build sthg like this:

SheetName = Array("math", "english&quot

For i = 0 To UBound(SheetName)
Select Case SheetName(i)
Case Is = "math"
Call Cut_n_Paste(SheetName(i))
Case Is = "english"
Call Cut_n_Paste(SheetName(i))
End Select
Next

End Sub
------------------------
Sub Cut_n_Paste(SheetName)

Sheets(SheetName).Select
Columns("A:J&quot.Select
Selection.ClearContents
Range("A1&quot.Select
Workbooks.Open FileName:= _
"\\WINFSP\de$\school\subjects\(SheetName) & ".mtm""

However, I find that I can't get pass the last line of codes. What can I do to open the file name as according to the sheet name in case 1, 2 etc. Eg of a file name is math.mtm, english.mtm. The error I got is that the data type is different. How should I define the data type?

Thanks a lot for your help

 

[sunaj]

Hi,

You've mixed things up a bit. First the select statement is not necessary. Leave it out:

------------------------------------------------------------
SheetName = Array("math", "english&quot
For i = 0 To UBound(SheetName)
Call Cut_n_Paste(SheetName(i))
Next i
------------------------------------------------------------

It also a bit confusing that you've called the variable in the sub that holds the filename the same as the array, call it something else (here SName). The problem in the .open line is that the quotes are in the wrong place, Sheetname is a variable at should be outside the quotes:

------------------------------------------------------------
Sub Cut_n_Paste(SName as string)
Sheets(SName).Select
Columns("A:J&quot.Select
Selection.ClearContents
Range("A1&quot.Select
Workbooks.Open "\\WINFSP\de$\school\subjects\" & SName & ".mtm"
end sub
------------------------------------------------------------

Sunaj

 

[sunaj]

By the way you will probably get more/better response by posting your VB/Excel questions at VBA Visual Basic for Applications (Microsoft).