Loop for Copying/ pasting data (batch process

My Cousin Vinny - Jul 13, 2009 at 09:48 AM
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 - Jul 13, 2009 at 11:26 PM

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
End Sub

Private Sub Calc_Log_Click()
Dim Counter As Integer
' 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

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()
' End Sub

' Sub PrintCalc()
' Print_Calc_Click

' Next vertcounter
Next Counter


End Sub


1 reply

Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307
Jul 13, 2009 at 11:26 PM

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