Explore our sites

Stack Exchange
tour help
Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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
share|improve this question
 
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 Oct 23 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 Oct 23 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 Oct 23 at 23:37
 
Above A3 on all 3 sheets –  Nrandazzo Oct 23 at 23:47
add comment

2 Answers

up vote 0 down vote accepted

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
share|improve this answer
 
Thank you. I see now that adding a variable there can keep those cells fixed. This worked. –  Nrandazzo Oct 24 at 0:39
 
no problem sir :) –  Jaycal Oct 24 at 2:59
add comment
up vote 0 down vote

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.