Copy cells from files in the loop and paste them transposed to MasterFile.

[dilshod_k]

Hello everyone,

I have a macros which loops through multiple workbooks (csv files) in a folder C:\VBA\, and makes changes to numerical values of each file, as a result of changes the value of the certain cells in the column “M” becomes (while the code is still processing CSV file) equal to BUY.

Structure of the rows in each CSV file is as it follows below:

Column ”A” – stock symbol – same in all the rows of a given file

Column “B” – Date stamp in descending order

Column ”F” – Close price

Column “M” – text value (“BUY”, “Sell”) or nothing

My aim is to insert piece of code that allows to copy just Cells of the row: “A” as it is, “B” and “F” - transposed) in case IF the row in question contains text value (BUY) in the Cell “M”, before the code will start processing next file in the loop.

I would also like to be able to copy several rows above it, even if their cells “M” values are “Sell” or nothing. So the minimum number of rows to be copied will be = 1 (in case if the row in question will be the upper most row – row#2) and maximum number of rows to be copied = n.

The number of rows to be copied I would like to specify in input cells something like as it follows:

For example:

Workbooks(“MasterFile”).Sheets(“Sheet1”).Range(“P1”).Value = Row# 128 (starting row, if it contains text value “BUY” if not, then do nothing with this CSV file) – row in question.

Workbooks(“MasterFile”).Sheets(“Sheet1”).Range(“P2”).Value = Ro# 128 – n (n rows above starting row).

So if n = 20, then Cells A, B and F from rows starting from Row128 to Row108 will be copied and pasted to MasterSheet.

Data from the next file will be pasted below data from the previous file.

But if both P1 and P2 cells will be equal to Row#2, then only cells from one row (row2) will be copied and pasted to:

Workbooks(“MasterFile”).Sheets(“MasterSheet”)

Cells “B128:B108” transposed and pasted to “B1:U1” only once for all CSV files during this run of the code

Cell “A” (one from each coped row) is a stock symbol pasted to column “A”

“F128:F108” transposed and pasted to “B2:U2”



My coding knowledge and experience are not enough to solve this problem. I hope description of the task is not too much confusing. If you have any questions, please let me know.

Thank you in advance for your time and help.

Dilshod

P.S. Below is the sample of my code.

Sub BuySellSignals()

Dim wb As Workbook, ws As Worksheet
Workbooks("MasterFile.xlsm").Worksheets("MasterSheet").Cells.Clear
Set fso = CreateObject("Scripting.FileSystemObject")
Set fldr = fso.GetFolder("C:\VBA\")

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayStatusBar = False
Application.DisplayAlerts = False

For Each wbFile In fldr.Files
    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]),""SELL"",""""))"
            Range("M2").AutoFill Destination:=Range("M2:M120")
       
            'I believe that the piece of code will need to be somewhere here
'as the value of cell "M" in the row in question of the given CSV file will be
'changed after these lines and the next file in the loop is going to be processed.


            ActiveSheet.Columns.AutoFit


        wb.Close True
End If
Next wbFile

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayStatusBar = True
Application.DisplayAlerts = True
Application.CutCopyMode = False


End Sub