roi santos
Board Regular
- Joined
- Oct 3, 2008
- Messages
- 57
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
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