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

dilshod_k

Board Regular
Joined
Feb 13, 2018
Messages
76
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.

VBA 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
 
Last edited:

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Watch MrExcel Video

Forum statistics

Threads
1,126,977
Messages
5,621,935
Members
415,867
Latest member
mauroccs

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
Top