Click here to Skip to main content
15,668,646 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Dim wbnew As Workbook
Set wbnew = Workbooks.Add
  Dim datim As String
    datim = Format(CStr(Now), "mm")
Range("D1").Value = Format(Now, "mm")

Range("A2:D2").Merge
Range("A2:D3").Interior.Color = vbYellow
Range("A3:D3").Merge
Range("A3:D3").ColumnWidth = 20
   Range("A2:D2").Select
    ActiveCell.FormulaR1C1 = "Relaxo Footwears Limited"
        Range("A2:D3").Font.Size = 20
  Range("A3:D3").Select
    ActiveCell.FormulaR1C1 = TextBoxN.Value
Range("A2:D3").HorizontalAlignment = xlCenter
'boards
Range("A2:D18").Borders.Weight = 3
Range("A4:D4").Interior.Color = RGB(250, 211, 199)
Range("A4:D4").Font.Bold = True
'Range("A4:E4").HorizontalAlignment = xlCenter
  Range("A4").Select
    ActiveCell.FormulaR1C1 = "Ingredient Name"
     Range("B4").Select
        Range("A1").Font.Size = 18
    ActiveCell.FormulaR1C1 = "Recipe Weight(%)"
       Range("C4").Select
    ActiveCell.FormulaR1C1 = "Recipe Weight(Kg)"
       Range("D4").Select
    ActiveCell.FormulaR1C1 = "Selected Blender"
   
    'INGREDIENT NAME
Range("A5").Value = "ADDITIVE 4007(P-16)"
Range("A6").Value = "ADDITIVE 4030(P-23)"
Range("A7").Value = "ADDITIVE 4034(P-15)"
Range("A8").Value = "ADDITIVE 4080(P-19)"
Range("A9").Value = "ADDITIVE B1123(P-17)"
Range("A10").Value = "ADDITIVE 4012(P-26)"
Range("A11").Value = "ADDITIVE 1028(P-21)"
Range("A12").Value = "ADDITIVE 1593(P-25)"
Range("A13").Value = "ADDITIVE 4023(P-20)"
Range("A14").Value = "ADDITIVE 150D(P-24)"
Range("A15").Value = "WATER(P-22)"

Range("A16").Value = "BASE POLY"
Range("A17").Value = "BLACK PIGMENT(P-30)"
Range("A18").Value = "BEIGE PIGMENT(P-33)"
'
Range("C19:D18").Borders.Weight = 3
Range("C19:D18").Font.Bold = True
Range("C19").Value = "TOTAL BATCH SIZE"
'DISPLAY
Range("B4:B18").HorizontalAlignment = xlCenter
Range("C4:C18").HorizontalAlignment = xlCenter
Range("D4:D18").HorizontalAlignment = xlCenter
wbnew.Worksheets(1).Range("B5").Value = TextBoxR1.Value
wbnew.Worksheets(1).Range("B6").Value = TextBoxR2.Value
wbnew.Worksheets(1).Range("B7").Value = TextBoxR3.Value
wbnew.Worksheets(1).Range("B8").Value = TextBoxR4.Value
wbnew.Worksheets(1).Range("B9").Value = TextBoxR5.Value
wbnew.Worksheets(1).Range("B10").Value = TextBoxR6.Value
wbnew.Worksheets(1).Range("B11").Value = TextBoxR7.Value
wbnew.Worksheets(1).Range("B12").Value = TextBoxR8.Value
wbnew.Worksheets(1).Range("B13").Value = TextBoxR9.Value
wbnew.Worksheets(1).Range("B14").Value = TextBoxR10.Value
wbnew.Worksheets(1).Range("B15").Value = TextBoxR11.Value
wbnew.Worksheets(1).Range("B16").Value = TextBoxR12.Value
wbnew.Worksheets(1).Range("B17").Value = TextBoxR13.Value
wbnew.Worksheets(1).Range("B18").Value = TextBoxR14.Value
wbnew.Worksheets(1).Range("C5").Value = TextBoxC1.Value
wbnew.Worksheets(1).Range("C6").Value = TextBoxC2.Value
wbnew.Worksheets(1).Range("C7").Value = TextBoxC3.Value
wbnew.Worksheets(1).Range("C8").Value = TextBoxC4.Value
wbnew.Worksheets(1).Range("C9").Value = TextBoxC5.Value
wbnew.Worksheets(1).Range("C10").Value = TextBoxC6.Value
wbnew.Worksheets(1).Range("C11").Value = TextBoxC7.Value
wbnew.Worksheets(1).Range("C12").Value = TextBoxC8.Value
wbnew.Worksheets(1).Range("C13").Value = TextBoxC9.Value
wbnew.Worksheets(1).Range("C14").Value = TextBoxC10.Value
wbnew.Worksheets(1).Range("C15").Value = TextBoxC11.Value
wbnew.Worksheets(1).Range("C16").Value = TextBoxC12.Value
wbnew.Worksheets(1).Range("C17").Value = TextBoxC13.Value
wbnew.Worksheets(1).Range("C18").Value = TextBoxC14.Value
wbnew.Worksheets(1).Range("D19").Value = TextBoxA.Value


 If TextBoxB1.Value = 1 Then
 Range("D5").Value = "Blender 1 Select"
ElseIf TextBoxB2.Value = 1 Then
  Range("D6").Value = "Blender 2 Select"
ElseIf TextBoxB3.Value = 1 Then
  Range("D7").Value = "Blender 3 Select"
  ElseIf TextBoxB4.Value = 1 Then
  Range("D8").Value = "Blender 4 Select"
  ElseIf TextBoxB5.Value = 1 Then
  Range("D9").Value = "Blender 5 Select"
    Else
 End If
 if Range("D1").Value<>datim then


 
 ActiveWorkbook.SaveAs "C:\Users\RCS\Desktop\RE\" & datim & ""
    ActiveWorkbook.Close
   else 


What I have tried:

i try all condition to genrate monthly new report but not genrate on same month
Posted
Updated 14-Mar-23 2:21am

1 solution

Check if the file exists (use the Dir function (Visual Basic for Applications) | Microsoft Learn[^] ) and if it does, don't try to save. That way, the MessageBox never appears ...
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900