Need Macro Code

[myakymiv]

I need a macro to combine a bunch of excel files into one file. i want something that when i am ready to combine the file it prompts me to pick a folder and whatever is in that folder gets placed into one combined excel sheet

Thanks in advance

 

[DataBlake]

are all of the files you are wanting to combine the first sheet in the workbook?

 

[myakymiv]

Yes they are, and they are all in one folder as well

are all of the files you are wanting to combine the first sheet in the workbook?

 

[DataBlake]

Yes they are, and they are all in one folder as well

Sub getResult()
Dim ary1 As Variant
Dim wfile As String, wdiag As Long, wpath As String, wbook As String
Dim i As Integer
Dim wb As Workbook
Dim awb As Workbook
Dim aws As Worksheet

Set awb = ThisWorkbook
Set aws = ActiveSheet

With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = True
    .Filters.Clear
    .Filters.Add "Excel Files", "*.xls*"

    If .Show = True Then
        For i = 1 To .SelectedItems.Count
            Workbooks.Open .SelectedItems(i)
        Next i
    End If
    
End With

For Each wb In Application.Workbooks
  If wb.Name <> "PERSONAL.xlsb" And wb.Name <> awb.Name Then
      wb.Worksheets(1).Range("A1") = Date
            ary1 = wb.Worksheets(1).Range("A1").CurrentRegion.Value2
            wb.Close False
    End If
Next wb

aws.Range("A1").Resize(UBound(ary1), UBound(ary1, 2)).Value = ary1

End Sub

i don't know how to multi select everything in a folder, but this will allow you to highlight everything in a folder
some notes:

1. you must save whatever file this macro is as an xlsm
2. it must be run as the only open workbook
3. it will only show "xls" files you can change that as needed on the .Filters.Add "Excel Files", "*.xls*" line

let me know if that works for you

 

[Kenneth Hobson]

Next time, please follow guideline #4 . https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

Did you want to copy the used range and paste everything or just paste the values sort of like post #4 .

 

[myakymiv]

I am getting this error, its saying something is wrong with this

aws.Range("B4").Resize(UBound(ary1)).Value = ary1

Sub getResult()
Dim ary1 As Variant
Dim wfile As String, wdiag As Long, wpath As String, wbook As String
Dim i As Integer
Dim wb As Workbook
Dim awb As Workbook
Dim aws As Worksheet

Set awb = ThisWorkbook
Set aws = ActiveSheet

With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = True
    .Filters.Clear
    .Filters.Add "Excel Files", "*.xls*"

    If .Show = True Then
        For i = 1 To .SelectedItems.Count
            Workbooks.Open .SelectedItems(i)
        Next i
    End If
    
End With

For Each wb In Application.Workbooks
  If wb.Name <> "PERSONAL.xlsb" And wb.Name <> awb.Name Then
      wb.Worksheets(1).Range("A1") = Date
            ary1 = wb.Worksheets(1).Range("A1").CurrentRegion.Value2
            wb.Close False
    End If
Next wb

aws.Range("A1").Resize(UBound(ary1), UBound(ary1, 2)).Value = ary1

End Sub

i don't know how to multi select everything in a folder, but this will allow you to highlight everything in a folder
some notes:

1. you must save whatever file this macro is as an xlsm
2. it must be run as the only open workbook
3. it will only show "xls" files you can change that as needed on the .Filters.Add "Excel Files", "*.xls*" line

let me know if that works for you

 

[Kenneth Hobson]

I don't see that line of code in the example. The example assumes that your data starts at A1 and is in a matrix. This means 2 dimensions. Your code line is one dimension on left side of the equation. Since you did not post other code or link to a sample file, I don't know the dimensions of ary1.

You can paste a link to a simple example file from a site like dropbox.

The example needs some tweaks to get all data. I would have shown how to do it if you had answered my question.

 

[DataBlake]

I am getting this error, its saying something is wrong with this

i edited my post check that line again see if that does not work
you are also changing the range of where it is pasting the array, why is that?