Macro or Formula to Copy Value in Next Available Blank Cell

[jeversf]

Hi All

I've got a spreadsheet that is updated weekly and requires values to be copied and pasted in it's respective week ending cell.

My challenge is to come up with a quick method of pasting the data into it's respective cell. Ideally, I would like to click on a macro button and have it do the lookup / pasting straight away but am open to using a lookup formula.

In the attached example the EARNED TO DATE values in column A (A13, A27, A41 etc.), need to be carried over to it's corresponding week ending cell i.e. A13 -> F12, A27 -> F26 etc.

copy&paste in next blank cell.xlsx

Any help would be greatly appreicated.

[p24leclerc]

try the following macro. As I saw no date in your sheet, I based the macro on today's date to determine the week to transfer data to.

Please Login or Register  to view this content.

[jeversf]

Thank you very much for that Pierre, as someone who knows nothing about macros you explained it very well and it seems to be working fine. You do raise a good point about the date though!

Typically I'd update the report every week with a Friday cut-off. If I were to put the Friday cut-off date in cell B2, how would you modify the code you sent to make provision for that?

Thanks once again.

[p24leclerc]

as the date in B2 will always be a friday (the end of a week) the macro will be simpler

Please Login or Register  to view this content.

[jeversf]

So I tried using the code you provided, in the original file I need the macro to work in, and tweaked some of your code to match one or two changes I made (i.e. data to be copied is in another cell and renamed tab) [see below in red] and it doesn't appear to work now. Is there something else I should have changed/updated? I've attached another sample showing what the master file looks like now.

Public Sub Transfer_Data()
Dim End_Of_Week_Date As Date, EOW_Found As Range
'Determine the end of this week (next Friday)
'If we are on weekend, then Friday of next week
If Weekday(Now(), vbMonday) <= 5 Then
End_Of_Week_Date = Now() + 5 - Weekday(Now(), vbMonday)
Else
End_Of_Week_Date = Now() + 12 - Weekday(Now(), vbMonday)
End If
End_Of_Week_Date = DateSerial(Year(End_Of_Week_Date), Month(End_Of_Week_Date), Day(End_Of_Week_Date))
'
'Locating the column to transfer the data
Set EOW_Found = Sheets("Table").Rows(3).Find(End_Of_Week_Date)
'If we found the right column then transfer data
If Not EOW_Found Is Nothing Then
EOW_Found.Offset(9, 0) = Range("A12")
EOW_Found.Offset(9 + 14, 0) = Range("A26")
EOW_Found.Offset(9 + 28, 0) = Range("A40")
EOW_Found.Offset(9 + 42, 0) = Range("A54")
EOW_Found.Offset(9 + 56, 0) = Range("A68")
EOW_Found.Offset(9 + 70, 0) = Range("A82")
End If
End Sub

copy&paste in next blank cell2.xlsm

[p24leclerc]

here is you workbook modifed to work with a date in cell B2 which MUST be a friday.

[jeversf]

Works like a charm! Thanks for all your help.