Hi everyone,
Can anyone help with the following problem please. The macro below should open some files, copy data into excel, save the excel file and then cycle round. However, it fails to compile/run with the message:
"Compile error: Expected Function or variable"
and points to the first occurence of the word selection, ie the line
Range(Selection, Selection.End(xlDown)).Select
If I comment that line, it just makes it point to the next occurence of selection and so on.
I do not understand why it does this as almost identical code in other workbooks functions fine. Is there something really obvious I am missing, such as changing some setting somewhere?
Any help or advice would be much appreciated.
The code is as follows:
Sub FileOpener()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim File_Names As Variant
Dim FFF As String
Dim Temp_File_Name As String
Dim Source_Temp_File_Name As String
Dim i As Integer
Dim Full_Path_Name As String
Dim Text_File_Name As Variant
Dim Name_for_File As String
ChDrive ("C")
ChDir ("C:\Projects\Data")
FFF = "Excel Files (*.xls), *.xls," & "Text Files (*.txt), *.txt," & "All files (*.*), *.*"
File_Names = Application.GetOpenFilename(FFF, 2, "Select files for import ...", , MultiSelect:=True)
If IsArray(File_Names) = False Then
If File_Names = False Then
MsgBox "Import cancelled by user"
Exit Sub
End If
End If
For i = LBound(File_Names) To UBound(File_Names)
Source_Temp_File_Name = ThisWorkbook.Name
Full_Path_Name = File_Names(i)
Text_File_Name = Split(Full_Path_Name, ".")
Name_for_File = Text_File_Name(0) & ".xls"
Workbooks.Open File_Names(i)
Temp_File_Name = ActiveWorkbook.Name
Windows(Temp_File_Name).Activate
Range("B2:C2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows(Source_Temp_File_Name).Activate
Sheets("Data").Activate
Range("P3:Q2000").Select
Selection.Clear
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
Range("P3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("C1").Select
Windows(Temp_File_Name).Close
Windows(Source_Temp_File_Name).Activate
ActiveWorkbook.SaveAs Name_for_File
Next
End Sub
Bookmarks