Insert rows in many sheets
 

Hi! I have a workbook that is basically a database. On each sheet there is a
list of dates in column A. the rest of the columns have values corresponding
to that date. Now sometimes there is a date missing. If I want to add data to
that date I must insert a new row in every worksheet. Is there some way I can
use a macro to insert the row? Assume I want to insert 2007-02-03. I would
then search column A in every sheet for the date before ie 2007-02-02 and if
I could not find that I would search for 2007-02-01. Once found I would
insert a row. Is this possible? any help appreciated! thanks!

Insert rows in many sheets
 

Arne,

You could do that but a far simpler solution would be to put your new data
on the last row and sort by column A which you could do with a macro if you
have lots of sheets.

Mike

"Arne Hegefors" wrote:

Hi! I have a workbook that is basically a database. On each sheet there is a
list of dates in column A. the rest of the columns have values corresponding
to that date. Now sometimes there is a date missing. If I want to add data to
that date I must insert a new row in every worksheet. Is there some way I can
use a macro to insert the row? Assume I want to insert 2007-02-03. I would
then search column A in every sheet for the date before ie 2007-02-02 and if
I could not find that I would search for 2007-02-01. Once found I would
insert a row. Is this possible? any help appreciated! thanks!

Insert rows in many sheets
 

Sub add_date()

NewDate = DateValue("1/28/2007")
For Each sht In ThisWorkbook.Sheets

Set c = sht.Columns("A:A").Find(what:=NewDate, _
LookIn:=xlValues)
If c Is Nothing Then
RowCount = 1
Do While sht.Range("A" & RowCount) < NewDate And _
sht.Range("A" & RowCount) < ""

RowCount = RowCount + 1
Loop
sht.Rows(RowCount).Insert
If RowCount = 1 Then
sht.Range("A1") = NewDate
Else
sht.Range("A" & (RowCount)) = NewDate
End If
End If
Next sht

End Sub


"Mike H" wrote:

Arne,

You could do that but a far simpler solution would be to put your new data
on the last row and sort by column A which you could do with a macro if you
have lots of sheets.

Mike

"Arne Hegefors" wrote:

Hi! I have a workbook that is basically a database. On each sheet there is a
list of dates in column A. the rest of the columns have values corresponding
to that date. Now sometimes there is a date missing. If I want to add data to
that date I must insert a new row in every worksheet. Is there some way I can
use a macro to insert the row? Assume I want to insert 2007-02-03. I would
then search column A in every sheet for the date before ie 2007-02-02 and if
I could not find that I would search for 2007-02-01. Once found I would
insert a row. Is this possible? any help appreciated! thanks!