VBA to delete rows with certain value before duplicating

[shayalsamawi]

Hi guys,

I have this VBA code:


Public Sub ShippingForecastMoveAlong()
Dim newName As String

On Error Resume Next
newName = InputBox("Please Enter New Period")


If newName <> "" Then
ActiveSheet.Copy After:=Worksheets(Sheets.Count)
On Error Resume Next
ActiveSheet.Name = newName
End If
End Sub


That duplicates the sheet into a new sheet (within the same workbook)

Before duplicating the sheet - I want any rows (starting column I row 8) with a "1" to be deleted.

I've attached a picture (users click end period and the sheet is duplicated without any rows that contain 1)

Can anybody help?

Thanks so much

 

[My Aswer Is This]

The script looks for the number 1 in column i
Starting in Row (8)
And deletes any row with just the number 1 not 1284

Sub Make_New_Sheet()
'Modified 2/4/2022  4:36:42 AM  EST
Application.ScreenUpdating = False
On Error GoTo M
Dim i As Long
Dim Lastrow As Long
Dim newName As String
Lastrow = Cells(Rows.Count, "I").End(xlUp).Row

For i = Lastrow To 8 Step -1
    If Cells(i, "I").Value = 1 Then Rows(i).Delete
Next
'Second part
newName = InputBox("Please Enter New Period")


If newName <> "" Then
ActiveSheet.Copy After:=Worksheets(Sheets.Count)
ActiveSheet.Name = newName
End If

Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "We had a Problem"
Application.ScreenUpdating = True

End Sub

 

[shayalsamawi]

Thanks so much!!

 

[My Aswer Is This]

Thanks so much!!

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