+ Reply to Thread
Results 1 to 2 of 2

Macro Troubles

  1. #1
    mastermind
    Guest

    Macro Troubles

    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


  2. #2
    Dave Peterson
    Guest

    Re: Macro Troubles

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1