Delete Worksheet (if it exist) and Delete Top 4 Rows on remaining worksheets

[JohanGduToit]

Greetings Experts,

Please assist me with an Error 91 generated in the following code on the 1st occurrence of line "For Each Sheet in ActiveWorkbook.Worksheets"

I am attempting to loop through all worksheets to see if a worksheet titled "Updated Allocation List" exist, and if it does, to delete it. I then want to delete the top 4 rows on all remaining worksheets. (I have commented out the deletion of the 4 top rows in the code below, because I also encounter an error when attempting to do run that code).

Once the abovementioned issue has been resolved I will need to add additional coding to remove all rows containing an array of text strings; but I will post a seperate thread on that issue

Please be so kind as to advise?

Many Thanks!

Public Sub FormatMarkham01(sFile As String)

'Delete Worksheet <Updated Allocation List> if sheet exist, Delete Rows 1 to 4 and Delete all Rows containing specified text

'On Error GoTo Err_FormatMarkham01
    
    Dim xlApp As Object
    Dim xlSheet As Object
        
    Application.SetOption "Show Status Bar", True
    vStatusBar = SysCmd(acSysCmdSetStatus, "Formatting Markham Sales File (Stage 1)... Please wait.")

    Set xlApp = CreateObject("Excel.Application")
    Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)

    With xlApp
       [B] For Each Sheet In ActiveWorkbook.Worksheets[/B]
            If Sheet.Name = "Updated Allocation List" Then
                .Application.DisplayAlerts = False
                .Application.Worksheets("Updated Allocation List").Delete
                .Application.DisplayAlerts = True
            End If
        Next Sheet
                
       [B] 'For Each Sheet In ActiveWorkbook.Worksheets
        '    .Application.Range("1:4").EntireRow.Delete
        'Next Sheet[/B]
                           
        .Application.Sheets(1).Select
        .Application.Range("A1").Select
        .Application.ActiveWorkbook.Save
        .Application.ActiveWorkbook.Close
        .Quit
   End With
   
   vStatusBar = SysCmd(acSysCmdClearStatus)

   Set xlSheet = Nothing
   Set xlApp = Nothing

    
'Exit_FormatMarkham01:
'    Exit Sub
    
'Err_FormatMarkham01:
'    vStatusBar = SysCmd(acSysCmdClearStatus)
'    MsgBox Err.Number & " - " & Err.Description
'    Set xlSheet = Nothing
'    Set xlApp = Nothing
'    Resume Exit_FormatMarkham01
    
End Sub

 

[JohanGduToit]

@BSALV : Thank you very much for assisting; I have noted all your comments and suggestions and will definitely refer back and apply some of your suggestions in future.

 

[JohanGduToit]

Kudos to both of you!

 

[My Aswer Is This]

Hi 'My Answer Is'

Your code works like a charm... I made a typo by omitting ".Rows(1)" from the "Sheets(i).Rows(1).Resize(4).Delete" line which caused Error 438. I had to add ".Application" to some lines to get the code to work...not sure why though. But hey, it works!!

Working Code below... thank you very much for your help!

Public Sub FormatMarkham01(sFile As String)

'Delete Worksheet <Updated Allocation List> if sheet exist, Delete Rows 1 to 4 and Delete all Rows containing specified text

'On Error GoTo Err_FormatMarkham01
   
    Dim xlApp As Object
    Dim xlSheet As Object
       
    Dim I As Long
       
    Application.SetOption "Show Status Bar", True
    vStatusBar = SysCmd(acSysCmdSetStatus, "Formatting Markham Sales File (Stage 1)... Please wait.")

    Set xlApp = CreateObject("Excel.Application")
    Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)

    With xlApp
        .Application.ScreenUpdating = False
        .Application.DisplayAlerts = False
        For I = .Application.Sheets.Count To 1 Step -1
            If .Application.Sheets(I).Name = "Updated Allocation List" Then
                .Application.Sheets(I).Delete
                Else
                    .Application.Sheets(I).Rows(1).Resize(4).Delete
            End If
        Next
        .Application.ScreenUpdating = True
                     
        .Application.Sheets(1).Select
        .Application.Range("A1").Select
        .Application.ActiveWorkbook.Save
        .Application.ActiveWorkbook.Close
        .Quit
   End With
  
   MsgBox "DONE!"
   vStatusBar = SysCmd(acSysCmdClearStatus)

   Set xlSheet = Nothing
   Set xlApp = Nothing

   
'Exit_FormatMarkham01:
'    Exit Sub
   
'Err_FormatMarkham01:
'    vStatusBar = SysCmd(acSysCmdClearStatus)
'    MsgBox Err.Number & " - " & Err.Description
'    Set xlSheet = Nothing
'    Set xlApp = Nothing
'    Resume Exit_FormatMarkham01
   
End Sub

Well glad it worked for you. Not sure why you think all the additional code you have is needed.

 

[JohanGduToit]

Well glad it worked for you. Not sure why you think all the additional code you have is needed.

Would you mind taking the last 'working code' I copied and remove what you believe is not required? Just as a matter of interest and only if you do have the time. Always a good thing to know where I can do better and optimize things

Thank you!

 

[JohanGduToit]

Well glad it worked for you. Not sure why you think all the additional code you have is needed.

I call the procedure (module) from within MS Access...

 

[My Aswer Is This]

I call the procedure (module) from within MS Access...

OK. I see now

 

[JohanGduToit]

OK. I see now

Thank you again for your help; much appreciated!

 

[My Aswer Is This]

Would you mind taking the last 'working code' I copied and remove what you believe is not required? Just as a matter of interest and only if you do have the time. Always a good thing to know where I can do better and optimize things

Thank you!

I have no knowledge of MS Access. So not sure what might not be needed

 

[My Aswer Is This]

Thank you again for your help; much appreciated!

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.