Long time of calculating

[mmenofy]

Hi everybody,
I am using the following code to copy files from my computer to a shared folder in my network, but when i am using it, it tooks a very very long time in calculating (about 15-20 min.) each time the sheet is opening (as it reads and calculate all the data each opening time - (about 10,000 rows)
any support ?

Sub Copy_Files()

Dim cell As Range
Dim sFolderPath As String
Dim FTC As String
Dim APPRV As String
Dim oFSO As Object
Dim XLS As Object

Set XLS = ActiveWorkbook.Sheets("TO SERVER")
For Each cell In XLS.Range("I4", XLS.Range("I" & Rows.Count).End(xlUp))

sFolderPath = cell.Offset(, 1).Value
FTC = cell.Offset(, 3).Value
APPRV = cell.Offset(, 4).Value
On Error Resume Next

'Check Specified Folder exists or not
If Dir(sFolderPath) <> "" Then
'If file is available
FileCopy Source:=cell.Value, Destination:=cell.Offset(, 2).Value
FileCopy Source:=cell.Value, Destination:=cell.Offset(, 5).Value
Kill cell.Value
Worksheets("TO SERVER").Calculate
End If
'If folder is not available
MkDir sFolderPath
MkDir FTC
MkDir APPRV
FileCopy Source:=cell.Value, Destination:=cell.Offset(, 2).Value
FileCopy Source:=cell.Value, Destination:=cell.Offset(, 5).Value
Kill cell.Value
Worksheets("TO SERVER").Calculate
On Error Resume Next
Next cell
End Sub

 

[SpillerBD]

You should first try adding

Application.ScreenUpdating = False

to beginning of code and

Application.ScreenUpdating = True

to end of your Code.

However, you will likely see your macro runs slow still because of the fie operations.

 

[johnnyL]

@mmenofy Can you explain what you are wanting the code to do?

I ask because it appears that the code is Doing the same task twice when the folder exists.

Could you provide a sample of the data that you are using in Sheets("TO SERVER")? Ex. what you have in columns I, J, k,etc.

 

[mmenofy]

@mmenofy Can you explain what you are wanting the code to do?

I ask because it appears that the code is Doing the same task twice when the folder exists.

Could you provide a sample of the data that you are using in Sheets("TO SERVER")? Ex. what you have in columns I, J, k,etc.

- I got alot of mails with attachments, and i need to save these attachments (based on mail's subject) in a specific folder (each related to specific location on a shared folder)
- Sheet (TO SERVER) is generating the path for the location supposed to used in saving the mentioned attachments (also based on email's subject)

when i tried all of this using only few rows in (TO SERVER) sheet, it works perfectly, but when i extended the equation in this sheet to more rows (10,000) it hangs when mail come