copying values that change weekly into another sheet

[sgeslava]

Hi!
I need some help with a code that copies values from one sheet, and paste them into another sheet. But the thing is that the values in the original sheet change weekly, so basically I want to keep record of the each week of the year in the other tab.
I would really appreciate any comments or questions,
Thank you in advance

Sebastian

 

[cjcobra]

I'm thinking you don't need code to copy and paste into a new sheet. Maybe just Copy and Paste the values into a new sheet.

 

[zjocko]

Yes you can manually copy and paste but that defeats the purpose of his question. Can you do it automatically (like a data entry form) so that when you enter it on Sheet1 it will automattically enter it on the first unused cell in a column on Sheet2. Then next time you enter a number on sheet1 it will automattically add the number on sheet2 below the last entered number. I have the same issue only I do it monthly instead of weekly and haven't been able to find a solution.

 

[GaryWMn]

How about this?

Sub MoveThisWeek()
'
' MoveThisWeek Macro
'
' Keyboard Shortcut: Ctrl+Shift+T
'
' Preconditions
' - Range for current data row is named "ThisWeek"
' - Range for top left cell of destination table is named "Target"
 
    Range("ThisWeek").Copy 
    With Range("Target").End(xlDown).Offset(1, 0)
        .PasteSpecial Paste:=xlPasteValues
        .PasteSpecial Paste:=xlPasteFormats
    End With
    Application.CutCopyMode = False

End Sub

Let us know if this works for you.

Gary

 

[sgeslava]

Thank you GaryWMn for your comment, but what is happening is that they put data every day (the data is numbers), and there is a column at the end that adds the daily data.
So i need to paste that last column (weekly column) into the other sheet.

I was thinking of using an IF function that would identify the current day and paste the weekly column into the other sheet, for the week that corresponds.. so if they put data on monday, tuesday, etc.. it will paste that weekly column in the other sheet every time they input data in that week.
it was something like this:
If #6/20/2011# <= Date And Date <= #6/24/2011# < Date Then
but the problem with this is that I would have to do that IF condition for all the weeks in the year, so its really long and I dont know how many IFs functions I can have.

The other thing I was thinking about, was to put a Button and tell the guys entering the data to push it when the week is over. And just assign a macro to that button to copy the weekly data and paste it on the other sheet in the next blank cell in the column.

So, I would really appreciate if anyone knows what can I do to do this easier,
Thank you!
Sincerely,

Sebastian