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

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
3,194
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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.

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!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top