Hey I have a macro that I have written, and I am having trouble getting
it to work. Most of the code will run smoothly no problem, but when it
comes to the section of code where it is supposed to select and clear a
certain area on another sheet in the workbook it generates an error.
Can anyone tell me why there is an error, and even more importantly,
how I can fix it? Any help would be much appreciated.

Private Sub CommandButton1_Click()
Dim PrevEstDate As Date
Dim CurrEstDate As Date
Dim temp As Integer

If Range("G8").Value < "" Then
PrevEstDate = Range("G8").Value
Else
PrevEstDate = 0
End If

Application.ScreenUpdating = False

temp = Range("G6").Value
Range("L1").Value = "Locked"

ActiveSheet.Copy After:=Sheets(temp)
ActiveSheet.Unprotect

Range("L1").Value = "Unlocked"
Range("G6").Value = temp + 1
Range("K37").Value = "''Est (" & temp & ")'"

If PrevEstDate < 0 Then
If Day(PrevEstDate) = 15 Then
CurrEstDate = DateSerial(Year(PrevEstDate), _
Month(PrevEstDate) + 1, 0)
Else
CurrEstDate = DateSerial(Year(PrevEstDate), _
Month(PrevEstDate) + 1, 15)
End If
Range("G8").Value = CurrEstDate
End If

' Errors Begin here. Error 1004 - something to do with the range.

Sheets("Tally WorkSheet").Range("X1") = "''Est (" & temp + 1 & ")'"
Sheets("Tally WorkSheet").Range("E11:T31,E34:T39").Select
Selection.ClearContents

Sheets("Water Truck").Range("P1") = "''Est (" & temp + 1 & ")'"
Sheets("Water Truck").Range("B10:G34").Select
Selection.ClearContents

Sheets("Pilot Car").Range("L1") = "''Est (" & temp + 1 & ")'"
Sheets("Pilot Car").Range("C11:D30").Select
Selection.ClearContents

Sheets("Street Sweeper").Range("L1") = "''Est (" & temp + 1 & ")'"
Sheets("Street Sweeper").Range("C11:D30").Select
Selection.ClearContents

Sheets("Power Broom").Range("L1") = "''Est (" & temp + 1 & ")'"
Sheets("Power Broom").Range("C11:D30").Select
Selection.ClearContents

Range("H16").Select
ActiveSheet.Protect
Application.ScreenUpdating = True
Unload Me
End Sub

First guess...

You can only select a range if that worksheet is active.

But you don't need to select a range to clearcontents:

Sheets("Tally WorkSheet").Range("E11:T31,E34:T39").Select
Selection.ClearContents

becomes

Sheets("Tally WorkSheet").Range("E11:T31,E34:T39").ClearContents

You'll have to do that for all that type of code.

mastermind wrote:

Hey I have a macro that I have written, and I am having trouble getting
it to work. Most of the code will run smoothly no problem, but when it
comes to the section of code where it is supposed to select and clear a
certain area on another sheet in the workbook it generates an error.
Can anyone tell me why there is an error, and even more importantly,
how I can fix it? Any help would be much appreciated.

Private Sub CommandButton1_Click()
Dim PrevEstDate As Date
Dim CurrEstDate As Date
Dim temp As Integer

If Range("G8").Value < "" Then
PrevEstDate = Range("G8").Value
Else
PrevEstDate = 0
End If

Application.ScreenUpdating = False

temp = Range("G6").Value
Range("L1").Value = "Locked"

ActiveSheet.Copy After:=Sheets(temp)
ActiveSheet.Unprotect

Range("L1").Value = "Unlocked"
Range("G6").Value = temp + 1
Range("K37").Value = "''Est (" & temp & ")'"

If PrevEstDate < 0 Then
If Day(PrevEstDate) = 15 Then
CurrEstDate = DateSerial(Year(PrevEstDate), _
Month(PrevEstDate) + 1, 0)
Else
CurrEstDate = DateSerial(Year(PrevEstDate), _
Month(PrevEstDate) + 1, 15)
End If
Range("G8").Value = CurrEstDate
End If

' Errors Begin here. Error 1004 - something to do with the range.

Sheets("Tally WorkSheet").Range("X1") = "''Est (" & temp + 1 & ")'"
Sheets("Tally WorkSheet").Range("E11:T31,E34:T39").Select
Selection.ClearContents

Sheets("Water Truck").Range("P1") = "''Est (" & temp + 1 & ")'"
Sheets("Water Truck").Range("B10:G34").Select
Selection.ClearContents

Sheets("Pilot Car").Range("L1") = "''Est (" & temp + 1 & ")'"
Sheets("Pilot Car").Range("C11:D30").Select
Selection.ClearContents

Sheets("Street Sweeper").Range("L1") = "''Est (" & temp + 1 & ")'"
Sheets("Street Sweeper").Range("C11:D30").Select
Selection.ClearContents

Sheets("Power Broom").Range("L1") = "''Est (" & temp + 1 & ")'"
Sheets("Power Broom").Range("C11:D30").Select
Selection.ClearContents

Range("H16").Select
ActiveSheet.Protect
Application.ScreenUpdating = True
Unload Me
End Sub

--

Dave Peterson