Clear Contents of a Range - Different Sheets

[slimpickens]

Hello,

I have trying everything I can think of to solve this problem. I a message with the end result being OK. When I click Ok, I want Sheets1, 2, 3 and 5 contents to be cleared leaving my headings. I have applied the following code but I get "subscript out of range". Take a look and see if you can tell me what is wrong with it.

Private Sub CommandButton1_Click()
Dim Range1 As Range
Dim Range2 As Range
Dim Range3 As Range
Dim Range4 As Range

Set Range1 = Worksheets("Sheet1").Range("A6:Z60")
Set Range2 = Worksheets("Sheet2").Range("A6:BC60")
Set Range1 = Worksheets("Sheet3").Range("A6:AY60")
Set Range1 = Worksheets("Sheet5").Range("A6:L60")
Range1.ClearContents
Range2.ClearContents
Range3.ClearContents
Range4.ClearContents

Any help would be appreciated.

Carol

[klb]

Set Range1 = Worksheets("Sheet3").Range("A6:AY60")
Set Range1 = Worksheets("Sheet5").Range("A6:L60")


Shouldn't these be Range3 and Range 4 respectively?

[kkknie]

Looks like a typo...

You set Range1 three times. Looks like it should have been Range3 and Range4. I duplicated this and got an object variable not set. Maybe the typo was in your post?

Also, when I first copied to a blank worksheet I got subscript out of range. The reason was becaus I didn't have a sheet called sheet5. I would bet your worksheet doesn't have the same sheet names either.

K

[slimpickens]

Hi K,

I made the correction and yes I do have sheet5, sheet 4 has the data for my controls on the form. I ran it again but I still get the same message. It highlights

Set Range1 = Worksheets("Sheet1").Range("A6:Z60")

Something has to be wrong with the code, but I can't figure out what is wrong. I will try messing around with it again and post if I make any progress.

Carol

[NateO]

You don't have to set the range if you don't want to. The following should work:

Private Sub CommandButton1_Click()

Sheets("Sheet1").Range("A6:Z60").ClearContents

Sheets("Sheet2").Range("A6:BC60").ClearContents

Sheets("Sheet3").Range("A6:AY60").ClearContents

Sheets("Sheet5").Range("A6:L60").ClearContents

End Sub

[NateO]

This works for me if you do want these assigned to variables:

Private Sub CommandButton1_Click()

Dim Range1 As Range, Range2 As Range, Range3 As Range, Range4 As Range

Set Range1 = Worksheets("Sheet1").Range("A6:Z60")

Set Range2 = Worksheets("Sheet2").Range("A6:BC60")

Set Range3 = Worksheets("Sheet3").Range("A6:AY60")

Set Range4 = Worksheets("Sheet5").Range("A6:L60")

Range1.ClearContents

Range2.ClearContents

Range3.ClearContents

Range4.ClearContents

End Sub

Are your sheets protected? You can't clear locked cells if the sheet's protected in either case.
_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-14 11:50 ]

[Ivan F Moala]

Also Try setting the commandbuttons
[TakeFocus*******] = False

[slimpickens]

I decided to record a macro for what I wanted done step by step and the coding is a little longer than the ones suggested but it is working up to the point now where I want to save and exit the application. I keep getting different error messages when I try to save & exit. Messages like:-

Automation error - the object invoked has disconnected from its client

Path/File access error.

Workbook not saved.

Take a look at this code and tell me where I am going wrong


Private Sub CommandButton1_Click()

Range("A6:Z70").Select
Selection.ClearContents
Range("A6").Select
Sheets("Marine").Select
Range("A6:BC70").Select
Selection.ClearContents
Range("A6").Select
Sheets(" Payments").Select
Range("A6:M70").Select
Selection.ClearContents
Range("A6").Select
Sheets("Motor & Accident").Select
Range("A6:AY70").Select
Selection.ClearContents
Range("A6").Select


End Sub


Private Sub ExitSave_Click()
For Each w In Application.Workbooks
w.Save
Next w
Application.Quit
End

End Sub

Private Sub Weekly_Click()

Dim MyDate
Dim MyMonth
MyDate = Date
MyMonth = Month(MyDate)
ThisWorkbook.SaveCopyAs Filename:="Claims-" & MonthName(MyMonth) & Day(Date) & "-" & Year(Date)
Response = MsgBox("Copy called Claims with today's date was saved,Please Email to Head Office", _
vbOKOnly)
If vbOK Then
CommandButton1_Click
ExitSave_Click
End If
End Sub

Any help would be appreciated

Carol

[slimpickens]

I have solved my problem, but now another problem has surfaced. I have sent the mail by copying the workbook into a temporary file ,and then deleted the file after the mail has been sent - now when I want to close the original file and clear all the contents, I am running into a snag. How do I get it to close? I have tried putting in a command button called 'Close Workbook' on a form and loading this after the mail is sent but I get 'subscript out of range when I click it and it highlights line one of the code below. Can anyone offer any help??


Sheets("Fire").Select
Range("A6:Z70").Select
Selection.ClearContents
Range("A6").Select
Sheets("Marine").Select
Range("A6:BC70").Select
Selection.ClearContents
Range("A6").Select
Sheets(" Payments").Select
Range("A6:M70").Select
Selection.ClearContents
Range("A6").Select
Sheets("Motor & Accident").Select
Range("A6:AY70").Select
Selection.ClearContents
Range("A6").Select

[Ivan F Moala]

On 2002-05-16 09:25, slimpickens wrote:
I have solved my problem, but now another problem has surfaced. I have sent the mail by copying the workbook into a temporary file ,and then deleted the file after the mail has been sent - now when I want to close the original file and clear all the contents, I am running into a snag. How do I get it to close? I have tried putting in a command button called 'Close Workbook' on a form and loading this after the mail is sent but I get 'subscript out of range when I click it and it highlights line one of the code below. Can anyone offer any help??


Sheets("Fire").Select
Range("A6:Z70").Select
Selection.ClearContents
Range("A6").Select
Sheets("Marine").Select
Range("A6:BC70").Select
Selection.ClearContents
Range("A6").Select
Sheets(" Payments").Select
Range("A6:M70").Select
Selection.ClearContents
Range("A6").Select
Sheets("Motor & Accident").Select
Range("A6:AY70").Select
Selection.ClearContents
Range("A6").Select

Are you in the correct workbook?
If so then is there a sheet named
Sheets "Fire"