14 Most Current Dates

[Whitewater]

I have a database that keeps weekly prices for some items. In the past the report filled up from 1 -14 date columns and then started a blank new one after that one was full. The users now want it to be a full sheet that always shows the last 14 dates.

Can you point me to the most efficient code that always test for the last 14 and then copies & pastes it to the report worksheet?

[rylo]

Hi

1) What is your database? Spreadsheet, MS Access, or something else.

2) Are your dates duplicated, or will there be only one instance of each date.

Without more detail on your structure, it is going to be hard to do more than give you some general directions...


rylo

[Whitewater]

I'm sorry, I thought I had attached the spreadsheet. The data is stored and inputed in the other worksheet. I'd like the macro to go into this data and take the last 14 column by 20 row range and copy this and paste it over the previous data from the week before. The dates are exclusive going back every Friday's date since 1995.

[rylo]

Hi

have a look at the attached. It uses a dynamic range name to determine the relevant data. The number of rows is fixed. I don't know if that is going to be a problem, but it fits the example.

If the number of rows is going to change, then you could still use the name approach but use a macro to copy and paste the data.


rylo

[Whitewater]

Thanks for getting back so soon. For simplicities sake I attached a scaled down version of the worksheet. The real data in my worksheet resides in row 180 to 218 with a sliding column. What I did today works but I think it is kind of amateursish. I'll include it here but everyone will know I don't do this for a living. Maybe you could recommend some refinements or a total makeover.

Sub UpdateWeek()

    
    Dim lastCol As Integer
    Dim myRange As Range
    Dim p As String
    Dim r As String
    
    
    
    Worksheets("By YEAR").Select
    Range("B180").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Set myRange = Selection
    lastCol = Selection.Columns.Count
    MsgBox (lastCol)
    Range("B179").Select
    Selection.Offset(0, lastCol - 14).Select
    p = Selection.Address
    Selection.Offset(39, 13).Select
    r = Selection.Address
    Range(p, r).Select
    Set myRange = Selection
    Selection.Copy
    Sheets("Report").Select
    Range("B3").Select
    ActiveSheet.Paste
    Sheets("By YEAR").Select
    myRange.Select
    
End Sub

B180 is where the first instance of prices starts. Like I said it works but it might be crude. Thanks again.