Hello everyone,
I want to modify code which loops through multiple files in folder.
First thing after opening each file, I want it's name without extension pasted to MasterFile, Sheet1, cell "S5", but I do not know how to do it.
I would be grateful for any help. Thanks in advance.
Code
Sub BuySellSignalsModified()
Dim wb As Workbook, ws As Worksheet
Workbooks("MasterFile - Copy.xlsm").Worksheets("MasterSheet").Cells.Clear
Set fso = CreateObject("Scripting.FileSystemObject")
Set fldr = fso.GetFolder("C:\VBA\")
Set DestSh = Workbooks("MasterFile - Copy.xlsm").Worksheets("MasterSheet")
Set DestWb = Workbooks("MasterFile - Copy.xlsm")
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayStatusBar = True '<= do not use or change to True
Application.DisplayAlerts = False
Dim counter As Long
Dim numFiles As Long
numFiles = fldr.Files.Count
For Each wbFile In fldr.Files
Application.StatusBar = "Processing: " & Format(counter / numFiles, "0.000%") & " completed"
If fso.GetExtensionName(wbFile.Name) = "csv" Then
Set wb = Workbooks.Open(wbFile.Path)
Worksheets(1).Activate
Range("I1").Value = "V/SMA10"
Columns("I:I").NumberFormat = "General"
Range("I2").FormulaR1C1 = "=AVERAGE(RC[-1]:R[9]C[-1])"
Range("I2").AutoFill Destination:=Range("I2:I1500")
Range("J1").Value = "Vol/Change%"
Columns("J:J").NumberFormat = "0.00%"
Range("J2").FormulaR1C1 = "=(RC[-2]-RC[-1])/RC[-1]"
Range("J2").AutoFill Destination:=Range("J2:J1500")
Range("K1").Value = "SMA10"
Columns("K:K").NumberFormat = "0.00$"
Range("K2").FormulaR1C1 = "=AVERAGE(RC[-4]:R[9]C[-4])"
Range("K2").AutoFill Destination:=Range("K2:K1500")
Range("L1").Value = "SMA30"
Columns("L:L").NumberFormat = "0.00$"
Range("L2").FormulaR1C1 = "=AVERAGE(RC[-5]:R[29]C[-5])"
Range("L2").AutoFill Destination:=Range("L2:L1500")
Range("M1").Value = "BUY/SELL SMA10 CROSSOVER"
Range("M2").FormulaR1C1 = "=IF(AND(RC[-2]>RC[-1],R[1]C[-2]<R[1]C[-1],RC[-1]>R[1]C[-1]),""BUY"",IF(AND(RC[-7]<RC[-4],R[1]C[-7]>R[1]C[-4]),""SEL L"",""""))"
Range("M2").AutoFill Destination:=Range("M2:M1500")
Range("O1").Value = "Close/Change%"
Columns("O:O").NumberFormat = "0.00%"
Range("O2").FormulaR1C1 = "=(RC[-8]-R[1]C[-8])/R[1]C[-8]"
Range("O2").AutoFill Destination:=Range("O2:O1500")
ActiveSheet.Columns.AutoFit
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayStatusBar = True
Application.DisplayAlerts = False
For i = 2 To 2
If Worksheets(1).Cells(i, 13).Value = "BUY" Then
ActiveWorkbook.Worksheets(1).Rows(i).Copy
DestSh.Activate
DestRowNumber = DestSh.Cells(Rows.Count, 1).End(xlUp).Row
With DestSh.Cells(DestRowNumber + 1, 1)
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With
End If
Next
DestSh.Columns.AutoFit
wb.Close True
End If
counter = counter + 1
Next wbFile
Application.StatusBar = False
Application.ScreenUpdating = True
Application.EnableEvents = True
'Application.DisplayStatusBar = True
Application.DisplayAlerts = True
Application.CutCopyMode = False
End Sub
Display More