PDA

View Full Version : VBA rnus slow in Office 2007



udigold1
12-30-2008, 04:07 AM
Hi,

I have this following code, which meant to import data from one file to another file. It runs pretty quick in Office 2003, but terribly slow in office 2007. Why?



Sub ShlifaData(s As String)
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Fname = ActiveWindow.Caption
ph = Range("Path" & s)
fl = Range("File" & s)
sh = Range("Sheet" & s)
shc = ActiveSheet.Name
Sheets("Data" & s).Select

If Cells(Range("output" & s).Row + 1, Range("formula" & s).Column) <> "" Then _
Range(Cells(Range("output" & s).Row + 1, Range("formula" & s).Column), _
Cells(Range("output" & s).End(xlDown).Row, Range("formula" & s).Column + Range("formula" & s).Columns.Count - 1)).Clear
Application.StatusBar = "Open " & ph & "\" & fl
Workbooks.Open Filename:=ph & "\" & fl
'ActiveSheet.Name = sh
Sheets(sh).Activate
inp = Range(Range("output" & s), Range("output" & s).End(xlToRight).End(xlDown)).Address
Windows(Fname).Activate

Workbooks(fl).Sheets(sh).Range(inp).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Range("output" & s), Unique:=False
inp = Range(Cells(Range("output" & s).Row, Range("output" & s).Column), _
Cells(Range("output" & s).End(xlDown).Row, Range("output" & s).Column + Range("output" & s).Columns.Count - 1)).Address
If Cells(Range("output" & s).Row + 1, Range("output" & s).Column) <> "" Then Call CopyFormula(s)
Application.CutCopyMode = False
Windows(fl).Close savechanges:=False
Sheets(shc).Select
Application.StatusBar = False
End Sub


Thanks,
Udi

Artik
12-30-2008, 05:55 AM
1. But what is in procedure CopyFormula?
2. For what it wanted?:inp = Range(Cells(Range("output" & s).Row, Range("output" & s).Column), _
Cells(Range("output" & s).End(xlDown).Row, Range("output" & s).Column + Range("output" & s).Columns.Count - 1)).AddressYou do not use with it further.


I apologize for my english language.

Artik

udigold1
12-30-2008, 06:08 AM
CopyFormula is :

Sub CopyFormula(s As String)
Application.StatusBar = "formula" & s
Range("formula" & s).Copy
Range(Cells(Range("output" & s).Row + 1, Range("formula" & s).Column), _
Cells(Range("output" & s).End(xlDown).Row, Range("formula" & s).Column + Range("formula" & s).Columns.Count - 1)).Select
Selection.PasteSpecial xlAll
Selection.Copy
Selection.PasteSpecial xlValues
Application.CutCopyMode = False
Application.StatusBar = False
End Sub

The inp = ... is redundant so I have just removed it from the code, but it didn't shorten the time it takes.