Looping through rows of data

Change
EndRow = Range("A65536").End(xlUp).Select
in
EndRow = Range("A65536").End(xlUp).Row

Paul

Change the .Select in your xlUp line to .Row

You will probably also need to change

Selection.Paste to

ActiveSheet.Paste

Hope this helps,

Russell

(the .Select statement just evaluates to True, so EndRow = 1 and the loop is never entered).

OK. So I've changed it, and hey... it works. Xcept now it stops at Sheets("Data").Cells(counter, 4).Select with error 1004. DO I have to re-initialise the counter - can't see why? Sorry if this seems dumb - I've not done a huge amount of Excel VBA - but it is increasing.

Sub PrintForm()

'Step through rows of data one at a time

Dim EndRow, counter As Integer
Sheets("Data").Select
EndRow = Range("A65536").End(xlUp).Row

For counter = 1 To EndRow

Sheets("Data").Cells(counter, 1).Select
Selection.copy
Application.Goto Reference:="NAME"
ActiveSheet.Paste

Sheets("Data").Cells(counter, 4).Select
Selection.copy
Application.Goto Reference:="NI"
ActiveSheet.Paste

Sheets("Data").Cells(counter, 8).Select
Selection.copy
Application.Goto Reference:="CODE"
ActiveSheet.Paste

Sheets("Data").Cells(counter, 11).Select
Selection.copy
Application.Goto Reference:="ACCOUNT"
Selection.PasteSpecial Paste:=xlValues

ActiveWindow.SelectedSheets.PrintOut Copies:=1

MsgBox ("Continue")

Next counter

End Sub

Matt,

Not sure exactly why it would stop there. Are you sure you have a range named "NI"?

I copied your code and ran through it with no problem.

Incidentally, I changed your code somewhat:

Sub PrintForm()

'Step through rows of data one at a time

Dim EndRow As Integer, counter As Integer

Sheets("Data").Select
EndRow = Range("A65536").End(xlUp).Row

For counter = 1 To EndRow

Sheets("Data").Cells(counter, 1).Copy Destination:="NAME"

Sheets("Data").Cells(counter, 4).Copy Destination:="NI"

Sheets("Data").Cells(counter, 8).Copy Destination:="CODE"

Sheets("Data").Cells(counter, 11).Copy
Range("ACCOUNT").Select
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = xlCopy

ActiveWindow.SelectedSheets.PrintOut Copies:=1

MsgBox ("Continue")

Next counter

End Sub

1) As you have it, EndRow is not dimensioned as Integer. The change I show corrects that oversight.

2) Your code will run faster if you don't have Excel "select" stuff it doesn't have to. With a proper reference you can get to the same end, faster. Go through the code posted above and I think you'll see what i mean.

have fun

Sheets("Data").Cells(counter, 4).Select Application.Goto Reference:="NI" Application.Goto Reference:="CODE" Sheets("Data").Cells(counter, 11).Select Application.Goto Reference:="ACCOUNT" Selection.PasteSpecial Paste:=xlValues MsgBox ("Continue")