Run a macro batch process

[marijo]

Hello,

I would like to ask for your help in creating a macro to run a batc
process, specifically doing the following:

1. Copy from Clients worksheet cell B2, then Paste Special (value)int
cell E12 of Forms worksheet.
2. Copy value from Clients worksheet cell C2, then Paste Specia
(value) into cell E14 of Forms worksheet.
3. Copy value from Clients worksheet cell D2, then Paste Specia
(value) into cell E17 of Forms worksheet.
4. Copy value from Clients worksheet cell E2, then Paste Specia
(value) into cell E19 of Forms worksheet.

(The spreadsheet will now be able to compute for the needed analysi
once all the four (4) entries above have been placed.)

5. Copy value from Computations worksheet cell CQ, then Paste Specia
(value) into cell F2 of Clients worksheet.
6. Copy value from Computations worksheet cell B6, then Paste Specia
(value) into cell G2 of Clients worksheet.

Now, the spreadsheet must be able to *save* the computed values paste
in both cells F2 and G2 of Clients worksheet for the first client, eve
if the requirement now is to *clear* its information from the Form
worksheet so that it can now compute for the second client doing th
same process (this time obtaining information from the 3rd row o
Clients worksheet). It will capture the results then perform simila
operation for n # of iterations until it finds the last client (as fo
my case, it'll be the 3,000th client).

Also will there be a problem to run this batch process as far a
accuracy of computations is concerned? For when I do this manually i
takes excel 97 about 10-15 seconds as it refreshes to calculate pe
client.

I currently have 3,000 clients that I need to plot using their require
computed values. How do I create this macro to run computations for al
these clients on a one-time-basis?

Really appreciate all the help you can give in resolving this.

Marij

 

[Vasant Nanavati]

Perhaps you should use the macro recorder for the first few iterations; then
post the resulting code for improvement suggestions.

 

[marijo]

Hi Vasant,

Here's the code for steps 1-6.

Sub cp()
'
' cp Macro
' Macro recorded 6/2/04 by Marijo
'
' Keyboard Shortcut: Ctrl+z
'
Selection.Copy
Sheets("TestPointInfo").Select
Range("E12").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
Sheets("Clients").Select
Range("C2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("TestPointInfo").Select
Range("E14").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
Sheets("Clients").Select
Range("D2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("TestPointInfo").Select
Range("E17").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
Sheets("Clients").Select
Range("E2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("TestPointInfo").Select
Range("E19").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
Sheets("RSL_CtoI").Select
ActiveWindow.SmallScroll ToRight:=32
Range("CQ8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Clients").Select
Range("F2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
Sheets("RSL_CtoI").Select
Range("B6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Clients").Select
Range("G2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
End Sub

How do I now save the computed values of the first client whil
clearing the form to compute for the next client? How do I retain th
computed values for each client assuming the same calculation proces
is done for 3,000 clients?

I am new to VBA programming. Would really appreciate it if you coul
explain in more detail.

Thanks,
Marij