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.

 

[Natsha]

Hi mumps,

Sorry I couldn't find anything, the code is working great can you,I just need all the workbooks to be pasted in one sheet that is sheet 2 of macro workbook.

Can you help again

 

[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).UsedRange.Offset(1).Copy Sheets(2).Cells(Sheets(2).Rows.Count, "A").End(xlUp).Offset(1)
            .Close False
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

 

[Natsha]

Hi mumps,

I am getting error on the below code

Sheets(1).UsedRange.Offset(1).Copy Sheets(2).Cells(Sheets(2).Rows.Count, "A").End(xlUp).Offset(1)

Description: I need all the workbooks data to be pasted in sheet-2 of macro workbook one by one below and I want the first row in all those workbooks to be removed.

 

[mumps]

What is the error message when you click "Debug"? What is the name of the sheet 2?

 

[Natsha]

The error message is runtime error 9

Name of macro workbook: combine-Macro-Final

 

[mumps]

What is the name of the destination sheet?

 

[Natsha]

Sheet 2 - I didn't give any particular name

 

[mumps]

Try:

Sub CopySheets()
    Application.ScreenUpdating = False
    Dim wsDest As Worksheet, wkbSource As Workbook, FolderName As String
    Set wsDest = ThisWorkbook.Sheets("Sheet2")
    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).UsedRange.Offset(1).Copy wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1)
            .Close False
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

 

[Natsha]

Yeah it's working fine..thank you so much... Do know how much this meant to me,I have failed 6 times ,I dnt know anything about macros,just I tried seeing tutorials on YouTube but that doesn't work.. You saved me lot.????

 

[mumps]

You are very welcome.