Excel vba macro to insert rows only functioning properly on first worksheet

Question

I have a macro to insert row of data above a date if that date isn't today. It then adds today's date in the date column. This is done across 30 worksheets but only the first worksheet is properly adding a row in the correct place and adding a date in the right cell. The row should be inserted above A3" on all of them but the rest insert a row above "A4". Below is the current code i am using, i think it may be a range problem but had no luck when testing that.

    Sub UpdatePrices()
    Dim ws As Worksheet, Ldate As String, DateRng As Range
Set DateRng = Sheets("AXP").Range("A3")    'date range is last date
Ldate = DateRng.Value    'defines ldate as most recent date


For Each ws In ThisWorkbook.Worksheets
    ws.Select
    'Inserts a new row with containing today's Date and exclude sheets
    If Ldate <> Date And UCase(ws.Name) <> "DATA" And UCase(ws.Name) <> "UPDATE" Then
        ws.Rows(DateRng.Row).EntireRow.Insert
        ws.Cells(DateRng.Row, DateRng.Column).Offset(-1, 0) = Date
    End If

Next

End Sub

confused by your explanation here.. "The row should be inserted above A3" on all of them but the rest insert a row above "A4"". Do you mean the row should be inserted above A3 on the first worksheet and above A4 for all other sheets? — Jaycal
– Jaycal, Commented Oct 23, 2013 at 23:28
Sorry what i meant was all sheets should have a row inserted above A3, but only the first sheet which is AXP does so, the rest of the sheets insert their row above A4 — Nrandazzo
– Nrandazzo, Commented Oct 23, 2013 at 23:31
Still not making sense. Let's do an example.... If your workbook has 3 sheets, where should the row be inserted on each of those 3 sheets...? — Jaycal
– Jaycal, Commented Oct 23, 2013 at 23:37

Jaycal · Accepted Answer · 2013-10-24 00:05:54Z

1

When you insert a row, the row of your DateRng increases from 3 to 4. You should instead store the original row and column numbers in another variable so it doesn't change

DateRngRow = Sheets("AXP").Range("A3").Row    'date range is last date
DateRngCol = Sheets("AXP").Range("A3").Column
Ldate = Sheets("AXP").Range("A3").Value    'defines ldate as most recent date


For Each ws In ThisWorkbook.Worksheets
    ws.Select
    'Inserts a new row with containing today's Date and exclude sheets
    If Ldate <> Date And UCase(ws.Name) <> "DATA" And UCase(ws.Name) <> "UPDATE" Then
        ws.Rows(DateRngRow).EntireRow.Insert
        ws.Cells(DateRngRow, DateRngCol) = Date
    End If

Next

answered Oct 24, 2013 at 0:05

Jaycal

2,0871 gold badge13 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nrandazzo Over a year ago

Thank you. I see now that adding a variable there can keep those cells fixed. This worked.

user2140261 · Accepted Answer · 2013-10-24 00:14:09Z

The below code is working code under your specification. Also in the below code are a few VERY minor speed increases to your original code: Only call the Date function once, only check if last date is less then date once.

You say you will always insert a row above 3 and that the new rows column A should have the date, the below code will do that.

Sub UpdatePrices()
Dim ws As Worksheet
Dim Ldate As Date
Dim Today As Date
Dim DateRng As Range

Set DateRng = Sheets("Sheet1").Range("A3")    'date range is last date
Ldate = CDate(DateRng.Value)    'defines ldate as most recent date
Today = Date

If Ldate < Today Then

For Each ws In ThisWorkbook.Worksheets
    ws.Select
    'Inserts a new row with containing today's Date and exclude sheets

    If UCase(ws.Name) <> "DATA" And UCase(ws.Name) <> "UPDATE" Then
        ws.Rows(3).EntireRow.Insert
        ws.Cells(3, 1) = Today
    End If

Next

End If
End Sub

Collectives™ on Stack Overflow

Excel vba macro to insert rows only functioning properly on first worksheet

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related