My macro won't find the last row !

[Roger]

I am reposting this as I cannot get a reply to my problem. The following
macro is meant to go to the last row and then on the next row (lr+1) make a
totals row. But when I run the macro I get an error message
"application-defined or object-defined error" and it
does to the line

..Range(Cells(lr, "h"), Cells(lr, "o")).Borders.LineStyle = xlContinuous

and after as the problem code.

Can someone help me please ?

> Sub MakeTotals()'

> ' Make Totals for Report (zonderArb) Macro
> ' Macro recorded 15/06/02 by RAO
> ' Keyboard Shortcut: Ctrl+Shift+T
> ' clears borders from rows below total line (row 460) for clean printing
> ' creates SUM formula and then copies across total line
> ' creates borders and formats 0.00 where appropriate
> ' saves worksheet

> lr=cells(rows.count,"H").end(xlup).row+1
> Worksheets("Report (zonderArb)").Rows("460:660").Borders.linestyle =
> xlNone
> with workSheets("Report (zonderArb)")
> . Range("H" & lr).formula="=sum(h1:h" & lr & ")"
> . Range("H" & lr).Copy Range(cells(lr,"I"),cells(lr,"ae"))
> . Range(cells(lr,"A"),cells(lr,"g")).borders.LineStyle = xlContinuous
> . Range(cells(lr,"h"),cells(lr,"o")).borders.LineStyle = xlContinuous
> . Range(cells(lr,"p"),cells(lr,"w")).borders.LineStyle = xlContinuous
> . Range(cells(lr,"x"),cells(lr,"ae")).borders.LineStyle = xlContinuous
> . Range("AF" & lr).").borders.LineStyle = xlContinuous
>
> . Range(cells(l4,"M"),cells(lr,"n")).NumberFormat = "0.00"
> . Range(cells(l4,"u"),cells(lr,"v")).NumberFormat = "0.00"
> . Range(cells(l4,"ac"),cells(lr,"ad")).NumberFormat = "0.00"
>
> . Range("G" & LR)= "Totals"
>
> With . Range("G" & LR).Characters(Start:=1, Length:=7).Font
> .Name = "Arial"
> .FontStyle = "Regular"
> .Size = 8
> .Strikethrough = False
> .Superscript = False
> .Subscript = False
> .OutlineFont = False
> .Shadow = False
> .Underline = xlUnderlineStyleNone
> .ColorIndex = 1
> End With
>
> end with
>
> 'Sheets("Instructions").Select
> ' Range("K61").Select
> ActiveWorkbook.Save
> End Sub
>
>
>

[Chip Pearson]

