Copying data from one workbook to anotherworkbook

[Natsha]

I need to create an macro, firstly-where I can pick any folder data,secondly the folders I am gonna pick has only 1 to 5 files not more than that,it varies some time like 1 to 4 but will not exist more than 5, each files sheet 1 name is different and all workbook has only one sheet, i need all the sheet 1 in all workbook should be pasted into macro workbook like sheet 1,sheet 2 ,sheet3... accordingly

Kindly help,I tried so many time but couldn't. I am not that much into macros.

 

[mumps]

Try:

Sub CopySheets()
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook, wkbSource As Workbook, FolderName As String
    Set wkbDest = ThisWorkbook
    With Application.FileDialog(msoFileDialogFolderPicker)
       .AllowMultiSelect = False
       .Show
       FolderName = .SelectedItems(1) & "\"
    End With
    ChDir FolderName
    strExtension = Dir("*.xls*")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(FolderName & strExtension)
        With wkbSource
            .Sheets(1).Copy after:=wkbDest.Sheets(wkbDest.Sheets.Count)
            .Close False
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

 

[Natsha]

?...omg it worked...ur my life saviour.... thank you so much...??

 

[Natsha]

?...omg it worked...ur my life saviour.... thank you so much...??

Also,I need additional help can I able to add comman button

 

[Natsha]

Try:

Sub CopySheets()
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook, wkbSource As Workbook, FolderName As String
    Set wkbDest = ThisWorkbook
    With Application.FileDialog(msoFileDialogFolderPicker)
       .AllowMultiSelect = False
       .Show
       FolderName = .SelectedItems(1) & "\"
    End With
    ChDir FolderName
    strExtension = Dir("*.xls*")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(FolderName & strExtension)
        With wkbSource
            .Sheets(1).Copy after:=wkbDest.Sheets(wkbDest.Sheets.Count)
            .Close False
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

Hi mumps,

Also I need to create an macro, where I can pick any folder data,secondly the folders I am gonna pick has only 1 to 5 files not more than that,it varies some time like 1 to 4 but will not exist more than 5, each files sheet 1 name is different and all workbook has only one sheet, i need all the sheet 1 in all workbook should be pasted in macro workbook sheet1 and also I want the header of all workbooks should be removed

Kindly help,

 

[mumps]

Also,I need additional help can I able to add comman button

Add a command button on your sheet and assign the macro to it. If you need help on how to do this, please let me know.

I want the header of all workbooks should be removed

What do you mean by "Header"? Please explain in detail.

 

[Natsha]

I dnt need frist row of all workbooks need to be removed ,to be more clear the heading I dnt want. I have attached the image , kindly check it

 

[mumps]

Try:

Sub CopySheets()
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook, wkbSource As Workbook, FolderName As String
    Set wkbDest = ThisWorkbook
    With Application.FileDialog(msoFileDialogFolderPicker)
       .AllowMultiSelect = False
       .Show
       FolderName = .SelectedItems(1) & "\"
    End With
    ChDir FolderName
    strExtension = Dir("*.xls*")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(FolderName & strExtension)
        With wkbSource
            .Sheets(1).Rows(1).Delete
            .Sheets(1).Copy after:=wkbDest.Sheets(wkbDest.Sheets.Count)
            .Close False
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

 

[Natsha]

Hey mumps,

It working fine but I need all the workbooks to be in one sheet that is sheet-2 of master workbook

Kindly help..

 

[mumps]

I suggested a solution in your other post.