Macro Help In Excel

W

welshlad

Every morning we have a file imported containing rows of data. There ar
a different amount of entries every day. I have managed to set up
macro that goes through each row individually highlighting it an
printing it off. Therefore, if I have an import with 20 rows, my macr
prints off 20 copies, with each row individually being highlighted.

My problem is that I can only do this if I firstly enter into the macr
how many rows of data there are. Is there any way the macro can wor
through the rows, and know when to stop?

Any advice will be gratefully received
 
R

Roger Govier

Hi

Assuming there is always date in column A of your sheet, then within your macro

Dim Counter as Long
Counter = Range("A65536").End(xlUp).Row

For i = 1 counter
your routine .....

Change he reference from A to another column if there is no data in column A.


Regards

Roger Govier
 
D

Don Guillett

As always, post your code for comments but to find the last row in col A

lastrow=cells(rows.count,"a").end(xlup).row
for i=2 to lastrow
your stuff
next i
 
W

welshlad

Thank you for your comments regarding a macro in excel registering how
many rows there are of data.
I took your advice but now this happens : - it registers how many rows
of data there are, but the shading does not move down.

Here's my macro : -

Sub importprint()
'
' importprint Macro
' Macro recorded 14/10/2005 by WP122
'

'
lastrow = Cells(Rows.Count, "a").End(xlUp).Row
For i = 2 To lastrow

With Selection.Interior
..ColorIndex = 15
..Pattern = xlSolid
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Selection.Interior.ColorIndex = xlNone

Next i
End Sub


(I want the macro to highlight the first row, highlight it, print it,
then de-highlight it, then move on to the second row and do the same
process etc etc.

Can you help at all?

Regards,

Luke
 
D

Don Guillett

still not quite sure what you want but try this. Why do you want to
highlight the row?
Change printpreview to printOUT to actually print

Sub importprint()
lastrow = Cells(Rows.Count, "a").End(xlUp).Row
For i = 2 To lastrow
With Rows(i)
..Interior.ColorIndex = 15
..PrintPreview
..Interior.ColorIndex = 0
End With
Next i
End Sub
 
W

welshlad

Want I want is for the first row to be shaded, then printed. Then the
first row to be deshaded, then shade the second row and print etc. The
macro realised how many rows I had but the shade only occured in the
row which the cursor was in when the macro was started.
 
D

Don Guillett

Did you try what i posted, as posted? If not col A, change.
Why did you put .. instead of . for the with statements?
 
W

welshlad

This is my macro now. It recognises how many rows of data there are,
prints that amount, which I want. But I want the first row to be
selected, shaded and printed, then unshaded, then the same with the
second row etc. The cursor still doesn't move though when the macro is
ran. Why? It's really frustrating me now.

Please help, any advice would be grateful.


Sub importprint()

'
lastrow = Cells(Rows.Count, "a").End(xlUp).Row
For i = 2 To lastrow
With Rows(i)
Selection.Interior.ColorIndex = 15
ActiveWindow.SelectedSheets.PrintPreview
Selection.Interior.ColorIndex = 0
End With
Next i
End Sub
 
D

Don Guillett

Professionals tend to avoid selections wherever possible. Why do you want to
move the cursor to the row to print>hilite>print>unhilite when just printing
each row should do nicely. I'm also not quite sure why you want to print
each row separately.???

This does what you want, WITHOUT selecting. Try it.

Sub importprint()
lastrow = Cells(Rows.Count, "a").End(xlUp).Row
For i = 2 To lastrow
With Rows(i)
..Interior.ColorIndex = 15
..PrintPreview
..Interior.ColorIndex = 0
End With
Next i
End Sub
 
W

welshlad

I copied and pasted your macro but it's not doing anything differently.
Yes it is registering how many rows there are and print previewing that
many entries, but the highlighting is remaining on the same cell.

Every day we have a list of client details. We print out a copy of the
list to put in to every client file. The row corresponding to the file
we highlight and put it into the front of the file. Therefore, if we
have 20 rows, we want 20 copies of the list, but with the individual
row highlighted.

Just cannot figure out why the macro does not go through the list
highlighting, whereas it just highlights, dehighlights, and
rehighlights the cell which the cursor was in immediately before
running the macro.

Ready to throw the computer now! hee hee

Luke
 
D

Don Guillett

I'm confused.
Again, what the macro does is hilite the row and print the ROW. Do you want
it to print all 20 rows with only the appropriate row hilited? If so, this
prints a1:d & last row

Sub importprint()
lastrow = Cells(Rows.Count, "a").End(xlUp).Row
For i = 2 To lastrow
With Rows(i)
..Interior.ColorIndex = 15
Range(Cells(1, 1), Cells(lastrow, 4)).PrintPreview
'.PrintPreview
..Interior.ColorIndex = 0
End With
Next i
End Sub
 
W

welshlad

The way you worded it is exactly what I want to happen, but somehow its
not happening. I am literally copying and pasting your macro, but all
it is doing is hiliting and deliting the only cell that the cursor is
in before the macro is ran. Any idea why this is?
 
D

Don Guillett

If you like you may send to my personal email a SMALL workbook along with
exactly what you are trying to do. This should NOT be rocket science.
 
D

Don Guillett

I got your workbook with the macro you provided which is NOT what I told you
to use. Use THIS to hilite each row and print the entire selection each time
with the next row hilited.

Sub importprint()
lastrow = Cells(Rows.Count, "a").End(xlUp).Row
For i = 2 To lastrow
With Rows(i)
..Interior.ColorIndex = 15
Range(Cells(1, 1), Cells(lastrow, 4)).PrintPreview
'.PrintPreview
..Interior.ColorIndex = 0
End With
Next i
End Sub
=======
you used this which is NOT what I sent.

Sub importprint1()
lastrow = Cells(Rows.Count, "a").End(xlUp).Row
For i = 2 To lastrow
With Rows(i)
Selection.Interior.ColorIndex = 15
Range(Cells(1, 1), Cells(lastrow, 4)).PrintPreview
ActiveWindow.SelectedSheets.PrintPreview
Selection.Interior.ColorIndex = 0
End With
Next i
End Sub
 
Top