Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel VBA to copy range from closed workbook to active workbook

Posted on 2016-07-21
2
Medium Priority
?
126 Views
Last Modified: 2016-07-27
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.
0
Comment
Question by:DougDodge
2 Comments
 
LVL 33

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 2000 total points
ID: 41723194
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
 
LVL 22

Expert Comment

by:Roy Cox
ID: 41723442
If you don't want to open the workbook there's some code eamples using ADO here
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question