Posts by yogin

    Re: VBA- Copying data from multiple Excel files


    in the file you have this information, which sheet and cell this information is on? for example if you wanted Account 507 in cell B9 where will this information be in file 18112013?


    Also what is the file name saved as? are they 18112013 or 181113 or something else? Also will these files be in same location?

    Re: Select a range of cells and give it a name


    try the below code:


    Re: move files to selected folder - code help


    i think i got it and its works fine...


    hi can someone please help me. I need Macro button which can move all the files which start Name in column A and move then to a folder in the same drive which is mentioned in Column B.


    for example, I will enter the path where i need this to done which is cell B2. And then the macro should run and move all files which starts with Bondi (A4 cell) and move to folder which is mentioned cell B4. in case the folder does not exist then it should create the folder and move the file.


    i do this daily for 500 files and move them into 20 to 23 folder each day. if some one can please create this it will help me alot. i did find few code on internet but not that good with VBA to make it work for me.


    Move file if its exist:

    Code
    Sub MoveFile()Dim fsoDim file As String, sfol As String, dfol As Stringfile = "test.xls" ' change to match the file namesfol = "C:\" ' change to match the source folder pathdfol = "E:\" ' change to match the destination folder pathSet fso = CreateObject("Scripting.FileSystemObject")If Not fso.FileExists(sfol & file) Then    MsgBox sfol & file & " does not exist!", vbExclamation, "Source File Missing"ElseIf Not fso.FileExists(dfol & file) Then    fso.MoveFile (sfol & file), dfolElse    MsgBox dfol & file & " already exists!", vbExclamation, "Destination File Exists"End IfEnd Sub


    Create folder if it does not exist:


    Code
    Sub CreateFolder()Dim fsoDim fol As Stringfol = "c:\MyFolder" ' change to match the folder pathSet fso = CreateObject("Scripting.FileSystemObject")If Not fso.FolderExists(fol) Then    fso.CreateFolder (fol)Else    MsgBox fol & " already exists!", vbExclamation, "Folder Exists"End IfEnd Sub

    Re: Alternate formula or VBA code‏


    Ok let me start again…..i get a file on daily bases which is pasted as is in Data tab. Which in turn fills Data lookup tab and I get list of which in Tab “key”. This is used to create tab name.

    R2 is the where I fill from list which is been created in Key. Which fill U2, U3, U4, K3 and K4 by vlookup from data lookup tab.

    And I think the problem is in this part which is using array formula, which is from B23 to I37..

    The array formula will list all client type where aware Code is same as R2 and column X in data lookup is Y.

    The file works fine and it does give me the answer I did but the only issue I have is taking a very long time to update and mainly because of the array formula which is taking too long to calculate. And this is where I need help, if there is away I can get this calculation working faster.

    Re: Alternate formula or VBA code‏


    i am still stuck, i did turn of calculation and then tried shift F9 on each each which still taking so long, and also tried F9 which look longer..


    is there a way i get tried of array formula to get the data that i want or am i stuck with this?

    Re: Alternate formula or VBA code‏


    kanbriiz dont overtake a thread which does not effect you... people post things because they need help.


    hi Roy, yes i do have array formula B23 to I37, as i need to pull the data from the lookup data. I am not sure how else i can pull the data. so that i can get the tab created with the information. is there a way a macro can do this?


    the whole process work in this way, i get a file each day which i copy in Data tab which in turn fills data lookup tab. Then i create the tab (which is taking ages) and once completed, i paste these as value.