VBA code that prompts user to select file and copy data from workbook

[surkdidat]

Hi there

Please can someone help!

I would like some VBA code whereby when a button is pressed it allows the user to select a particular folder.

In that workbook, I need the code to automatically copy data from a sheet called "Item Summary" and then to select Columns B:M and copy them into the new worksheet (and then close the window where data copied from)

Many thanks in advance

 

[THE_NEW_XL_GUY]

can you be more clear? u mean to say a workbook in folder? like prompt to select that file? if so try this..

code to browse a folder:

Sub GetFile()
Dim fileNameAndPath As Variant
fileNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLS), *.XLS", Title:="Select File To Be Opened")
If fileNameAndPath = False Then Exit Sub
Workbooks.Open Filename:=fileNameAndPath
End Sub

.

code to copying code:

workbooks("workbookwithdata.xlsx").worksheets("item summary").range("B1:M100").copy workbooks("newbook.xlsx").worksheeet("sheet1").range("a1")

.

 

[surkdidat]

Hi there

Many thanks for the first bit of the code, which allows me to select the workbook I want to extract the data from.
Apologies, for not making it clear.

- This code is to sit on a button in a workbook called "League Table Report"
- When the button is pressed I want it to run the code provided (many thanks, works fine)
- When this new workbook opens, I want it to find the "Items Summary" tab and copy columns B:M (from the workbook the VBA code has just opened)
- I then need it to revert back to the workbook I already have open (called "League Table Report") and find the "Items Summary" sheet in this workbook and paste the data in

Hope that explains it a bit better!

can you be more clear? u mean to say a workbook in folder? like prompt to select that file? if so try this..

code to browse a folder:

Sub GetFile()
Dim fileNameAndPath As Variant
fileNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLS), *.XLS", Title:="Select File To Be Opened")
If fileNameAndPath = False Then Exit Sub
Workbooks.Open Filename:=fileNameAndPath
End Sub

.

code to copying code:

workbooks("workbookwithdata.xlsx").worksheets("item summary").range("B1:M100").copy workbooks("newbook.xlsx").worksheeet("sheet1").range("a1")

.

 

[THE_NEW_XL_GUY]

Try like this:

private sub GetFile()
Dim fileNameAndPath As Variant
fileNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLS), *.XLS", Title:="Select File To Be Opened")
If fileNameAndPath = False Then Exit Sub
Workbooks.Open Filename:=fileNameAndPath

Worksheets("Items Summary").Activate
worksheets("item summary").Columns("B:M").Copy Destination:=Sheets("Items Summary").Range("give range where to copy")

wb.Close savechanges:=False
End Sub

 

[surkdidat]

Many thanks

 

[THE_NEW_XL_GUY]

Glad it worked.

 

[Fluff]

How about

Sub GetFileCopyData()
   Dim Fname As String
   Dim SrcWbk As Workbook
   Dim DestWbk As Workbook
   
   Set DestWbk = ThisWorkbook
   
   Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*", title:="Select a File")
   If Fname = "False" Then Exit Sub
   Set SrcWbk = Workbooks.Open(Fname)
   
   SrcWbk.Sheets("Items Summary").Range("B:M").Copy DestWbk.Sheets("Items Summary").Range("B:M")
   
   SrcWbk.Close False
End Sub

@THE_NEW_XL_GUY
Your code will probably copy to/from the same sheet (with the exception of the typo)

 

[THE_NEW_XL_GUY]

thanks for correcting me fluff. you know, Just trying to help out with my level of knowledge . can you just help with my thread.. pleaseee ?? @Fluff https://www.mrexcel.com/forum/excel...books-diff-names-through-browse-dialogue.html