Need VBA code for Copy+insert / Delete rows & columns by criteria

[lico13]

Hi,

Can you please help? I need it urgently.

I need a VBA code solution for copy+insert / delete rows & columns by criteria (attached an example Excel file):

When type "1" in column A next to a certain row and press the macro button, row 8 (the example row) will be copied entirely and
INSERTED above the selected row.
When type "0" in column A next to the selected row and press the macro button, the selected row will be entirely deleted.

If it's OK, I would like to add another request:
When pressing the 2nd macro button, 3 columns with previous month dates will be deleted entirely (at the left)
and at the same time 3 columns with new month dates will be added with all formulas in them (at the right).

*** Note that I need these 2 macros for multiple sheets with identical table structure so I think it will be best to write the codes just once and put macro buttons to the same macros.

Thanks in advance :)

[Geetha Gupta]

try this :

1. When type "1" in column A next to a certain row and press the macro button, row 8 (the example row) will be copied entirely and
INSERTED above the selected row.


With ActiveSheet
If Range("A" & ActiveCell.Row) = "1" Then
.Rows(8).Copy
Range("A" & ActiveCell.Row).EntireRow.Insert
.paste Destination:=.Rows(ActiveCell.Row)
End If
End With



When type "0" in column A next to the selected row and press the macro button, the selected row will be entirely deleted.


With ActiveSheet
If Range("A" & ActiveCell.Row) = "0" Then Range("A" & ActiveCell.Row).EntireRow.Delete
End With


Regards

[lico13]

Thanks a lot Geetha :)

Is there a chance to get help also for the 2nd question?

[Geetha Gupta]

Actually i use excel 2000. so haven't seen your file

but try this.

first, activate the sheet in which this macro will work



Sub DtCols()
Dim rCurrMon As Range
Dim iMonth As Integer

'there should be a date in the cell.
Set rCurrMon = Range("M1") 'or whichever is the cell with the current month in your file
iMonth = Month(rCurrMon)


'startDelete
'there should be dates in the 3 prreceeding cells, in the same row
Dim i As Double
For i = 1 To 3
If Month(rCurrMon) - Month(rCurrMon.Offset(0, -1)) <= 3 Then
Columns(rCurrMon.Offset(0, -1).Column).Delete
End If
Next


'start insert date
Dim dNewDt As Date
For i = 1 To 3
dNewDt = DateAdd("m", i, rCurrMon)
rCurrMon.Offset(0, i) = dNewDt
Next


End Sub

[lico13]

Geetha, Thanks a lot! Appreciate it very much :):)