Copying a range from a different excell sheet to a master sheet linked to another sheet for outlook vba

[mualh1999]

hi,

I'm trying to open file explorer and copy a range of cells from a xls file and paste them to a range of cells in my master sheet. I have another tap that is setup with outlook vba code and I don't want it to get effected considering the the information transfers over to it from the master sheet. I'm using the code below and I'm getting compile errors left and right.

Option Explicit
Sub test()
Dim wb As Workbook, wb2 As Workbook
Dim ws As Worksheet
Dim vFile As Variant

'Open the target workbook
vFile = Application.GetOpenFilename("Excel-files,*.xls", _
1, "Select One File To Open", , False)
'if the user didn't select a file, exit sub
If TypeName(vFile) = "Boolean" Then Exit Sub
Workbooks.Open vFile
'Set selectedworkbook
Set wb2 = ActiveWorkbook
ActiveSheets.Range("A9:L65536").Copy
Workbook("SurveysSheet").Activate
ActiveWorkbook.Sheets("SURVEYSHEET").Range("A17").Paste
End Sub

 

[AlphaFrog]

[color=darkblue]Sub[/color] test()
    
    [color=darkblue]Dim[/color] wb2       [color=darkblue]As[/color] Workbook
    [color=darkblue]Dim[/color] vFile     [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    
    [color=green]'Open the target workbook[/color]
    vFile = Application.GetOpenFilename("Excel-files,*.xls", _
            1, "Select One File To Open", , [color=darkblue]False[/color])
    [color=green]'if the user didn't select a file, exit sub[/color]
    [color=darkblue]If[/color] TypeName(vFile) = "Boolean" [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    [color=darkblue]Set[/color] wb2 = Workbooks.Open(vFile)
    [color=green]'Set selectedworkbook[/color]
    [color=darkblue]With[/color] wb2.Sheets(1)
        .Range("A9:L" & .Range("A" & .Rows.Count).End(xlUp).Row).Copy
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    ThisWorkbook.Sheets("SURVEYSHEET").Range("A17").PasteSpecial xlPasteValues
    
End [color=darkblue]Sub[/color]