Copy data from source and return to main wb

Question

SUB Copy_paste_ data()

 Dim source as string

 Dim template as Thisworkbook 

 Msgbox "Open the BLI RAW DATA WORKBOOK",,"BLI"

Source = Application.GetopenFilename()

Workbooks.open (source)

Range("A1"). select

Rows("3:4").Select

Selection.Delete shift:xlup

Range("A1"). select

Range("A4:F4"). select

Range(Selection, Selection.End(xlDown)). select

Selection.copy

Thisworkbook.activate

Sheets("Sheet1").select

Range("A1").Select

Selection.paste, paste:=xlpastevalues

End sub

I couldn't activate previous workbook run time error 9 subscript out of range is occuring kindly give me a solution

Answer 1

Copy Values of a Range to Another Workbook

Option Explicit

Sub Copy_paste_data()

    MsgBox "Open the BLI RAW DATA WORKBOOK", , "BLI"
    
    ' Source
    
    Dim swbPath As String: swbPath = Application.GetOpenFilename()
    If swbPath = "False" Then
        MsgBox "You canceled.", vbExclamation
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
    
    Dim swb As Workbook: Set swb = Workbooks.Open(swbPath)
    Dim sws As Worksheet: Set sws = swb.Worksheets("Sheet1") ' adjust!
    
    sws.Rows("3:4").Delete xlShiftUp
    
    Dim srg As Range: Set srg = sws.Range(sws.Range("A4:F4").Cells, _
        sws.Range("A4:F4").End(xlDown))
    
    ' Destination
    
    Dim dwb As Workbook: Set dwb = ThisWorkbook ' workbook containing this code
    Dim dws As Worksheet: Set dws = dwb.Worksheets("Sheet1")

    Dim drg As Range
    Set drg = dws.Range("A1").Resize(srg.Rows.Count, srg.Columns.Count)
    
    ' Copy by assignment (most efficient).
    
    drg.Value = srg.Value
        
    ' Save and Close
    
    swb.Close SaveChanges:=False ' set to 'True' if you want to save
    'dwb.Save
    
    ' Inform.
    Application.ScreenUpdating = True
    MsgBox "Data copied.", vbInformation
    
End Sub