Copy data from one workbook to the other workbook sheet

Question

I am new to VBA excel. I am trying to copy the data on a worksheet from a workbook to the other worksheet on another workbook. The user choose the file they want to open through File Dialog. However, I keep get the error of Runtime error 9: Subscript out of range. Can someone please enlighten me. Thank you.

Sub selectfile()
Dim FileToOpen As String
Dim wb2 As Workbook
Dim wb1 As Workbook
Dim sheet As Worksheet
Application.ScreenUpdating = False
Sheet2.Range("A:Y").ClearContents
FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a Excel File to Open", _
FileFilter:="Excel Files *.xlsx(*.xlsx),")
Sheet1.Range("B30").Value = FileToOpen
Set wb2 = Workbooks.Open(FileToOpen)
Set sheet = wb2.Worksheets(1)
wb2.Activate
sheet.Copy After:=Workbooks("OQC_Check_Tools.xlsm").Sheets("Sheet2")

End Sub

does the error come at set wb2=workbooks.worksheets("Sheet1") line? or at workboos.open line? — Sivaprasath Vadivel
try the index of the sheet instead of name.....something like set sheet=wb2.worksheets(1) — Sivaprasath Vadivel
It works now but i want to copy the data from the file i opened to the workbook. How do i do that ? — Rachel Chia
sheet.Copy After:=Workbooks("OQC_Check_Tools.xlsm").Sheets("Sheet2") this line in your code might take care of that... I guess — Sivaprasath Vadivel

Sivaprasath Vadivel Sivaprasath Vadivel · Accepted Answer · 2017-04-07T07:26:35

Sub selectfile()
Dim FileToOpen As String
Dim wb2 As Workbook
Dim wb1 As Workbook
Dim sheet As Worksheet
Application.ScreenUpdating = False
Sheet2.Range("A:Y").ClearContents
FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a Excel File to Open", _
FileFilter:="Excel Files *.xlsx(*.xlsx),")
Sheet1.Range("B30").Value = FileToOpen
Set wb2 = Workbooks.Open(FileToOpen)
Set sheet = wb2.Worksheets(1)
sheet.usedrange.copy destination:=thisworkbook.worksheets(2).range("A1")

End Sub

try this code.

Copy data from one workbook to the other workbook sheet

1 Answers