Results 1 to 6 of 6

Thread: VB Error 9

  1. #1

    VB Error 9



    Register for a FREE account, and/
    or Log in to avoid these ads!

    '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

  2. #2
    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

  3. #3
    Thank you. But I have multiple sheets to clear. If I add DestinationName = "Sheet1" does not clear other worksheets. Any idea?

  4. #4
    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

  5. #5
    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

  6. #6
    Thank you! I like your Sub post.

Posting Permissions

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