Copy rows and paste based on a value in an excel cell

Question

Copy rows and paste based on a value in an excel cell

Navigation

#1 by (0 votes)

0

I would like to know how I can copy a set of lines. For example, line 1 through 5 and paste in lines 10 through 15. This action would be done with value 1 on another sheet and in cell A1.

I have a button with VBA to do this, but I wanted to do it automatically when the cell value is "28" or "29".

These are the codes I have button. One adds and the other removes the lines.

Private Sub CommandButton1_Click()

Sheets("Fevereiro").Rows("5:9").Copy (Sheets("Fevereiro").Rows("11:15"))

End Sub

Private Sub CommandButton2_Click()

Worksheets("Fevereiro").Rows("11:15").Delete

End Sub

This is to try to do the following: The month of February now has 28 days or 29. I have a FIMMÊS formula to give me the exact day if the month ends on 28 or 29 depending on the year.

If from one year to another the month has one more day, VBA adds a certain number of rows in the "February" sheet (just below the last row filled in) or removes if the next year is only 28 again.

excel vba

asked by anonymous 01.11.2018 / 17:30

1 answer

Aligning twiter bootstrap buttons inside a Table mPdf ignores inline css style

score 0 · Answer 1

The simplest way to check the number of days in a month is as follows:

Ano = 2018
Mes = 2
DiasNoMes = Day(DateSerial(Ano, Mes + 1, 0))
MsgBox DiasNoMes

That returns the number 28, that is, the month of February 2018 has 28 days.

Then a if conditional logic can be created with:

If DiasNoMes = 28 Then
    Sheets("Fevereiro").Rows("5:9").Copy (Sheets("Fevereiro").Rows("11:15"))
ElseIf DiasNoMes = 29 Then
    Worksheets("Fevereiro").Rows("11:15").Delete
End If