Hi
first thanks for @Alex Blakenburg's code
I want prventing add new sheet based on columns D,E,G contains zero or empty together.
if they contain zero values or empty cells together in columns D:E,G , then should show message"please fill numbers for columns D:E,G before adding new sheet" if they contain numeric values except zero , then should add new sheet as the orginal code does it (should be one of the columns contain numeric not should all of the columns contain numeric together as in first case when they contain zero values or empty cells together)
the original code will create new sheet with the same borders and formatting based on previous sheet and increment number for new sheet name .
just I need to add condition based on column D:E,G to prevent add new sheet if column D:E,G equal zero or empty cells .
first thanks for @Alex Blakenburg's code
I want prventing add new sheet based on columns D,E,G contains zero or empty together.
if they contain zero values or empty cells together in columns D:E,G , then should show message"please fill numbers for columns D:E,G before adding new sheet" if they contain numeric values except zero , then should add new sheet as the orginal code does it (should be one of the columns contain numeric not should all of the columns contain numeric together as in first case when they contain zero values or empty cells together)
the original code will create new sheet with the same borders and formatting based on previous sheet and increment number for new sheet name .
just I need to add condition based on column D:E,G to prevent add new sheet if column D:E,G equal zero or empty cells .
VBA Code:
Sub new_report_MthName()
Dim a
Dim sht As Worksheet
Dim shtName As String
Dim NameSuffix As String, NameMain As String
Dim currMth As Date
Set sht = Worksheets(Sheets.Count)
shtName = sht.Name
NameMain = Left(shtName, InStrRev(shtName, " "))
NameSuffix = Right(shtName, Len(shtName) - InStrRev(shtName, " "))
If UCase(NameSuffix) = "DECEMBER" Then
MsgBox "you should create new file"
Exit Sub
End If
On Error Resume Next
currMth = DateValue("1-" & NameSuffix)
If Err = 0 Then
NameSuffix = UCase(MonthName(Month(currMth) + 1))
Else
NameMain = shtName
NameSuffix = " JANUARY"
End If
On Error GoTo 0
sht.Copy After:=Sheets(Sheets.Count)
With ActiveSheet.Cells(1).CurrentRegion.Offset(1)
ActiveSheet.Name = NameMain & NameSuffix
a = .Value
.ClearContents
Cells(2, 1).Resize(UBound(a), 3) = Application.Index(a, Evaluate("row(1:" & UBound(a) & ")"), Array(1, 2, 6))
End With
End Sub