Loop for Copying/ pasting data (batch process

Closed
-
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
-
Hello,

I have a worksheet that has 3 tabs. The first tab stores all the personal employee data I need to run pension calculations for Employees. The first column is used as the "Index number" for each line that will be used in the process. The index number is increasing as more data is added. One data line per employee.

The second Tab pulls that data (from the first tab) into various cells using vlookup & the Index number and calculates pension amounts. The calculation is summarized on to row #2.

The Third Tab will be used to store the results (and eventually used for a WORD Merge) by copying row #2 from the 2nd TAB and pasting it on to the next line in third Tab. Within this spreadsheet, I have defined a cell called the "Counter". Its job is to count the # of lines that have been used so that the next calculation can be pasted on to the next empty line.

I have also created 3 button on the 2nd TAB that can 1) print calculations on a one-off basis when I enter the index number to be used; 2) "log" the information onto the third TAB. As each calculation is run, the results gets saved on to the next line; 3) "batch run" so that I can run the same procedure for 200 employees. The idea is that I would be using the increasing Index number to loop through to the next record to run.

Currently the print button works, but I get a run time error when I'm trying to Log the information. This is what I have for coding right now:

***************************************

Private Sub Print_Calc_Click()
' Print Calculation Page Macro
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("Management").Select
End Sub
***************************************

Private Sub Calc_Log_Click()
Dim Counter As Integer
Worksheets("Management").Range("2:2").Copy
' Sheets("Management").Select
' Rows("2:2").Select
' Selection.Copy
Sheets("Merge Log").Select
For Counter = 2 To 2000
Cells(Range("Counter") + 3, 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
' Cells(Range("Counter") + 1, 1).Select
Sheets("Management").Select
Range("G6").Select

Next Counter

End Sub
***************************************

Private Sub Run_Statement_Click()
Dim Counter As Integer

For Counter = 2 To 2000
'For vertcounter = 2

While (vertcounter < 2000)

Sheets("Merge Log").Select
Range("Index").Value = vertcounter

' Sub Calc_Log_Click()
Calc_Log_Click
' End Sub

' Sub PrintCalc()
' Print_Calc_Click

' Next vertcounter
Next Counter


Wend

End Sub
***************************************

Help!!!

1 reply

Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
Hello

try changing all Range("Counter") to just Counter without any quotes