You have not assigned a value to the l4 variable.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Roger" <roger46@skynet.be> wrote in message
news:u07ajqsnGHA.2312@TK2MSFTNGP04.phx.gbl...
>I am reposting this as I cannot get a reply to my problem. The
>following macro is meant to go to the last row and then on the
>next row (lr+1) make a totals row. But when I run the macro I
>get an error message "application-defined or object-defined
>error" and it
> does to the line
>
> .Range(Cells(lr, "h"), Cells(lr, "o")).Borders.LineStyle =
> xlContinuous
>
> and after as the problem code.
>
> Can someone help me please ?
>
>> Sub MakeTotals()'
>
>> ' Make Totals for Report (zonderArb) Macro
>> ' Macro recorded 15/06/02 by RAO
>> ' Keyboard Shortcut: Ctrl+Shift+T
>> ' clears borders from rows below total line (row 460) for
>> clean printing
>> ' creates SUM formula and then copies across total line
>> ' creates borders and formats 0.00 where appropriate
>> ' saves worksheet
>
>> lr=cells(rows.count,"H").end(xlup).row+1
>> Worksheets("Report
>> (zonderArb)").Rows("460:660").Borders.linestyle =
>> xlNone
>> with workSheets("Report (zonderArb)")
>> . Range("H" & lr).formula="=sum(h1:h" & lr & ")"
>> . Range("H" & lr).Copy Range(cells(lr,"I"),cells(lr,"ae"))
>> . Range(cells(lr,"A"),cells(lr,"g")).borders.LineStyle =
>> xlContinuous
>> . Range(cells(lr,"h"),cells(lr,"o")).borders.LineStyle =
>> xlContinuous
>> . Range(cells(lr,"p"),cells(lr,"w")).borders.LineStyle =
>> xlContinuous
>> . Range(cells(lr,"x"),cells(lr,"ae")).borders.LineStyle =
>> xlContinuous
>> . Range("AF" & lr).").borders.LineStyle = xlContinuous
>>
>> . Range(cells(l4,"M"),cells(lr,"n")).NumberFormat = "0.00"
>> . Range(cells(l4,"u"),cells(lr,"v")).NumberFormat = "0.00"
>> . Range(cells(l4,"ac"),cells(lr,"ad")).NumberFormat =
>> "0.00"
>>
>> . Range("G" & LR)= "Totals"
>>
>> With . Range("G" & LR).Characters(Start:=1,
>> Length:=7).Font
>> .Name = "Arial"
>> .FontStyle = "Regular"
>> .Size = 8
>> .Strikethrough = False
>> .Superscript = False
>> .Subscript = False
>> .OutlineFont = False
>> .Shadow = False
>> .Underline = xlUnderlineStyleNone
>> .ColorIndex = 1
>> End With
>>
>> end with
>>
>> 'Sheets("Instructions").Select
>> ' Range("K61").Select
>> ActiveWorkbook.Save
>> End Sub
>>
>>
>>
>
>

[Norman Jones]

Hi Roger,

In addition to Chip's response

Change:

>> . Range("AF" & lr).").borders.LineStyle = xlContinuous

to

.Range("AF" & lr).Borders.LineStyle = xlContinuous

Additionally, the line

>> . Range("H" & lr).formula="=sum(h1:h" & lr & ")"

will create a crrcular reference. Try, instead:

..Range("H" & lr).Formula = "=sum(h1:h" & lr - 1 & ")"

Incrporating these change, try:


---
Regards,
Norman

[Don Guillett]

It probably should have been LR. Typo. Probably mine when I hurriedly
provided the code originally.

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Chip Pearson" <chip@cpearson.com> wrote in message
news:e1Tyb1snGHA.3348@TK2MSFTNGP03.phx.gbl...
> You have not assigned a value to the l4 variable.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> "Roger" <roger46@skynet.be> wrote in message
> news:u07ajqsnGHA.2312@TK2MSFTNGP04.phx.gbl...
>>I am reposting this as I cannot get a reply to my problem. The following
>>macro is meant to go to the last row and then on the next row (lr+1) make
>>a totals row. But when I run the macro I get an error message
>>"application-defined or object-defined error" and it
>> does to the line
>>
>> .Range(Cells(lr, "h"), Cells(lr, "o")).Borders.LineStyle = xlContinuous
>>
>> and after as the problem code.
>>
>> Can someone help me please ?
>>
>>> Sub MakeTotals()'
>>
>>> ' Make Totals for Report (zonderArb) Macro
>>> ' Macro recorded 15/06/02 by RAO
>>> ' Keyboard Shortcut: Ctrl+Shift+T
>>> ' clears borders from rows below total line (row 460) for clean printing
>>> ' creates SUM formula and then copies across total line
>>> ' creates borders and formats 0.00 where appropriate
>>> ' saves worksheet
>>
>>> lr=cells(rows.count,"H").end(xlup).row+1
>>> Worksheets("Report (zonderArb)").Rows("460:660").Borders.linestyle =
>>> xlNone
>>> with workSheets("Report (zonderArb)")
>>> . Range("H" & lr).formula="=sum(h1:h" & lr & ")"
>>> . Range("H" & lr).Copy Range(cells(lr,"I"),cells(lr,"ae"))
>>> . Range(cells(lr,"A"),cells(lr,"g")).borders.LineStyle = xlContinuous
>>> . Range(cells(lr,"h"),cells(lr,"o")).borders.LineStyle = xlContinuous
>>> . Range(cells(lr,"p"),cells(lr,"w")).borders.LineStyle = xlContinuous
>>> . Range(cells(lr,"x"),cells(lr,"ae")).borders.LineStyle =
>>> xlContinuous
>>> . Range("AF" & lr).").borders.LineStyle = xlContinuous
>>>
>>> . Range(cells(l4,"M"),cells(lr,"n")).NumberFormat = "0.00"
>>> . Range(cells(l4,"u"),cells(lr,"v")).NumberFormat = "0.00"
>>> . Range(cells(l4,"ac"),cells(lr,"ad")).NumberFormat = "0.00"
>>>
>>> . Range("G" & LR)= "Totals"
>>>
>>> With . Range("G" & LR).Characters(Start:=1, Length:=7).Font
>>> .Name = "Arial"
>>> .FontStyle = "Regular"
>>> .Size = 8
>>> .Strikethrough = False
>>> .Superscript = False
>>> .Subscript = False
>>> .OutlineFont = False
>>> .Shadow = False
>>> .Underline = xlUnderlineStyleNone
>>> .ColorIndex = 1
>>> End With
>>>
>>> end with
>>>
>>> 'Sheets("Instructions").Select
>>> ' Range("K61").Select
>>> ActiveWorkbook.Save
>>> End Sub
>>>
>>>
>>>
>>
>>
>
>

[JMB]

You might want to double check your cell references

with workSheets("Report (zonderArb)")
.......
......
.Range(cells(lr,"h"),cells(lr,"o")).borders.LineStyle = xlContinuous
End With

If this worksheet is not the activesheet cells(lr,"h") refers the the cell
on the active sheet, not "Report (zonderArg)". Cells should also be preceded
by a period

with workSheets("Report (zonderArb)")
.......
......
.Range(.cells(lr,"h"), .cells(lr,"o")).borders.LineStyle = xlContinuous
End With


"Roger" wrote:

> I am reposting this as I cannot get a reply to my problem. The following
> macro is meant to go to the last row and then on the next row (lr+1) make a
> totals row. But when I run the macro I get an error message
> "application-defined or object-defined error" and it
> does to the line
>
> ..Range(Cells(lr, "h"), Cells(lr, "o")).Borders.LineStyle = xlContinuous
>
> and after as the problem code.
>
> Can someone help me please ?
>
> > Sub MakeTotals()'
>
> > ' Make Totals for Report (zonderArb) Macro
> > ' Macro recorded 15/06/02 by RAO
> > ' Keyboard Shortcut: Ctrl+Shift+T
> > ' clears borders from rows below total line (row 460) for clean printing
> > ' creates SUM formula and then copies across total line
> > ' creates borders and formats 0.00 where appropriate
> > ' saves worksheet
>
> > lr=cells(rows.count,"H").end(xlup).row+1
> > Worksheets("Report (zonderArb)").Rows("460:660").Borders.linestyle =
> > xlNone
> > with workSheets("Report (zonderArb)")
> > . Range("H" & lr).formula="=sum(h1:h" & lr & ")"
> > . Range("H" & lr).Copy Range(cells(lr,"I"),cells(lr,"ae"))
> > . Range(cells(lr,"A"),cells(lr,"g")).borders.LineStyle = xlContinuous
> > . Range(cells(lr,"h"),cells(lr,"o")).borders.LineStyle = xlContinuous
> > . Range(cells(lr,"p"),cells(lr,"w")).borders.LineStyle = xlContinuous
> > . Range(cells(lr,"x"),cells(lr,"ae")).borders.LineStyle = xlContinuous
> > . Range("AF" & lr).").borders.LineStyle = xlContinuous
> >
> > . Range(cells(l4,"M"),cells(lr,"n")).NumberFormat = "0.00"
> > . Range(cells(l4,"u"),cells(lr,"v")).NumberFormat = "0.00"
> > . Range(cells(l4,"ac"),cells(lr,"ad")).NumberFormat = "0.00"
> >
> > . Range("G" & LR)= "Totals"
> >
> > With . Range("G" & LR).Characters(Start:=1, Length:=7).Font
> > .Name = "Arial"
> > .FontStyle = "Regular"
> > .Size = 8
> > .Strikethrough = False
> > .Superscript = False
> > .Subscript = False
> > .OutlineFont = False
> > .Shadow = False
> > .Underline = xlUnderlineStyleNone
> > .ColorIndex = 1
> > End With
> >
> > end with
> >
> > 'Sheets("Instructions").Select
> > ' Range("K61").Select
> > ActiveWorkbook.Save
> > End Sub
> >
> >
> >
>
>
>

[Chip Pearson]

It was anyone's guess since the variables were not declared
(shame, shame, shame).


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Don Guillett" <dguillett1@austin.rr.com> wrote in message
news:eB93iAtnGHA.776@TK2MSFTNGP04.phx.gbl...
> It probably should have been LR. Typo. Probably mine when I
> hurriedly provided the code originally.
>
> --
> Don Guillett
> SalesAid Software
> dguillett1@austin.rr.com
> "Chip Pearson" <chip@cpearson.com> wrote in message
> news:e1Tyb1snGHA.3348@TK2MSFTNGP03.phx.gbl...
>> You have not assigned a value to the l4 variable.
>>
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>>
>> "Roger" <roger46@skynet.be> wrote in message
>> news:u07ajqsnGHA.2312@TK2MSFTNGP04.phx.gbl...
>>>I am reposting this as I cannot get a reply to my problem. The
>>>following macro is meant to go to the last row and then on the
>>>next row (lr+1) make a totals row. But when I run the macro I
>>>get an error message "application-defined or object-defined
>>>error" and it
>>> does to the line
>>>
>>> .Range(Cells(lr, "h"), Cells(lr, "o")).Borders.LineStyle =
>>> xlContinuous
>>>
>>> and after as the problem code.
>>>
>>> Can someone help me please ?
>>>
>>>> Sub MakeTotals()'
>>>
>>>> ' Make Totals for Report (zonderArb) Macro
>>>> ' Macro recorded 15/06/02 by RAO
>>>> ' Keyboard Shortcut: Ctrl+Shift+T
>>>> ' clears borders from rows below total line (row 460) for
>>>> clean printing
>>>> ' creates SUM formula and then copies across total line
>>>> ' creates borders and formats 0.00 where appropriate
>>>> ' saves worksheet
>>>
>>>> lr=cells(rows.count,"H").end(xlup).row+1
>>>> Worksheets("Report
>>>> (zonderArb)").Rows("460:660").Borders.linestyle =
>>>> xlNone
>>>> with workSheets("Report (zonderArb)")
>>>> . Range("H" & lr).formula="=sum(h1:h" & lr & ")"
>>>> . Range("H" & lr).Copy
>>>> Range(cells(lr,"I"),cells(lr,"ae"))
>>>> . Range(cells(lr,"A"),cells(lr,"g")).borders.LineStyle =
>>>> xlContinuous
>>>> . Range(cells(lr,"h"),cells(lr,"o")).borders.LineStyle =
>>>> xlContinuous
>>>> . Range(cells(lr,"p"),cells(lr,"w")).borders.LineStyle =
>>>> xlContinuous
>>>> . Range(cells(lr,"x"),cells(lr,"ae")).borders.LineStyle =
>>>> xlContinuous
>>>> . Range("AF" & lr).").borders.LineStyle = xlContinuous
>>>>
>>>> . Range(cells(l4,"M"),cells(lr,"n")).NumberFormat =
>>>> "0.00"
>>>> . Range(cells(l4,"u"),cells(lr,"v")).NumberFormat =
>>>> "0.00"
>>>> . Range(cells(l4,"ac"),cells(lr,"ad")).NumberFormat =
>>>> "0.00"
>>>>
>>>> . Range("G" & LR)= "Totals"
>>>>
>>>> With . Range("G" & LR).Characters(Start:=1,
>>>> Length:=7).Font
>>>> .Name = "Arial"
>>>> .FontStyle = "Regular"
>>>> .Size = 8
>>>> .Strikethrough = False
>>>> .Superscript = False
>>>> .Subscript = False
>>>> .OutlineFont = False
>>>> .Shadow = False
>>>> .Underline = xlUnderlineStyleNone
>>>> .ColorIndex = 1
>>>> End With
>>>>
>>>> end with
>>>>
>>>> 'Sheets("Instructions").Select
>>>> ' Range("K61").Select
>>>> ActiveWorkbook.Save
>>>> End Sub
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

[Roger]

As an amateur to a group of professionals, thanks for the advice but you
have now lost me completely !! What is the problem with the code below, sure
it is not just making lr into LR ?? ... Roger



"Don Guillett" <dguillett1@austin.rr.com> wrote in message
news:eB93iAtnGHA.776@TK2MSFTNGP04.phx.gbl...
> It probably should have been LR. Typo. Probably mine when I hurriedly
> provided the code originally.
>
> --
> Don Guillett
> SalesAid Software
> dguillett1@austin.rr.com
> "Chip Pearson" <chip@cpearson.com> wrote in message
> news:e1Tyb1snGHA.3348@TK2MSFTNGP03.phx.gbl...
>> You have not assigned a value to the l4 variable.
>>
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>>
>> "Roger" <roger46@skynet.be> wrote in message
>> news:u07ajqsnGHA.2312@TK2MSFTNGP04.phx.gbl...
>>>I am reposting this as I cannot get a reply to my problem. The following
>>>macro is meant to go to the last row and then on the next row (lr+1) make
>>>a totals row. But when I run the macro I get an error message
>>>"application-defined or object-defined error" and it
>>> does to the line
>>>
>>> .Range(Cells(lr, "h"), Cells(lr, "o")).Borders.LineStyle = xlContinuous
>>>
>>> and after as the problem code.
>>>
>>> Can someone help me please ?
>>>
>>>> Sub MakeTotals()'
>>>
>>>> ' Make Totals for Report (zonderArb) Macro
>>>> ' Macro recorded 15/06/02 by RAO
>>>> ' Keyboard Shortcut: Ctrl+Shift+T
>>>> ' clears borders from rows below total line (row 460) for clean
>>>> printing
>>>> ' creates SUM formula and then copies across total line
>>>> ' creates borders and formats 0.00 where appropriate
>>>> ' saves worksheet
>>>
>>>> lr=cells(rows.count,"H").end(xlup).row+1
>>>> Worksheets("Report (zonderArb)").Rows("460:660").Borders.linestyle =
>>>> xlNone
>>>> with workSheets("Report (zonderArb)")
>>>> . Range("H" & lr).formula="=sum(h1:h" & lr & ")"
>>>> . Range("H" & lr).Copy Range(cells(lr,"I"),cells(lr,"ae"))
>>>> . Range(cells(lr,"A"),cells(lr,"g")).borders.LineStyle =
>>>> xlContinuous
>>>> . Range(cells(lr,"h"),cells(lr,"o")).borders.LineStyle =
>>>> xlContinuous
>>>> . Range(cells(lr,"p"),cells(lr,"w")).borders.LineStyle =
>>>> xlContinuous
>>>> . Range(cells(lr,"x"),cells(lr,"ae")).borders.LineStyle =
>>>> xlContinuous
>>>> . Range("AF" & lr).").borders.LineStyle = xlContinuous
>>>>
>>>> . Range(cells(l4,"M"),cells(lr,"n")).NumberFormat = "0.00"
>>>> . Range(cells(l4,"u"),cells(lr,"v")).NumberFormat = "0.00"
>>>> . Range(cells(l4,"ac"),cells(lr,"ad")).NumberFormat = "0.00"
>>>>
>>>> . Range("G" & LR)= "Totals"
>>>>
>>>> With . Range("G" & LR).Characters(Start:=1, Length:=7).Font
>>>> .Name = "Arial"
>>>> .FontStyle = "Regular"
>>>> .Size = 8
>>>> .Strikethrough = False
>>>> .Superscript = False
>>>> .Subscript = False
>>>> .OutlineFont = False
>>>> .Shadow = False
>>>> .Underline = xlUnderlineStyleNone
>>>> .ColorIndex = 1
>>>> End With
>>>>
>>>> end with
>>>>
>>>> 'Sheets("Instructions").Select
>>>> ' Range("K61").Select
>>>> ActiveWorkbook.Save
>>>> End Sub
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

[Roger]

This is the code that I am using. When I run it still get an error on the
line .LR = .Cells(Rows.Count, "H").End(xlUp).Row + 1. Any ideas ?? It just
does not want to count the rows to determine which row is the last row in
the table of data ! thanks ... Roger


With Worksheets("Report (zonderArb)")
..LR = .Cells(Rows.Count, "H").End(xlUp).Row + 1
..Range("H" & LR).Formula = "=sum(h1:h" & LR + 1 & ")"
..Range("H" & LR).Copy Range(.Cells(LR, "I"), .Cells(LR, "ae"))
..Range(.Cells(LR, "A"), .Cells(LR, "g")).Borders.LineStyle = xlContinuous
..Range(.Cells(LR, "h"), .Cells(LR, "o")).Borders.LineStyle = xlContinuous
..Range(.Cells(LR, "p"), .Cells(LR, "w")).Borders.LineStyle = xlContinuous
..Range(.Cells(LR, "x"), .Cells(LR, "ae")).Borders.LineStyle = xlContinuous
..Range("AF" & LR).Borders.LineStyle = xlContinuous

..Range(.Cells(l4, "M"), .Cells(LR, "n")).NumberFormat = "0.00"
..Range(.Cells(l4, "u"), .Cells(LR, "v")).NumberFormat = "0.00"
..Range(.Cells(l4, "ac"), .Cells(LR, "ad")).NumberFormat = "0.00"
..Range("G" & LR) = "Totals"

With Range("G" & LR).Characters(Start:=1, Length:=7).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With

End With

'Sheets("Instructions").Select
' Range("K61").Select
ActiveWorkbook.Save
End Sub

[Don Guillett]

I know, I know, I know
I should do that

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Chip Pearson" <chip@cpearson.com> wrote in message
news:%23BIEyGtnGHA.4636@TK2MSFTNGP05.phx.gbl...
> It was anyone's guess since the variables were not declared (shame, shame,
> shame).
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> "Don Guillett" <dguillett1@austin.rr.com> wrote in message
> news:eB93iAtnGHA.776@TK2MSFTNGP04.phx.gbl...
>> It probably should have been LR. Typo. Probably mine when I hurriedly
>> provided the code originally.
>>
>> --
>> Don Guillett
>> SalesAid Software
>> dguillett1@austin.rr.com
>> "Chip Pearson" <chip@cpearson.com> wrote in message
>> news:e1Tyb1snGHA.3348@TK2MSFTNGP03.phx.gbl...
>>> You have not assigned a value to the l4 variable.
>>>
>>>
>>> --
>>> Cordially,
>>> Chip Pearson
>>> Microsoft MVP - Excel
>>> Pearson Software Consulting, LLC
>>> www.cpearson.com
>>>
>>>
>>> "Roger" <roger46@skynet.be> wrote in message
>>> news:u07ajqsnGHA.2312@TK2MSFTNGP04.phx.gbl...
>>>>I am reposting this as I cannot get a reply to my problem. The following
>>>>macro is meant to go to the last row and then on the next row (lr+1)
>>>>make a totals row. But when I run the macro I get an error message
>>>>"application-defined or object-defined error" and it
>>>> does to the line
>>>>
>>>> .Range(Cells(lr, "h"), Cells(lr, "o")).Borders.LineStyle = xlContinuous
>>>>
>>>> and after as the problem code.
>>>>
>>>> Can someone help me please ?
>>>>
>>>>> Sub MakeTotals()'
>>>>
>>>>> ' Make Totals for Report (zonderArb) Macro
>>>>> ' Macro recorded 15/06/02 by RAO
>>>>> ' Keyboard Shortcut: Ctrl+Shift+T
>>>>> ' clears borders from rows below total line (row 460) for clean
>>>>> printing
>>>>> ' creates SUM formula and then copies across total line
>>>>> ' creates borders and formats 0.00 where appropriate
>>>>> ' saves worksheet
>>>>
>>>>> lr=cells(rows.count,"H").end(xlup).row+1
>>>>> Worksheets("Report (zonderArb)").Rows("460:660").Borders.linestyle =
>>>>> xlNone
>>>>> with workSheets("Report (zonderArb)")
>>>>> . Range("H" & lr).formula="=sum(h1:h" & lr & ")"
>>>>> . Range("H" & lr).Copy Range(cells(lr,"I"),cells(lr,"ae"))
>>>>> . Range(cells(lr,"A"),cells(lr,"g")).borders.LineStyle =
>>>>> xlContinuous
>>>>> . Range(cells(lr,"h"),cells(lr,"o")).borders.LineStyle =
>>>>> xlContinuous
>>>>> . Range(cells(lr,"p"),cells(lr,"w")).borders.LineStyle =
>>>>> xlContinuous
>>>>> . Range(cells(lr,"x"),cells(lr,"ae")).borders.LineStyle =
>>>>> xlContinuous
>>>>> . Range("AF" & lr).").borders.LineStyle = xlContinuous
>>>>>
>>>>> . Range(cells(l4,"M"),cells(lr,"n")).NumberFormat = "0.00"
>>>>> . Range(cells(l4,"u"),cells(lr,"v")).NumberFormat = "0.00"
>>>>> . Range(cells(l4,"ac"),cells(lr,"ad")).NumberFormat = "0.00"
>>>>>
>>>>> . Range("G" & LR)= "Totals"
>>>>>
>>>>> With . Range("G" & LR).Characters(Start:=1, Length:=7).Font
>>>>> .Name = "Arial"
>>>>> .FontStyle = "Regular"
>>>>> .Size = 8
>>>>> .Strikethrough = False
>>>>> .Superscript = False
>>>>> .Subscript = False
>>>>> .OutlineFont = False
>>>>> .Shadow = False
>>>>> .Underline = xlUnderlineStyleNone
>>>>> .ColorIndex = 1
>>>>> End With
>>>>>
>>>>> end with
>>>>>
>>>>> 'Sheets("Instructions").Select
>>>>> ' Range("K61").Select
>>>>> ActiveWorkbook.Save
>>>>> End Sub
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

[Don Guillett]

A lot of this is my fault for being in a hurry when I tried to help clean up
your code. This idea here HAS been tested
I am finding the last row on sheet3 which is NOT the active sheet. As was
pointed out, notice the .'s in the with. I am copying range("a4:d" & LR) to
the active sheet. Either line works.

Sub dolastrowexercise()
With Sheets("sheet3")
LR = .Cells(Rows.Count, "H").End(xlUp).Row
MsgBox LR
'.Range(.Cells(4, 1), .Cells(LR, 4)).Copy Range("a15")
..Range("a4:d" & LR).Copy Range("a15")'with .'s
'.Range("a4:d" & LR).Copy sheets("sheet5").Range("a15")'no dots
End With
End Sub


--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Roger" <roger46@skynet.be> wrote in message
news:edietztnGHA.4364@TK2MSFTNGP05.phx.gbl...
> This is the code that I am using. When I run it still get an error on the
> line .LR = .Cells(Rows.Count, "H").End(xlUp).Row + 1. Any ideas ?? It just
> does not want to count the rows to determine which row is the last row in
> the table of data ! thanks ... Roger
>
>
> With Worksheets("Report (zonderArb)")
> .LR = .Cells(Rows.Count, "H").End(xlUp).Row + 1
> .Range("H" & LR).Formula = "=sum(h1:h" & LR + 1 & ")"
> .Range("H" & LR).Copy Range(.Cells(LR, "I"), .Cells(LR, "ae"))
> .Range(.Cells(LR, "A"), .Cells(LR, "g")).Borders.LineStyle = xlContinuous
> .Range(.Cells(LR, "h"), .Cells(LR, "o")).Borders.LineStyle = xlContinuous
> .Range(.Cells(LR, "p"), .Cells(LR, "w")).Borders.LineStyle = xlContinuous
> .Range(.Cells(LR, "x"), .Cells(LR, "ae")).Borders.LineStyle = xlContinuous
> .Range("AF" & LR).Borders.LineStyle = xlContinuous
>
> .Range(.Cells(l4, "M"), .Cells(LR, "n")).NumberFormat = "0.00"
> .Range(.Cells(l4, "u"), .Cells(LR, "v")).NumberFormat = "0.00"
> .Range(.Cells(l4, "ac"), .Cells(LR, "ad")).NumberFormat = "0.00"
> .Range("G" & LR) = "Totals"
>
> With Range("G" & LR).Characters(Start:=1, Length:=7).Font
> .Name = "Arial"
> .FontStyle = "Regular"
> .Size = 8
> .Strikethrough = False
> .Superscript = False
> .Subscript = False
> .OutlineFont = False
> .Shadow = False
> .Underline = xlUnderlineStyleNone
> .ColorIndex = 1
> End With
>
> End With
>
> 'Sheets("Instructions").Select
> ' Range("K61").Select
> ActiveWorkbook.Save
> End Sub
>
>

[JMB]

LR should not be preceded by a period. Also, do you have any chart sheets in
your workbook? If so I would change Rows.Count to .Rows.Count

Rows.Count will refer to the active sheet, which will fail if it happens to
be a chart.

"Roger" wrote:

> This is the code that I am using. When I run it still get an error on the
> line .LR = .Cells(Rows.Count, "H").End(xlUp).Row + 1. Any ideas ?? It just
> does not want to count the rows to determine which row is the last row in
> the table of data ! thanks ... Roger
>
>
> With Worksheets("Report (zonderArb)")
> ..LR = .Cells(Rows.Count, "H").End(xlUp).Row + 1
> ..Range("H" & LR).Formula = "=sum(h1:h" & LR + 1 & ")"
> ..Range("H" & LR).Copy Range(.Cells(LR, "I"), .Cells(LR, "ae"))
> ..Range(.Cells(LR, "A"), .Cells(LR, "g")).Borders.LineStyle = xlContinuous
> ..Range(.Cells(LR, "h"), .Cells(LR, "o")).Borders.LineStyle = xlContinuous
> ..Range(.Cells(LR, "p"), .Cells(LR, "w")).Borders.LineStyle = xlContinuous
> ..Range(.Cells(LR, "x"), .Cells(LR, "ae")).Borders.LineStyle = xlContinuous
> ..Range("AF" & LR).Borders.LineStyle = xlContinuous
>
> ..Range(.Cells(l4, "M"), .Cells(LR, "n")).NumberFormat = "0.00"
> ..Range(.Cells(l4, "u"), .Cells(LR, "v")).NumberFormat = "0.00"
> ..Range(.Cells(l4, "ac"), .Cells(LR, "ad")).NumberFormat = "0.00"
> ..Range("G" & LR) = "Totals"
>
> With Range("G" & LR).Characters(Start:=1, Length:=7).Font
> .Name = "Arial"
> .FontStyle = "Regular"
> .Size = 8
> .Strikethrough = False
> .Superscript = False
> .Subscript = False
> .OutlineFont = False
> .Shadow = False
> .Underline = xlUnderlineStyleNone
> .ColorIndex = 1
> End With
>
> End With
>
> 'Sheets("Instructions").Select
> ' Range("K61").Select
> ActiveWorkbook.Save
> End Sub
>
>
>