Posts by akin

    I need assistance of experts in the house on how to copy source workbook names along with other data in files I am to merge in new worksheet. The code will create a new column in the new worksheet and will fill it with the source book name (preferably first column)

    I have this code that merge files based on number of rows already but with no demarcation on where one ends and another begins:

    I have sample files for testrun too.


    Already, I have this VBA script that merge workbooks

    Hello house. I need urgent help on how to group some workbooks containing one worksheet each with similar data in them but varied rows of data.

    The groupping becomes necessary because I want to run a macro script on them which is row sensitivity and has been giving conflicting results.

    Please, I need the assistance of experts in the house on groupping the workbooks in the folder. The script will run through or loop through the folder by counting the rows and merge similar workbooks based on number of rows in one sheet. I will not mind if the sheets I will be getting at the end will be in separate worksheets in a workbook.

    Thanks for your usual assistance.

    Noted, sir. I now saw the symbol;I was actually looking for it.

    I am trying to analyse some American stock options historical price using standard deviation method to pick stock with highest price move as can be seen in the summary table attached.

    I copied the script from a book I am reading on it. When I tried to apply the script, it was not running with the workbook (the sample I attached).

    Please help me with it for any modification needed. Thanks.

    I am very sorry for that.

    You are right. That is not the whole code; I copied the first aspect. The code is too long and I did not what to stress you, thinking that if one worked I will be able to manipulate my way through the rest. The whole code is:

    And workbook to apply it to is attached as first attachment

    After the vba script has been run, it will produce a table similar to the one in second attachment.

    In all, this script set to achieve on the workbook:

    1. Align record in the Merged worksheet (that consist five different data each consisting 252 rows) of data base on date and remove unmatched ticker or row of data

    2. Remove blank row of data

    3. Create extra columns for calculation like 'price change', 'log of price change', 'standard deviation' and 'price spike'

    4. Finally, produce the summary table as seen in the attached table

    I really appreciate your kind gesture. Once again, I am sorry for my early attitude. Thanks.

    Pls, I am new to VBA. I copy this script below from a book on how to calculate standard deviation on a data in a sheet and record in separate column.

    The problem I am having is that the script is not running. Please, what could be the reason? Or rather help me modify it. Thanks.

    The script:


    When I tried that. Meaning, when I was pressing F8 repeating and the lines got to 'FolderName=. SelectedItem(1)&"\" ' it tried to open source folder. And when I picked it, it failed to performed any function. There was no error generated and it stopped there. I mean the F8 refused to go pass there. Thanks alot.

    Thanks alot. It worked perfectly.

    Sorry for this. I have a slight problem; I first used it with my sample folder that contain just 4 workbooks and it worked, but when I wanted to use it with the real folder containing 380 workbooks it failed to work. It did not respond at all. Please what could be responsible? Thanks

    Pls, I need help on how to merge multiple excel workbooks into single worksheet but with demarcation. I mean I want to know where one workbook data starts and ends, preferably filling the entire first column with workbook name.

    What this mean is that as each workbook is copying to the new worksheet, it will pick its workbook name and use it to fill entire first column in the new worksheet and do that to all workbooks.
    I had tried the code below before but I was giving me 'compile error: Label not define' . I want to know what is wrong with the code. Or preferably a new one.

    Sub Button2_Click()
    Dim Wkb As Workbook
    Dim wbDest As Workbook, shtDest As Worksheet, source As Worksheet
    Dim path As String, ThisWB As String, Filename As String
    Dim CopyRng As Range, Dest As Range
    Dim currLastrow As Long, prevlastrow As Long        On Error GoTo err_exit    Application.EnableEvents = False    Application.ScreenUpdating = False
        currLastrow = 2 ' Row to start on in the sheets you are copying from        ThisWB = ActiveWorkbook.Name    Set shtDest = ActiveWorkbook.Sheets(1)        path = GetDirectory("Select a folder containing Excel files you want to merge")        Filename = Dir(path & "\*.xls", vbNormal)    If Len(Filename) = 0 Then Exit Sub        Do Until Filename = vbNullString            If Not Filename = ThisWB Then                    Set Wkb = Workbooks.Open(Filename:=path & "" & Filename)            Set source = Wkb.Sheets(1)            Set CopyRng = source.Range(source.Cells(currLastrow, 1), source.Cells(ActiveSheet.UsedRange.Rows.Count, ActiveSheet.UsedRange.Columns.Count))                        Dest = shtDest.Range("B" & shtDest.UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1)            CopyRng.Copy Dest                        Wkb.Close False                        prevlastrow = currLastrow            currLastrow = shtDest.Cells(shdest.Rows.Count, "B").End(xlUp).Row            shdest.Cells(prevlastrow, "A").Resize(currLastrow - prevlastrow + 1).Value = Filename        End If                Filename = Dir()    Loop        shdest.Range("A1").Select        Application.EnableEvents = True    Application.ScreenUpdating = True        MsgBox "Done!"        Exit Sub
    GoTo err_exit:    Application.EnableEvents = True    Application.ScreenUpdating = True
    End Sub

    Thanks a lot.