PDA

View Full Version : [Excel] Data transfer to new file



Tomp
09-27-2008, 10:13 AM
1-I'm transfering data from one file to another and I have them both open in the window. (see attachment)

2-I have a series of macros that take data from the v4.2 file and copy it to the v4.3 file. Example from 1st macro below:
Sub Xfer_InfoCksData()
'
Windows("Prog_Area1_v4.2.xls").Activate
Application.Goto Reference:="UV_Contracts" 'Updates the list of contracts
Selection.Copy
Windows("Prog_Area1_v4.3.xls").Activate
Application.Goto Reference:="UV_Contracts"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Windows("Prog_Area1_v4.2.xls").Activate
Application.Goto Reference:="UV_ProjData01" 'Updates the Project Data Fields
Selection.Copy
Windows("Prog_Area1_v4.3.xls").Activate
Application.Goto Reference:="UV_ProjData01"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


3-There are a series of macros that make up the entire procedure. I have put them together like this:

Sub VersionUpdate_42_43()
' Data Transfer from v4.2 tp v4.3 Macro
' Macro recorded 5/15/2008 by per38916
'
Application.Run "Prog_Area1_v4.3.xls!DATA_Transfer_4x_4x"
Application.Run "Prog_Area1_v4.3.xls!Xfer_InfoCksData"
Application.Run "Prog_Area1_v4.3.xls!Xfer_ChartData"
Application.Run "Prog_Area1_v4.3.xls!Xfer_BasisData"
Application.Run "Prog_Area1_v4.3.xls!Xfer_QtyData"
Application.Run "Prog_Area1_v4.3.xls!Xfer_ProgressBaseData"
Application.Run "Prog_Area1_v4.3.xls!Xfer_GlobalPFAdj"
Application.Run "Prog_Area1_v4.3.xls!Xfer_HrsAvailable"
Application.Run "Prog_Area1_v4.3.xls!Xfer_OrigPlanData"
Application.Run "Prog_Area1_v4.3.xls!Xfer_CurrPlanData"
Application.Run "Prog_Area1_v4.3.xls!Xfer_FcstData"
Application.Run "Prog_Area1_v4.3.xls!Xfer_EarnedHrsData"
Application.Run "Prog_Area1_v4.3.xls!Xfer_ExpendedHrsData"
Application.Run "Prog_Area1_v4.3.xls!Xfer_PeriodPFAdj"
Application.Run "Prog_Area1_v4.3.xls!Data_Transfer_FinalMsg"
Application.Run "Prog_Area1_v4.3.xls!Data_Transfer_Close42"
End Sub


4-If I run an individual macros it takes about 2 seconds to run, works fine and runs fast enough that I can't follow it. The data transfers and there are no problems. If I run the procedure above, the copy/paste slows down to a snails pace and I can easily watch each excrutiatingly slow copy/paste. The next macro starts when the prev finishes but, the entire process slows down.
When I run this procedure, I can see that it is constantly recalculating the workbooks. Is this part of the problem? I can't seem to get the right "calculation=false" code to stop all calculations in both books till everything runs. Do you think this is the issue? What am I doing wrong????????

rbrhodes
09-27-2008, 02:08 PM
Ho Tomp,

First: Please enclose any posted code in VBA tags. Click on the Green/White Icon that says VBA. It's above this window. Put the code inside the bracketed tags.

Second: If possible post a small example of your actual sheet. Pdf's don't really describe the problem eg I had to recreate your sheets, etc.

Finally, the macro recorder is a great tool but is is literal. It activates every window, updates the screen and recalculates, etc this causing a major slowdown. The best way is to record the macro then edit it for efficiency (See my examples).

I've set up 2 variables for the Copy From/Copy To Worksheets then simply used Copy and paste - no selecting or activating screens, etc.

There is errorhandling, Calculation off and Screen refresh off commands as well (They are reset at the end of the Main Sub).

As always, try this code on a COPY of your real workbook. NOTE: You may want to edit all of your subs to reflect the Copy from ws1 to ws2 format of my example sub, however it's not imperative. If you simply turn off Screenupdating at the beginning of the main sub and restore it at the end I'm sure you'll see a marked improvement. Same for Calculation, Set it for Application.xlCalculationManual at the start and Application.xlCalculationAutomatic at the end of the main sub.

Study the example sub and let me know...