my code works fine with the total line, but when I added the label in column
A it bugged out. What is wrong with code?

'Add label for last row

lastrow = Range("A65536").End(xlUp).Row
lastrow = lastrow + 1
Sheet1.Range("A" & lastrow) = "Total Outstanding at Month End"

'Add totals to report

Cells(1, 1).End(xlDown).Offset(1, 0).Range( _
"F2,H2,I2").FormulaR1C1 = "=Sum(R2C:R[-1]C)"

End Sub

lastrow = Range("A65536").End(xlUp).Row + 1
Sheet1.Range("A" & lastrow).Value = "Total Outstanding at Month End"



"marcia2026" wrote in message
...
my code works fine with the total line, but when I added the label in
column
A it bugged out. What is wrong with code?

'Add label for last row

lastrow = Range("A65536").End(xlUp).Row
lastrow = lastrow + 1
Sheet1.Range("A" & lastrow) = "Total Outstanding at Month End"

'Add totals to report

Cells(1, 1).End(xlDown).Offset(1, 0).Range( _
"F2,H2,I2").FormulaR1C1 = "=Sum(R2C:R[-1]C)"

End Sub

It still bugs on the 1st line.

"Bob Umlas" wrote:

lastrow = Range("A65536").End(xlUp).Row + 1
Sheet1.Range("A" & lastrow).Value = "Total Outstanding at Month End"



"marcia2026" wrote in message
...
my code works fine with the total line, but when I added the label in
column
A it bugged out. What is wrong with code?

'Add label for last row

lastrow = Range("A65536").End(xlUp).Row
lastrow = lastrow + 1
Sheet1.Range("A" & lastrow) = "Total Outstanding at Month End"

'Add totals to report

Cells(1, 1).End(xlDown).Offset(1, 0).Range( _
"F2,H2,I2").FormulaR1C1 = "=Sum(R2C:R[-1]C)"

End Sub

I'm not sure what bugged out means, but if you have gaps in column A, then using
..end(xldown) may not be a good choice.

