Macro to choose document from folder and copy data into current document?

[tonywatsonhelp]

Hi Everyone,

I need a macro that opens up the file explorer and lets me choose the document i want to open,
then copies range A1:Z & last row, and pastes it into the document the macro is run from sheet "new Data" A1

please help if you can

Thanks

Tony

 

[Domenic]

The following macro copies data from the first worksheet of the user selected workbook. Also, it doesn't clear any existing data from the destination worksheet before pasting the data, but simply adds it to the existing data. Post back if you need help with making any changes to the code.

Option Explicit

Sub CopyDataFromSelectedWorkbook()


    Dim strFileName As String
    Dim wkbOpen As Workbook
    Dim rngCopy As Range
    
    strFileName = Application.GetOpenFilename( _
        FileFilter:="Excel Files (*.xls;*xlsx), *.xls;*xlsx", _
        FilterIndex:=1, _
        Title:="Select file . . .", _
        ButtonText:="Open", _
        MultiSelect:=False)
        
    If strFileName = "False" Then Exit Sub
    
    Application.ScreenUpdating = False
    
    Set wkbOpen = Workbooks.Open(strFileName)
    
    With wkbOpen.Worksheets(1)
        Set rngCopy = .Range("A1:Z" & .Cells(.Rows.Count, "A").End(xlUp).Row)
    End With
    
    With ThisWorkbook.Worksheets("new Data")
        rngCopy.Copy .Cells(.Rows.Count, "A").End(xlUp)(2)
        .Activate
    End With
        
    wkbOpen.Close SaveChanges:=False
    
    Application.ScreenUpdating = True
    
End Sub

Hope this helps!