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????????
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????????