Excel masterfile vba

[moshc]

Good day!

Anyone can help me please?

I've been looking for an EXCEL VBA code in which i will have the option to select a folder path first which all the workbooks i need to combined is saved and have all the first sheets in all workbook in that specific folder is then combined to a new workbook.

Would really much appreciate any immediate response.

Thank you!

 

[mumps]

Open a blank workbook, place this macro in a standard module and save the workbook as a macro-enabled file. Run the macro.

Sub CopySheets()
    Application.ScreenUpdating = False
    Dim MyFolder As String, MyFile As String, srcWB As Workbook, desWB As Workbook
    Set desWB = ThisWorkbook
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Please select a folder"
        .Show
        .AllowMultiSelect = False
        If .SelectedItems.Count = 0 Then
            MsgBox "You did not select a folder."
            Exit Sub
        End If
        MyFolder = .SelectedItems(1) & "\"
    End With
    MyFile = Dir(MyFolder)
    Do While MyFile <> ""
        Set srcWB = Workbooks.Open(Filename:=MyFolder & "\" & MyFile)
        With srcWB
            .Sheets(1).Copy desWB.Sheets(desWB.Sheets.Count)
            .Close False
        End With
        MyFile = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

 

[moshc]

Works perfectly thank you so much! Although apologies if i have follow up help needed, do you know any code where data copied to the new workbook are paste as values only? Like the files i needed to combined has formula in it and drop down menus.

 

[mumps]

Try:

Sub CopySheets()
    Application.ScreenUpdating = False
    Dim MyFolder As String, MyFile As String, srcWB As Workbook, desWB As Workbook
    Set desWB = ThisWorkbook
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Please select a folder"
        .Show
        .AllowMultiSelect = False
        If .SelectedItems.Count = 0 Then
            MsgBox "You did not select a folder."
            Exit Sub
        End If
        MyFolder = .SelectedItems(1) & "\"
    End With
    MyFile = Dir(MyFolder)
    Do While MyFile <> ""
        Set srcWB = Workbooks.Open(Filename:=MyFolder & "\" & MyFile)
        With srcWB
            .Sheets(1).Copy desWB.Sheets(desWB.Sheets.Count)
            With ActiveSheet.UsedRange
                .Cells.Validation.Delete
                .Cells.Value = .Cells.Value
            End With
            .Close False
        End With
        MyFile = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

 

[moshc]

Brilliant! Just brilliant hats off to you mumps you've save me a lot working days hehehe.

 

[mumps]

My pleasure.

 

[tech3]

Can you help me as well with vba code? I'm trying to figure out a way how to automatically click don't update on the excel file that i've been working on.

 

[mumps]

@tech3
Welcome to the Forum. According to Forum rules, you should not post your question in another member's thread. Please start your own new thread explaining in detail what you want to do.

 

[tech3]

Ooh my apologies mumps, I'm only new and was kinda of in a rush for answer. really sorry about it. I however have posted a thread as well can you help me on that?

 

[moshc]

It's me again! Need additional help on the vba code provided please. the sheets i'm trying to copy are protected and i'm having error prompt when executing the command. please anyone?