VBA Returns Error on Select Command

[davec8723]

Hi All,

I am new to VBA and to this forumn, but would really appreciate your help. I am writing a code which I want to:

1) prompt the user to select a file
2) Open this file, copy multiple ranges from this file
3) Paste these multiple ranges into multiple places in the file the code resides in
4) I will be doing this each month, therefore I would like to begin to build a reference table.

Below is my code. It always runs into an error where the text is red, which is a simple select command.

Sub Update_Data()
' All Comments Above relevant code
' Open Flash file
Dim FileName As Variant
Dim Flash As Workbook
FileName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls")
If FileName <> False Then
Set Flash = Workbooks.Open(FileName)
 
'Copy and Paste Bulk Data into ThisWorkbook
Flash.Sheets("Bulk Flash").Range("D16:D65").Copy
ThisWorkbook.Activate
Sheets("BULK Data").Activate
Range("A59").Select
ActiveCell.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Application.CutCopyMode = False
 
'Copy and Paste Pharma Data into ThisWorkbook
Flash.Sheets("Pharma Flash").Range("D16:D65").Copy
ThisWorkbook.Sheets("Pharma Data").Range("A59").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Application.CutCopyMode = False
 
'Copy and Paste Right Data into ThisWorkbook
Flash.Activate
Sheets("Right Flash").Range("D16:D65").Copy
ThisWorkbook.Activate
Sheets("RIGHT Data").Range("A59").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Application.CutCopyMode = False
 
'Copy and Paste HQ Data into ThisWorkbook
Flash.Activate
Sheets("HQ Flash").Range("D16:D65").Copy
ThisWorkbook.Activate
Sheets("HQ Data").Range("A59").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Application.CutCopyMode = False
'Copy and Paste Total Data into ThisWorkbook
Flash.Activate
Sheets("Total").Range("D16:D65").Copy
ThisWorkbook.Activate
Sheets("TOTAL Data").Range("A59").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Application.CutCopyMode = False
 
'Close Flash without saving changes
Flash.Close False
End If
End Sub

P.S. i know this code could probably be done in about 1/10th as many lines, but as I said, I am just learning

Any help would be greatly appreciated, as this is driving me nuts!

Thanks and have a nice weekend

 

[davec8723]

 With Workbooks.Open(Filename:=sFile, UpdateLinks=False)

VBA is rejecting this code. Maybe it has to be different since this line begins with "With"?

 

[Jon von der Heyden]

With Workbooks.Open(Filename:=sFile, UpdateLinks=False)
    'do stuff with da workbook
End With

 

[davec8723]

Thanks everyone! Works perfectly!