Select multiple worksheets and copy to desktop folder

  • [SIZE=11px]Using a macro enabled work book to process a number of files and have them in a desktop folder together with the result.

    What I’m trying to do

    1 User selects one or more.csv files and clicks “Copy”

    2 Macro creates new desktop folder using

    MkDir "C:\Users" & Environ("UserName") & "\Desktop\Matches" & " " & Format (Now (), "DD-MMM-YYYY hh mm ss")

    (Time format so there will never be an issue with the folder already existing)

    3 Macro pastes copies of selected files intact into newly created folder.

    4 Macro takes Sheet2 of each from row 2** and combines them into new sheet "Matches.csv", which is then opened.
    **(So headers are lost. This is because I am sure there is a way of merging them using the headers from the first sheet but I don’t know how to do this, so I have cheated and get the macro to populate the headers afterwards)[/SIZE]

    [SIZE=11px]From that point on I know what I'm doing and can run the macro over the sheet and save the resulting summary alongside the sheets that have been summarised. I have pieced together the code below which works in conjunction with the rest of my macro, but of course does everything within the macro enabled workbook. I am very much a beginner at this so have no real idea how to modify this to do what I have set out above. All I seem to be able to find is based on moving files between pre-determined folder locations, where I need something dynamic.[/SIZE]

    [SIZE=11px]I should also explain that these were originally written by a colleague who is no longer with us. Although I was involved in this it was only as a user, advising what was required. I had nothing to do with writing the actual code. I have had to sit down with text books and Google to try and learn what the code does and how it works. I have learned to modify the body of the macros reasonably well but the initial download and saving areas are still a bit of a mystery to me. So far I have been able to use the code he wrote previously. What I’m trying to do here is outside of that and so I am struggling. Suffice to say I am not anything like as proficient at this as some of the code might lead you to think!
    Any help would be greatly appreciated.

  • See if this macro does steps 1 to 4, as required. Rather than opening each selected .csv file, it uses a text query to import the data into the macro workbook. The first .csv file import starts at row 1, to include column headings, and subsequent files start at row 2 to omit them. I put comments in the code to help you to understand it.

  • Wow! That is so helpful of you I will cetainly try that, and the explanations are just what I needed to try and get my head around it. Thank you so much.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!