VB Error 9

Jim

New member
Joined
Jul 10, 2012
Messages
11
Reaction score
0
Points
0
'Procedure to Clear Contents from the Sheets
Sub SheetLabels(DestinationName As String, BranchName As String)
Dim Destination As Worksheet
**************************************************
THE LINE BELOW I GET ERROR 9 Can you advice please?
Set Destination = Worksheets(DestinationName)
**************************************************
'This will Clear the spreadsheet contents
On Error Resume Next
'Destination.Range("A1:G300").SpecialCells(xlCellTypeConstants).ClearContents
Destination.Range("A7:G36").SpecialCells(xlCellTypeConstants).ClearContents
Destination.Range("A40:E140").SpecialCells(xlCellTypeConstants).ClearContents
Destination.Range("C144:E152").SpecialCells(xlCellTypeConstants).ClearContents
Destination.Range("A156:E196").SpecialCells(xlCellTypeConstants).ClearContents
'Destination.Cells.SpecialCells(xlCellTypeConstants).ClearContents
 
See if this works for you...

Code:
Sub SheetLabels()
Dim DestinationName As String
Dim Destination As Worksheet
DestinationName = "Sheet1"
Set Destination = Worksheets(DestinationName)
'This will Clear the spreadsheet contents
On Error Resume Next
'Destination.Range("A1:G300").SpecialCells(xlCellTypeConstants).ClearContents
Destination.Range("A7:G36").SpecialCells(xlCellTypeConstants).ClearContents
Destination.Range("A40:E140").SpecialCells(xlCellTypeConstants).ClearContents
Destination.Range("C144:E152").SpecialCells(xlCellTypeConstants).ClearContents
Destination.Range("A156:E196").SpecialCells(xlCellTypeConstants).ClearContents
'Destination.Cells.SpecialCells(xlCellTypeConstants).ClearContents
End Sub
 
Thank you. But I have multiple sheets to clear. If I add DestinationName = "Sheet1" does not clear other worksheets. Any idea?
 
will you always be clearing the same sheets and same ranges on these sheets ?

for example:

sheet1 a1:e40
sheet2 c23:l44
sheet3 d2: f50
 
If the sheets and ranges will always be static then use something like this

Code:
Sub clear_sheets()
Sheets("Sheet1").Range("A1:C49", "A50:C100").ClearContents
Sheets("Sheet2").Range("A1:C49", "A50: C100").ClearContents
 

End Sub
 
Thank you! I like your Sub post.
 
Back
Top