I like
with activesheet
.cells(.rows.count,"A").end(xlup).offset(1,0)
....
(starting from the bottom

If bugging out means that the totals go on the row below that total string,
maybe...

Cells(1, 1).End(xlDown).Offset(1, 0).Range("F1,H1,I1").FormulaR1C1 _
= "=Sum(R2C:R[-1]C)"

marcia2026 wrote:

my code works fine with the total line, but when I added the label in column
A it bugged out. What is wrong with code?

'Add label for last row

lastrow = Range("A65536").End(xlUp).Row
lastrow = lastrow + 1
Sheet1.Range("A" & lastrow) = "Total Outstanding at Month End"

'Add totals to report

Cells(1, 1).End(xlDown).Offset(1, 0).Range( _
"F2,H2,I2").FormulaR1C1 = "=Sum(R2C:R[-1]C)"

End Sub

--

Dave Peterson

The code you have may or may not do what you want depending on which sheet is
the active sheet... Try using a range object to hold the spot where you want
to make the changes...

dim rng as range

set rng = sheet1.cells(rows.count, "a").end(xlup).offset(1,0)
rng.value = "Total Outstanding at Month End"
rng.offset(0,1).FormulaR1C1 = "=Sum(R2C:R[-1]C)" 'not sure what you want here

--
HTH...

Jim Thomlinson


"marcia2026" wrote:

my code works fine with the total line, but when I added the label in column
A it bugged out. What is wrong with code?

'Add label for last row

lastrow = Range("A65536").End(xlUp).Row
lastrow = lastrow + 1
Sheet1.Range("A" & lastrow) = "Total Outstanding at Month End"

'Add totals to report

Cells(1, 1).End(xlDown).Offset(1, 0).Range( _
"F2,H2,I2").FormulaR1C1 = "=Sum(R2C:R[-1]C)"

End Sub

sorry, I am new to this. When I said that it bugged out, I meant that the
code stopped working at that point and went to the debugger. Lastrow was
what was highlighted with a message about a compile error. What I want to do
is to create a worksheet with a variable number of lines with a total at the
end. It was working fine until I tried to add the "Total at month end" That
cell was empty. so i added the code just above my code for my totals. If i
take that part out it works ok. Here is the whole code

Sub CreateEndingWorkbook()
'
'
'Delete Outstanding worksheet

Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("Outstanding").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Create new Outstanding worksheet with most recent data

Sheets("TotalForMonth").Select
Sheets("TotalForMonth").Copy Befo=Sheets(1)
Sheets("TotalForMonth (2)").Select
Sheets("TotalForMonth (2)").Name = "Outstanding"

Application.Run "PERSONAL.XLS!DeleteCleared" '<<<< see below

'Add label for last row

LastRow = Range("A65536").End(xlUp).Row + 1
Sheet("Outstanding").Range("A" & LastRow).Value = "Total Outstanding at
Month End"

'Add totals to report

Cells(1, 1).End(xlDown).Offset(1, 0).Range( _
"F2,H2,I2").FormulaR1C1 = "=Sum(R2C:R[-1]C)"

End Sub

Sub DeleteCleared()
'will delete a rows that have been resolved
Dim LastRow As Long
Dim Row As Long
LastRow = Cells(Rows.Count, "F").End(xlUp).Row
For Row = LastRow To 1 Step -1
If Cells(Row, "G") = "R" Then
Rows(Row).Delete
End If
Next Row

End Sub
"Jim Thomlinson" wrote:

The code you have may or may not do what you want depending on which sheet is
the active sheet... Try using a range object to hold the spot where you want
to make the changes...

dim rng as range

set rng = sheet1.cells(rows.count, "a").end(xlup).offset(1,0)
rng.value = "Total Outstanding at Month End"
rng.offset(0,1).FormulaR1C1 = "=Sum(R2C:R[-1]C)" 'not sure what you want here

--
HTH...

Jim Thomlinson


"marcia2026" wrote:

my code works fine with the total line, but when I added the label in column
A it bugged out. What is wrong with code?

'Add label for last row

lastrow = Range("A65536").End(xlUp).Row
lastrow = lastrow + 1
Sheet1.Range("A" & lastrow) = "Total Outstanding at Month End"

'Add totals to report

Cells(1, 1).End(xlDown).Offset(1, 0).Range( _
"F2,H2,I2").FormulaR1C1 = "=Sum(R2C:R[-1]C)"

End Sub

You did have a typo in this line:

Sheet("Outstanding").Range("A" & LastRow).Value _
= "Total Outstanding at Month End"

It's SheetS (with that final S)

Sheets("Outstanding").Range("A" & LastRow).Value _
= "Total Outstanding at Month End"

Is that the line that was causing the error?


marcia2026 wrote:

sorry, I am new to this. When I said that it bugged out, I meant that the
code stopped working at that point and went to the debugger. Lastrow was
what was highlighted with a message about a compile error. What I want to do
is to create a worksheet with a variable number of lines with a total at the
end. It was working fine until I tried to add the "Total at month end" That
cell was empty. so i added the code just above my code for my totals. If i
take that part out it works ok. Here is the whole code

Sub CreateEndingWorkbook()
'
'
'Delete Outstanding worksheet

Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("Outstanding").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Create new Outstanding worksheet with most recent data

Sheets("TotalForMonth").Select
Sheets("TotalForMonth").Copy Befo=Sheets(1)
Sheets("TotalForMonth (2)").Select
Sheets("TotalForMonth (2)").Name = "Outstanding"

Application.Run "PERSONAL.XLS!DeleteCleared" '<<<< see below

'Add label for last row

LastRow = Range("A65536").End(xlUp).Row + 1
Sheet("Outstanding").Range("A" & LastRow).Value = "Total Outstanding at
Month End"

'Add totals to report

Cells(1, 1).End(xlDown).Offset(1, 0).Range( _
"F2,H2,I2").FormulaR1C1 = "=Sum(R2C:R[-1]C)"

End Sub

Sub DeleteCleared()
'will delete a rows that have been resolved
Dim LastRow As Long
Dim Row As Long
LastRow = Cells(Rows.Count, "F").End(xlUp).Row
For Row = LastRow To 1 Step -1
If Cells(Row, "G") = "R" Then
Rows(Row).Delete
End If
Next Row

End Sub
"Jim Thomlinson" wrote:

The code you have may or may not do what you want depending on which sheet is
the active sheet... Try using a range object to hold the spot where you want
to make the changes...

dim rng as range

set rng = sheet1.cells(rows.count, "a").end(xlup).offset(1,0)
rng.value = "Total Outstanding at Month End"
rng.offset(0,1).FormulaR1C1 = "=Sum(R2C:R[-1]C)" 'not sure what you want here

--
HTH...

Jim Thomlinson


"marcia2026" wrote:

my code works fine with the total line, but when I added the label in column
A it bugged out. What is wrong with code?

'Add label for last row

lastrow = Range("A65536").End(xlUp).Row
lastrow = lastrow + 1
Sheet1.Range("A" & lastrow) = "Total Outstanding at Month End"

'Add totals to report

Cells(1, 1).End(xlDown).Offset(1, 0).Range( _
"F2,H2,I2").FormulaR1C1 = "=Sum(R2C:R[-1]C)"

End Sub

--

Dave Peterson

ps. Share the complete error message and the line that caused the error.

marcia2026 wrote:

sorry, I am new to this. When I said that it bugged out, I meant that the
code stopped working at that point and went to the debugger. Lastrow was
what was highlighted with a message about a compile error. What I want to do
is to create a worksheet with a variable number of lines with a total at the
end. It was working fine until I tried to add the "Total at month end" That
cell was empty. so i added the code just above my code for my totals. If i
take that part out it works ok. Here is the whole code

Sub CreateEndingWorkbook()
'
'
'Delete Outstanding worksheet

Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("Outstanding").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Create new Outstanding worksheet with most recent data

Sheets("TotalForMonth").Select
Sheets("TotalForMonth").Copy Befo=Sheets(1)
Sheets("TotalForMonth (2)").Select
Sheets("TotalForMonth (2)").Name = "Outstanding"

Application.Run "PERSONAL.XLS!DeleteCleared" '<<<< see below

'Add label for last row

LastRow = Range("A65536").End(xlUp).Row + 1
Sheet("Outstanding").Range("A" & LastRow).Value = "Total Outstanding at
Month End"

'Add totals to report

Cells(1, 1).End(xlDown).Offset(1, 0).Range( _
"F2,H2,I2").FormulaR1C1 = "=Sum(R2C:R[-1]C)"

End Sub

Sub DeleteCleared()
'will delete a rows that have been resolved
Dim LastRow As Long
Dim Row As Long
LastRow = Cells(Rows.Count, "F").End(xlUp).Row
For Row = LastRow To 1 Step -1
If Cells(Row, "G") = "R" Then
Rows(Row).Delete
End If
Next Row

End Sub
"Jim Thomlinson" wrote:

The code you have may or may not do what you want depending on which sheet is
the active sheet... Try using a range object to hold the spot where you want
to make the changes...

dim rng as range

set rng = sheet1.cells(rows.count, "a").end(xlup).offset(1,0)
rng.value = "Total Outstanding at Month End"
rng.offset(0,1).FormulaR1C1 = "=Sum(R2C:R[-1]C)" 'not sure what you want here

--
HTH...

Jim Thomlinson


"marcia2026" wrote:

my code works fine with the total line, but when I added the label in column
A it bugged out. What is wrong with code?

'Add label for last row

lastrow = Range("A65536").End(xlUp).Row
lastrow = lastrow + 1
Sheet1.Range("A" & lastrow) = "Total Outstanding at Month End"

'Add totals to report

Cells(1, 1).End(xlDown).Offset(1, 0).Range( _
"F2,H2,I2").FormulaR1C1 = "=Sum(R2C:R[-1]C)"

End Sub

--

Dave Peterson

You guys are terrific!!!
When I corrected the typo everything worked fine. Thanks so much!
"Dave Peterson" wrote:

ps. Share the complete error message and the line that caused the error.

marcia2026 wrote:

sorry, I am new to this. When I said that it bugged out, I meant that the
code stopped working at that point and went to the debugger. Lastrow was
what was highlighted with a message about a compile error. What I want to do
is to create a worksheet with a variable number of lines with a total at the
end. It was working fine until I tried to add the "Total at month end" That
cell was empty. so i added the code just above my code for my totals. If i
take that part out it works ok. Here is the whole code

Sub CreateEndingWorkbook()
'
'
'Delete Outstanding worksheet

Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("Outstanding").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Create new Outstanding worksheet with most recent data

Sheets("TotalForMonth").Select
Sheets("TotalForMonth").Copy Befo=Sheets(1)
Sheets("TotalForMonth (2)").Select
Sheets("TotalForMonth (2)").Name = "Outstanding"

Application.Run "PERSONAL.XLS!DeleteCleared" '<<<< see below

'Add label for last row

LastRow = Range("A65536").End(xlUp).Row + 1
Sheet("Outstanding").Range("A" & LastRow).Value = "Total Outstanding at
Month End"

'Add totals to report

Cells(1, 1).End(xlDown).Offset(1, 0).Range( _
"F2,H2,I2").FormulaR1C1 = "=Sum(R2C:R[-1]C)"

End Sub

Sub DeleteCleared()
'will delete a rows that have been resolved
Dim LastRow As Long
Dim Row As Long
LastRow = Cells(Rows.Count, "F").End(xlUp).Row
For Row = LastRow To 1 Step -1
If Cells(Row, "G") = "R" Then
Rows(Row).Delete
End If
Next Row

End Sub
"Jim Thomlinson" wrote:

The code you have may or may not do what you want depending on which sheet is
the active sheet... Try using a range object to hold the spot where you want
to make the changes...

dim rng as range

set rng = sheet1.cells(rows.count, "a").end(xlup).offset(1,0)
rng.value = "Total Outstanding at Month End"
rng.offset(0,1).FormulaR1C1 = "=Sum(R2C:R[-1]C)" 'not sure what you want here

--
HTH...

Jim Thomlinson


"marcia2026" wrote:

my code works fine with the total line, but when I added the label in column
A it bugged out. What is wrong with code?

'Add label for last row

lastrow = Range("A65536").End(xlUp).Row
lastrow = lastrow + 1
Sheet1.Range("A" & lastrow) = "Total Outstanding at Month End"

'Add totals to report

Cells(1, 1).End(xlDown).Offset(1, 0).Range( _
"F2,H2,I2").FormulaR1C1 = "=Sum(R2C:R[-1]C)"

End Sub

--

Dave Peterson