Excel VBA to copy range from closed workbook to active workbook

DougDodge
DougDodge used Ask the Experts™
on
Looking to use VBA to copy range columns A:W from a closed workbook in worksheet "Worksheet1" to my active workbook, worksheet "Worksheet1" range  columns A:W

The source workbook has a different name each time it is looked for, as it is renamed after the last update.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
You may try something like this....
The code will prompt you to select the source file.

Sub CopyFromAnotherWorkbook()
Dim swb As Workbook, wb As Workbook
Dim sws As Worksheet, ws As Worksheet

Dim SelectedFile As String

Application.ScreenUpdating = False
Set swb = ThisWorkbook
Set sws = swb.Sheets("Sheet1")
sws.Range("A:W").ClearContents

With Application.FileDialog(msoFileDialogFilePicker)
   .Title = "Select A Folder!"
   .ButtonName = "Confirm"
   .Filters.Clear
   .Filters.Add "Select an Excel File to open", "*.xlsx"
   If .Show = -1 Then
      SelectedFile = .SelectedItems(1)
   Else
      MsgBox "You didn't select a file.", vbExclamation, "File Not Selected!"
      Exit Sub
   End If
End With
Workbooks.Open SelectedFile
Set wb = ActiveWorkbook
Set ws = wb.Sheets(1)
ws.Range("A:W").Copy sws.Range("A1")
wb.Close False
Application.ScreenUpdating = True
MsgBox "Data has been successfully copied.", vbInformation, "Done!"
End Sub

Open in new window

Roy CoxGroup Finance Manager

Commented:
If you don't want to open the workbook there's some code eamples using ADO here

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial