Hello ,
what I look for when there is no any data inside the form sheet and select month from combobox then should copy to FORM sheet from the first time ignoring all of conditions inside the code.
be carful don't change any condition inside the code I really need it.
just when clear data inside sheet and try to copy selected month from combobox from first time when there is no data inside the sheet ,then should copy to inside the sheet , after there is data in sheet will implement as the original code does it .
here is the procedure what I'm talking about .
also posted here
copy selected month from combobox for just from first time when there is no data
what I look for when there is no any data inside the form sheet and select month from combobox then should copy to FORM sheet from the first time ignoring all of conditions inside the code.
be carful don't change any condition inside the code I really need it.
just when clear data inside sheet and try to copy selected month from combobox from first time when there is no data inside the sheet ,then should copy to inside the sheet , after there is data in sheet will implement as the original code does it .
here is the procedure what I'm talking about .
also posted here
copy selected month from combobox for just from first time when there is no data
VBA Code:
Private Sub CommandButton1_Click()
Dim i As Long
Dim lastrow As Long
Dim sheet2 As Worksheet
Dim currentMonth As Date
Dim copyDate As Date
Dim remainingDays As Long
Dim headersWritten As Boolean
Set sheet2 = ThisWorkbook.Sheets("FORM") ' Replace with the correct sheet name if necessary
' lastrow line moved down
' Check if headers are already written
headersWritten = (sheet2.Cells(1, 1).Value = "MONTH" And sheet2.Cells(1, 2).Value = "ITEM" _
And sheet2.Cells(1, 3).Value = "NAME" And sheet2.Cells(1, 4).Value = "BALANCE")
' Write headers if not already present
If Not headersWritten Then
sheet2.Cells(1, 1).Value = "MONTH"
sheet2.Cells(1, 2).Value = "ITEM"
sheet2.Cells(1, 3).Value = "NAME"
sheet2.Cells(1, 4).Value = "BALANCE"
End If
' Determine the last row
lastrow = sheet2.Cells(sheet2.Rows.Count, 1).End(xlUp).Row
' Check if this month has already been copied
currentMonth = WorksheetFunction.EoMonth(Date, -1) + 1
Dim alreadyCopied As Boolean, previousCopied As Boolean, prevMonth As Date
prevMonth = WorksheetFunction.EoMonth(Date, -2) + 1
alreadyCopied = False
previousCopied = False
For i = 2 To lastrow
' If sheet2.Cells(i, 1).Value = currentMonth Then
' alreadyCopied = True
' Exit For
' End If
' check if previous month or current month was copied
If sheet2.Cells(i, 1).Value = prevMonth Then previousCopied = True
If sheet2.Cells(i, 1).Value = currentMonth Then alreadyCopied = True
Next i
If alreadyCopied Then
MsgBox "The data has already been copied for this month!", vbExclamation
Exit Sub
End If
If previousCopied = False Then
MsgBox "The data for " & Format(prevMonth, "Mmm-yy") _
& " cannot be found." & vbCr & "This will be copied first of all", vbExclamation
' jump to placeholder to insert data
GoTo DoPrevious
'Exit Sub
End If
' Check if today is within the allowed days
copyDate = Date
If Day(copyDate) < 27 Then
remainingDays = 27 - Day(copyDate)
MsgBox "Warning: You need to wait " & remainingDays & " more days to copy the data.", vbExclamation
Exit Sub
ElseIf Day(copyDate) >= 27 And Day(copyDate) <= 31 Then
' placeholder for copying missed previous month
DoPrevious:
' Copy data from ListBox1 to Sheet2
With Me.ListBox1
' write headers if not already done
If headersWritten Then
sheet2.Cells(lastrow + 1, 1).Value = "MONTH"
sheet2.Cells(lastrow + 1, 2).Value = "ITEM"
sheet2.Cells(lastrow + 1, 3).Value = "NAME"
sheet2.Cells(lastrow + 1, 4).Value = "BALANCE"
Else
' adjust lastrow to avoid gap for first data set
lastrow = 0
End If
' determine which month to record
If previousCopied = False Then
currentMonth = prevMonth
End If
' write month data
For i = 1 To .ListCount - 1
sheet2.Cells(lastrow + i + 1, 1).Value = currentMonth
sheet2.Cells(lastrow + i + 1, 2).Value = .List(i, 0)
sheet2.Cells(lastrow + i + 1, 3).Value = .List(i, 1)
sheet2.Cells(lastrow + i + 1, 4).Value = .List(i, 2)
' lastrow = lastrow + 1
Next i
' refresh the screen
Application.ScreenUpdating = True
End With
MsgBox "Data successfully copied for " & Format(currentMonth, "Mmm-yy"), vbInformation
Else
MsgBox "Warning: You can only copy data between the 27th and 31st of the month.", vbExclamation
Exit Sub
End If
End Sub