[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.[/SIZE]
Dim CurrentBook As Workbook Dim info As String info = "UserForm 1 Matches" Dim lastrow As Long Dim length As Integer Dim MyFileName As Variant Dim WS As Worksheet Set WS = ThisWorkbook.Sheets("Sheet2") Dim IndvFiles As FileDialog Dim FileIdx As Long Dim i As Integer, x As Integer Dim r As Range Dim Sheet As Variant Set IndvFiles = Application.FileDialog(msoFileDialogOpen) With IndvFiles .AllowMultiSelect = True .Title = "Multi-select target data files:" .ButtonName = "" .Filters.Clear .Filters.Add ".csv files", "*.csv" .Show End With Application.DisplayAlerts = False Application.ScreenUpdating = False For FileIdx = 1 To IndvFiles.SelectedItems.Count Set CurrentBook = Workbooks.Open(IndvFiles.SelectedItems(FileIdx)) For Each Sheet In CurrentBook.Sheets Dim LRow1 As Long LRow1 = WS.Range("A" & WS.Rows.Count).End(xlUp).Row Dim LRow2 As Long LRow2 = CurrentBook.ActiveSheet.Range("A" & CurrentBook.ActiveSheet.Rows.Count).End(xlUp).Row Dim ImportRange As Range Set ImportRange = CurrentBook.ActiveSheet.Range("A2:Z" & LRow2) ImportRange.Copy WS.Range("A" & LRow1 + 1).PasteSpecial 'Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Next CurrentBook.Close False Next FileIdx