mightymorgs
New Member
- Joined
- Dec 22, 2021
- Messages
- 8
- Office Version
- 365
- Platform
- 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!
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