CODEWITHSUNDEEP
excelvba
Rachel Chia
Rachel Chia

Reputation: 259

Copy data from one workbook to the other workbook sheet

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

Upvotes: 1

Views: 205

Answers (1)

Sivaprasath Vadivel
Sivaprasath Vadivel

Reputation: 541

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.

Upvotes: 0

Related Questions