Excel VBA to copy range from closed workbook to active workbook

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.
DougDodgeAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
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

0
 
Roy CoxGroup Finance ManagerCommented:
If you don't want to open the workbook there's some code eamples using ADO here
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.