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

 

[kpark91]

Is it just me or am I not seeing the red text? lol

 

[davec8723]

Sorry you are right, it took the formating away when I put it in the code box. It is the first occurance of the line:

"ThisWorkbook.Activate
Sheets("BULK Data").Activate
Range("A59").Select"

The error actually occurs on the red line.

Thanks

Dave

 

[njimack]

Can't see any obvious errors. As a general rule, don't select sheets or cells. For instance, this:

Range("A59").Select 
ActiveCell.End(xlToRight).Select 
ActiveCell.Offset(0, 1).
Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

could be written as:

Range("A59").End(xlToRight).Offset(0,1).PasteSpecial xlValues

 

[davec8723]

I knew I could put some of them together, but I didnt realize I could shorten the code that much! Thanks will do! If anyone else has any tips I would be happy to hear them.

 

[kpark91]

Well. just repost the code after you're done cleaning up the code.
It'll be much easier to identify the problem.

1 post away from 1000 posts :D

 

[davec8723]

Sorry about that, here you go.

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.Sheets("BULK Data").Range("A59").End(xlToRight).Offset(0, 1).PasteSpecial xlValues
Application.CutCopyMode = False
 
'Copy and Paste Pharma Data into ThisWorkbook
Flash.Sheets("Bulk Flash").Range("D16:D65").Copy
ThisWorkbook.Sheets("BULK Data").Range("A59").End(xlToRight).Offset(0, 1).PasteSpecial xlValues
Application.CutCopyMode = False
 
'Copy and Paste Right Data into ThisWorkbook
Flash.Sheets("Right Flash").Range("D16:D65").Copy
ThisWorkbook.Sheets("RIGHT Data").Range("A59").End(xlToRight).Offset(0, 1).PasteSpecial xlValues
Application.CutCopyMode = False
 
'Copy and Paste HQ Data into ThisWorkbook
Flash.Sheets("HQ Flash").Range("D16:D65").Copy
ThisWorkbook.Sheets("HQ Data").Range("A59").End(xlToRight).Offset(0, 1).PasteSpecial xlValues
Application.CutCopyMode = False
'Copy and Paste Total Data into ThisWorkbook
Flash.Sheets("Total Flash").Range("D16:D65").Copy
ThisWorkbook.Sheets("TOTAL Data").Range("A59").End(xlToRight).Offset(0, 1).PasteSpecial xlValues
Application.CutCopyMode = False
 
'Close Flash without saving changes
Flash.Close False
End If
End Sub

 

[shg]

Maybe ...

Sub Update_Data()
    Dim sFile       As Variant
    Dim vs          As Variant
 
    sFile = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls")
    If sFile <> "False" Then
        With Workbooks.Open(sFile)
            For Each vs In Array("Bulk ", "Right ", "HQ ", "Total ")
                .Worksheets(vs & "Flash").Range("D16:D65").Copy
                ThisWorkbook.Worksheets(vs & "Data").Range("A59").End(xlToRight).Offset(0, 1).PasteSpecial xlValues
            Next vs
            .Close SaveChanges:=False
        End With
    End If
End Sub

 

[davec8723]

Wow, thank you very much, that is impressive! I sure do have alot to learn. Would anyone be able to tell me what to add to the open line to have it not ask about updating the links?

Thanks,

Dave

 

[Jon von der Heyden]

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