I have the following code:

Sub DirectorFormat()

Dim TSLastPFRow As Integer 'Tally Sheet
Dim TSPFTotal As Integer 'Tally Sheet PF
Dim ZeroRow As Long, i As Long

With Sheets("Tally Sheet")
.Cells.Copy
.Paste Destination:=Worksheets("DirectorCopy").Range("A1" )
End With

With Worksheets("DirectorCopy")
.Shapes("LazyEyeButton").Cut
For j = 1 To 64
.Shapes("Done! " & j).Cut
Next

.Columns("G:G").Delete
.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues,
Operation:=xlPasteSpecialOperationNone, _
SkipBlanks:=False, Transpose:=False

'Find the last PF
For i = 4 To Rows.Count Step 8
If Cells(i, "A").Value = 0 Then
ZeroRow = i
Exit For
End If
Next

TSLastPFRow = ZeroRow - 9
TSPFTotal = (Val(Replace(Cells(TSLastPFRow, 1).Value, "_PF", "")))

'Delete empty PFs at the bottom
.Range(ZeroRow & ":515").Delete

'Delete all title bars except the first one
For i = (ZeroRow - 7) To 13 Step -8
.Rows(i).Delete
Next

'Move the last title bar up 2 rows
.Rows(5).Select
Selection.Cut
.Rows(3).Select
Selection.Insert Shift:=xlDown

.Rows(4).Select
ActiveWindow.FreezePanes = True
End With
End Sub

At .Rows(5).Select I keep getting error Select method of Range class failed.
I read the help file and I think I have the format right. Is .Select
causing the issue? How do I select that row otherwise?

Some possible reasons:

The worksheet that the rows are on isn't the active sheet
Row 5 is hidden.


"Bishop" wrote:

I have the following code:

Sub DirectorFormat()

Dim TSLastPFRow As Integer 'Tally Sheet
Dim TSPFTotal As Integer 'Tally Sheet PF
Dim ZeroRow As Long, i As Long

With Sheets("Tally Sheet")
.Cells.Copy
.Paste Destination:=Worksheets("DirectorCopy").Range("A1" )
End With

With Worksheets("DirectorCopy")
.Shapes("LazyEyeButton").Cut
For j = 1 To 64
.Shapes("Done! " & j).Cut
Next

.Columns("G:G").Delete
.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues,
Operation:=xlPasteSpecialOperationNone, _
SkipBlanks:=False, Transpose:=False

'Find the last PF
For i = 4 To Rows.Count Step 8
If Cells(i, "A").Value = 0 Then
ZeroRow = i
Exit For
End If
Next

TSLastPFRow = ZeroRow - 9
TSPFTotal = (Val(Replace(Cells(TSLastPFRow, 1).Value, "_PF", "")))

'Delete empty PFs at the bottom
.Range(ZeroRow & ":515").Delete

'Delete all title bars except the first one
For i = (ZeroRow - 7) To 13 Step -8
.Rows(i).Delete
Next

'Move the last title bar up 2 rows
.Rows(5).Select
Selection.Cut
.Rows(3).Select
Selection.Insert Shift:=xlDown

.Rows(4).Select
ActiveWindow.FreezePanes = True
End With
End Sub

At .Rows(5).Select I keep getting error Select method of Range class failed.
I read the help file and I think I have the format right. Is .Select
causing the issue? How do I select that row otherwise?

If the worksheet (DirectorCopy) wasn't active then none of the other
properties would work, no? .Columns, .Cells, .Shapes... those all work fine.
No other worksheet becomes active within this With block so it has to be
active.

There are no hidden rows or columns on the sheet.

"Barb Reinhardt" wrote:

Some possible reasons:

The worksheet that the rows are on isn't the active sheet
Row 5 is hidden.


"Bishop" wrote:

I have the following code:

Sub DirectorFormat()

Dim TSLastPFRow As Integer 'Tally Sheet
Dim TSPFTotal As Integer 'Tally Sheet PF
Dim ZeroRow As Long, i As Long

With Sheets("Tally Sheet")
.Cells.Copy
.Paste Destination:=Worksheets("DirectorCopy").Range("A1" )
End With

With Worksheets("DirectorCopy")
.Shapes("LazyEyeButton").Cut
For j = 1 To 64
.Shapes("Done! " & j).Cut
Next

.Columns("G:G").Delete
.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues,
Operation:=xlPasteSpecialOperationNone, _
SkipBlanks:=False, Transpose:=False

'Find the last PF
For i = 4 To Rows.Count Step 8
If Cells(i, "A").Value = 0 Then
ZeroRow = i
Exit For
End If
Next

TSLastPFRow = ZeroRow - 9
TSPFTotal = (Val(Replace(Cells(TSLastPFRow, 1).Value, "_PF", "")))

'Delete empty PFs at the bottom
.Range(ZeroRow & ":515").Delete

'Delete all title bars except the first one
For i = (ZeroRow - 7) To 13 Step -8
.Rows(i).Delete
Next

