Refining code

[roi santos]

Hi

I have created a code for importing data from an excel sheet to the active workbook. The macro works fine but just take a bit too long, as I'm intending to copy several columns I think that this will become a problem. I wonder if you can give some advice on how to accelarate the macro; given its complexity it should not be taking so long.

Any thoughts?

Many thanks

Sub doCopies2()
Dim DestWB As Workbook: Set DestWB = ActiveWorkbook
Dim DestWS As Worksheet: Set DestWS = ActiveSheet
Dim DestCLL As Range: Set DestCLL = ActiveCell
Dim UserChoice
UserChoice = Application.GetOpenFilename(FileFilter:="Text Files (*.xls),*.xls")


If TypeName(UserChoice) = "Boolean" Then Exit Sub
Application.ScreenUpdating = False
Dim SrcWB As Workbook
Set SrcWB = Application.Workbooks.Open(UserChoice)
SrcWB.Worksheets(1).Range(Range("C50"), Range("C50").End(xlDown)).Copy

DestWS.Activate
DestCLL.Offset(1, 0).Activate
ActiveSheet.Paste

Application.DisplayAlerts = False
SrcWB.Close savechanges:=False
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

 

[BiocideJ]

If I understand the code correctly, it appears like the delay is coming from the file that Excel is opening and not from the process of copying. There really isn't a way that I know of to speed this up and the performance is going to depend on the size of the Excel file you are opening. (i.e. a bigger file size = slower macro performance)