Help fixing and cleaning up my VBA Code

mightymorgs

New Member
Joined
Dec 22, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
I know just enough about VBA to dig myself into a hole and I need some help getting out. I'm using the code below to format several worksheets within my workbook. I'm running into a few issues that I can't figure out how to solve.

1) It's working the way I expect it to except that when it gets to the part with the red text, it's adding two rows of headers instead of one. What I'm trying to do is insert custom headers into the top row without overwriting the data that's currently there.

2) The first part of the code runs on the targeted worksheets but once it gets to the red text, it only runs on the active worksheets. I need the entire code to run on all of the targeted worksheets.

Any help is appreciated!

VBA Code:
Sub Delete()
Dim ws As Worksheet
Dim lastrow As Long
Dim i As Long
    With Application
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .ScreenUpdating = False
    End With
        For Each ws In ThisWorkbook.Worksheets
            If TryMatch(Lookup:=ws.Name, _
                Lookin:=wsControl.ListObjects("tblTarget").DataBodyRange) Then
                ws.Range("A:A,B:B,C:C,D:D,E:E,M:M,N:N,Q:Q,R:R").Delete
            End If
            If TryMatch(Lookup:=ws.Name, _
                Lookin:=wsControl.ListObjects("tblTarget").DataBodyRange) Then
                lastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
                    For i = lastrow To 1 Step -1
                        If TryMatch(Lookup:=ws.Cells(i, "A").Interior.Color, _
                        Lookin:=wsControl.ListObjects("tblColours").ListColumns(2).DataBodyRange) Then
                        ws.Rows(i).Delete
                        End If
                    Next i
            End If
           [COLOR=rgb(226, 80, 65)] If TryMatch(Lookup:=ws.Name, _
                Lookin:=wsControl.ListObjects("tblTarget").DataBodyRange) Then
                Rows("1:1").Select
                    Application.CutCopyMode = False
                    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            End If
            If TryMatch(Lookup:=ws.Name, _
                Lookin:=wsControl.ListObjects("tblTarget").DataBodyRange) Then
                Range("A1:J1").Value = Array("EE #", "mfg", "Serial #", "Initial Status", "Final Status", "Cost", "Description", "CSN", "CSN Description", "Category")
            End If[/COLOR]
        Next ws
    With Application
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

Worksheet.JPG
Worksheet 2.JPG
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Have try with these three changes (selection and reference to sheet in progress):
VBA Code:
'...
If TryMatch(Lookup:=ws.Name, LookIn:=wsControl.ListObjects("tblTarget").DataBodyRange) Then
    ws.Select                             '<- added
    ws.Rows("1:1").Select                 '<- changed
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End If
If TryMatch(Lookup:=ws.Name, LookIn:=wsControl.ListObjects("tblTarget").DataBodyRange) Then
    ws.Range("A1:J1").Value = Array("EE #", "mfg", "Serial #", "Initial Status", "Final Status", "Cost", "Description", "CSN", "CSN Description", "Category") '<- changed
End If
'...
 
Upvote 0
Have try with these three changes (selection and reference to sheet in progress):
VBA Code:
'...
If TryMatch(Lookup:=ws.Name, LookIn:=wsControl.ListObjects("tblTarget").DataBodyRange) Then
    ws.Select                             '<- added
    ws.Rows("1:1").Select                 '<- changed
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End If
If TryMatch(Lookup:=ws.Name, LookIn:=wsControl.ListObjects("tblTarget").DataBodyRange) Then
    ws.Range("A1:J1").Value = Array("EE #", "mfg", "Serial #", "Initial Status", "Final Status", "Cost", "Description", "CSN", "CSN Description", "Category") '<- changed
End If
'...
Thanks for the reply! I tried your changes and get an error at the "ws.select" line.
 

Attachments

  • Error.JPG
    Error.JPG
    24.5 KB · Views: 13
  • Error2.JPG
    Error2.JPG
    13.6 KB · Views: 21
Upvote 0
Just tested your macro and works correctly on my test file. I can use ws.Activate or ws.Select without errors.
Have you done some debbuging ? what name does "ws" assume when the macro stops ?
The only difference is that you are using Office 365 and mine is Office 2016.
Sorry, without a dummy file can't be of any other help.
 
Upvote 0
Just tested your macro and works correctly on my test file. I can use ws.Activate or ws.Select without errors.
Have you done some debbuging ? what name does "ws" assume when the macro stops ?
The only difference is that you are using Office 365 and mine is Office 2016.
Sorry, without a dummy file can't be of any other help.
Everything is working! Thanks
 
Upvote 0

Forum statistics

Threads
1,223,246
Messages
6,170,988
Members
452,373
Latest member
TimReeks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top