'Move the last title bar up 2 rows
.Rows(5).Select
Selection.Cut
.Rows(3).Select
Selection.Insert Shift:=xlDown

.Rows(4).Select
ActiveWindow.FreezePanes = True
End With
End Sub

At .Rows(5).Select I keep getting error Select method of Range class failed.
I read the help file and I think I have the format right. Is .Select
causing the issue? How do I select that row otherwise?

I didn't see anywhere where it was active. Check it please.

"Bishop" wrote:

If the worksheet (DirectorCopy) wasn't active then none of the other
properties would work, no? .Columns, .Cells, .Shapes... those all work fine.
No other worksheet becomes active within this With block so it has to be
active.

There are no hidden rows or columns on the sheet.

"Barb Reinhardt" wrote:

Some possible reasons:

The worksheet that the rows are on isn't the active sheet
Row 5 is hidden.


"Bishop" wrote:

I have the following code:

Sub DirectorFormat()

Dim TSLastPFRow As Integer 'Tally Sheet
Dim TSPFTotal As Integer 'Tally Sheet PF
Dim ZeroRow As Long, i As Long

With Sheets("Tally Sheet")
.Cells.Copy
.Paste Destination:=Worksheets("DirectorCopy").Range("A1" )
End With

With Worksheets("DirectorCopy")
.Shapes("LazyEyeButton").Cut
For j = 1 To 64
.Shapes("Done! " & j).Cut
Next

.Columns("G:G").Delete
.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues,
Operation:=xlPasteSpecialOperationNone, _
SkipBlanks:=False, Transpose:=False

'Find the last PF
For i = 4 To Rows.Count Step 8
If Cells(i, "A").Value = 0 Then
ZeroRow = i
Exit For
End If
Next

TSLastPFRow = ZeroRow - 9
TSPFTotal = (Val(Replace(Cells(TSLastPFRow, 1).Value, "_PF", "")))

'Delete empty PFs at the bottom
.Range(ZeroRow & ":515").Delete

'Delete all title bars except the first one
For i = (ZeroRow - 7) To 13 Step -8
.Rows(i).Delete
Next

'Move the last title bar up 2 rows
.Rows(5).Select
Selection.Cut
.Rows(3).Select
Selection.Insert Shift:=xlDown

.Rows(4).Select
ActiveWindow.FreezePanes = True
End With
End Sub

At .Rows(5).Select I keep getting error Select method of Range class failed.
I read the help file and I think I have the format right. Is .Select
causing the issue? How do I select that row otherwise?

You are the man! I'm sorry but I thought the With Worksheets("DirectorCopy")
made the worksheet active. I was wrong obviously. I added

Worksheets("DirectorCopy").Activate

before the With block. It works as intended now. Thanks

"Barb Reinhardt" wrote:

I didn't see anywhere where it was active. Check it please.

"Bishop" wrote:

If the worksheet (DirectorCopy) wasn't active then none of the other
properties would work, no? .Columns, .Cells, .Shapes... those all work fine.
No other worksheet becomes active within this With block so it has to be
active.

There are no hidden rows or columns on the sheet.

"Barb Reinhardt" wrote:

Some possible reasons:

The worksheet that the rows are on isn't the active sheet
Row 5 is hidden.


"Bishop" wrote:

I have the following code:

Sub DirectorFormat()

Dim TSLastPFRow As Integer 'Tally Sheet
Dim TSPFTotal As Integer 'Tally Sheet PF
Dim ZeroRow As Long, i As Long

With Sheets("Tally Sheet")
.Cells.Copy
.Paste Destination:=Worksheets("DirectorCopy").Range("A1" )
End With

With Worksheets("DirectorCopy")
.Shapes("LazyEyeButton").Cut
For j = 1 To 64
.Shapes("Done! " & j).Cut
Next

.Columns("G:G").Delete
.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues,
Operation:=xlPasteSpecialOperationNone, _
SkipBlanks:=False, Transpose:=False

'Find the last PF
For i = 4 To Rows.Count Step 8
If Cells(i, "A").Value = 0 Then
ZeroRow = i
Exit For
End If
Next

TSLastPFRow = ZeroRow - 9
TSPFTotal = (Val(Replace(Cells(TSLastPFRow, 1).Value, "_PF", "")))

'Delete empty PFs at the bottom
.Range(ZeroRow & ":515").Delete

'Delete all title bars except the first one
For i = (ZeroRow - 7) To 13 Step -8
.Rows(i).Delete
Next

'Move the last title bar up 2 rows
.Rows(5).Select
Selection.Cut
.Rows(3).Select
Selection.Insert Shift:=xlDown

.Rows(4).Select
ActiveWindow.FreezePanes = True
End With
End Sub

At .Rows(5).Select I keep getting error Select method of Range class failed.
I read the help file and I think I have the format right. Is .Select
causing the issue? How do I select that row otherwise?