Macro Help In Excel

Every morning we have a file imported containing rows of data. There are
a different amount of entries every day. I have managed to set up a
macro that goes through each row individually highlighting it and
printing it off. Therefore, if I have an import with 20 rows, my macro
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 macro
how many rows of data there are. Is there any way the macro can work
through the rows, and know when to stop?

Any advice will be gratefully received.


--
welshlad

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


welshlad wrote:
> Every morning we have a file imported containing rows of data. There are
> a different amount of entries every day. I have managed to set up a
> macro that goes through each row individually highlighting it and
> printing it off. Therefore, if I have an import with 20 rows, my macro
> 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 macro
> how many rows of data there are. Is there any way the macro can work
> through the rows, and know when to stop?
>
> Any advice will be gratefully received.
>
>

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
--
Don Guillett
SalesAid Software
donaldb@281.com
"welshlad" <welshlad.1x3thn@news.officefrustration.com> wrote in message
news:welshlad.1x3thn@news.officefrustration.com...
>
> Every morning we have a file imported containing rows of data. There are
> a different amount of entries every day. I have managed to set up a
> macro that goes through each row individually highlighting it and
> printing it off. Therefore, if I have an import with 20 rows, my macro
> 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 macro
> how many rows of data there are. Is there any way the macro can work
> through the rows, and know when to stop?
>
> Any advice will be gratefully received.
>
>
> --
> 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

Don Guillett Wrote:
> 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
> --
> Don Guillett
> SalesAid Software
> donaldb@281.com
> "welshlad" welshlad.1x3thn@news.officefrustration.com wrote in message
> news:welshlad.1x3thn@news.officefrustration.com...
>
> Every morning we have a file imported containing rows of data. There
> are
> a different amount of entries every day. I have managed to set up a
> macro that goes through each row individually highlighting it and
> printing it off. Therefore, if I have an import with 20 rows, my
> macro
> 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
> macro
> how many rows of data there are. Is there any way the macro can work
> through the rows, and know when to stop?
>
> Any advice will be gratefully received.
>
>
> --
> welshlad


--
welshlad

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

--
Don Guillett
SalesAid Software
donaldb@281.com
"welshlad" <welshlad.1x57ho@news.officefrustration.com> wrote in message
news:welshlad.1x57ho@news.officefrustration.com...
>
> 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
>
> Don Guillett Wrote:
> > 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
> > --
> > Don Guillett
> > SalesAid Software
> > donaldb@281.com
> > "welshlad" welshlad.1x3thn@news.officefrustration.com wrote in message
> > news:welshlad.1x3thn@news.officefrustration.com...
> >
> > Every morning we have a file imported containing rows of data. There
> > are
> > a different amount of entries every day. I have managed to set up a
> > macro that goes through each row individually highlighting it and
> > printing it off. Therefore, if I have an import with 20 rows, my
> > macro
> > 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
> > macro
> > how many rows of data there are. Is there any way the macro can work
> > through the rows, and know when to stop?
> >
> > Any advice will be gratefully received.
> >
> >
> > --
> > welshlad
>
>
> --
> 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.

Don Guillett Wrote:
> 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
>
> --
> Don Guillett
> SalesAid Software
> donaldb@281.com
> "welshlad" welshlad.1x57ho@news.officefrustration.com wrote in message
> news:welshlad.1x57ho@news.officefrustration.com...
>
> 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
>
> Don Guillett Wrote:
> 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
> --
> Don Guillett
> SalesAid Software
> donaldb@281.com
> "welshlad" welshlad.1x3thn@news.officefrustration.com wrote in
> message
> news:welshlad.1x3thn@news.officefrustration.com...
>
> Every morning we have a file imported containing rows of data. There
> are
> a different amount of entries every day. I have managed to set up a
> macro that goes through each row individually highlighting it and
> printing it off. Therefore, if I have an import with 20 rows, my
> macro
> 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
> macro
> how many rows of data there are. Is there any way the macro can work
> through the rows, and know when to stop?
>
> Any advice will be gratefully received.
>
>
> --
> welshlad
>
>
> --
> welshlad


--
welshlad

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

--
Don Guillett
SalesAid Software
donaldb@281.com
"welshlad" <welshlad.1x64tr@news.officefrustration.com> wrote in message
news:welshlad.1x64tr@news.officefrustration.com...
>
> 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.
>
> Don Guillett Wrote:
> > 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
> >
> > --
> > Don Guillett
> > SalesAid Software
> > donaldb@281.com
> > "welshlad" welshlad.1x57ho@news.officefrustration.com wrote in message
> > news:welshlad.1x57ho@news.officefrustration.com...
> >
> > 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
> >
> > Don Guillett Wrote:
> > 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
> > --
> > Don Guillett
> > SalesAid Software
> > donaldb@281.com
> > "welshlad" welshlad.1x3thn@news.officefrustration.com wrote in
> > message
> > news:welshlad.1x3thn@news.officefrustration.com...
> >
> > Every morning we have a file imported containing rows of data. There
> > are
> > a different amount of entries every day. I have managed to set up a
> > macro that goes through each row individually highlighting it and
> > printing it off. Therefore, if I have an import with 20 rows, my
> > macro
> > 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
> > macro
> > how many rows of data there are. Is there any way the macro can work
> > through the rows, and know when to stop?
> >
> > Any advice will be gratefully received.
> >
> >
> > --
> > welshlad
> >
> >
> > --
> > welshlad
>
>
> --
> welshlad