Copy data from one workbook into another

drop05

Active Member
Joined
Mar 23, 2021
Messages
285
Office Version
  1. 365
Platform
  1. Windows
Hi i am trying to see if there is a way to copy data from one workbook to anther with the user being able to select the file to grab from and the sheet is sheet 1 and the only sheet in that workbook.
The range of the data to look and grab from is from A3 to the last used row in column A but goes to G. So A3: G last row used in A. Then paste the data into a table in the workbook wanting to bring the data over, the table is called "User_Report" the tab is called "User Report" and has headers so the data would go from A2 to G2 and down based on the copied data
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Try:
VBA Code:
Sub CopyRange()
    Application.ScreenUpdating = False
    Dim OpenFileName As String, srcWS As Worksheet
    Set srcWS = ThisWorkbook.Sheets("Sheet1")
    OpenFileName = Application.GetOpenFilename("Excel files (*.xls*), *.xls*")
    If OpenFileName = "False" Then Exit Sub
    srcWS.Range("A3", srcWS.Range("G" & Rows.Count).End(xlUp)).Copy Sheets("User Report").Range("A2")
    ActiveWorkbook.Close False
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Try:
VBA Code:
Sub CopyRange()
    Application.ScreenUpdating = False
    Dim OpenFileName As String, srcWS As Worksheet
    Set srcWS = ThisWorkbook.Sheets("Sheet1")
    OpenFileName = Application.GetOpenFilename("Excel files (*.xls*), *.xls*")
    If OpenFileName = "False" Then Exit Sub
    srcWS.Range("A3", srcWS.Range("G" & Rows.Count).End(xlUp)).Copy Sheets("User Report").Range("A2")
    ActiveWorkbook.Close False
    Application.ScreenUpdating = True
End Sub
HI thank you for this, quick questions, why is there set srcWS set to this workbook? Trying to get it from an outside workbook but that outside workbook the sheet name is sheet 1
 
Upvote 0
Thisworkbook refers to the workbook containing the macro and the range that you want to copy.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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