Macros - Copy data from one worksheet to another with date constraint

[audiman09]

Hi everyone, I am fairly new to excel and need help with excel programming / macros.
I am project manager and trying to develop project financial tracking sheet.

I have created a sheet to track "Project Forecast effort" and "Project Actual effort". At the start of the project I create project forecast (in $ & effort in days) based on my scope and then every week I update my actuals in "Project Actual effort" and then manually adjust "Project forecast sheet". This takes very long time and get challenging for a project with large number of resources.
I was wondering if any one can help me automate this process.

Attached below is a snap short from my Actuals and Forecast sheet

Labour Actuals:

[TABLE="width: 616"]
<colgroup><col style="width: 77pt; mso-width-source: userset; mso-width-alt: 3766;" span="8" width="103"> <tbody>[TR]
[TD="class: xl74, width: 412, bgcolor: #D9D9D9, colspan: 4"]Week Ending Sunday Enter Effort in Days (1 day = 8 hrs)[/TD]
[TD="class: xl74, width: 412, bgcolor: #D9D9D9, colspan: 4"]Week Ending Sunday Enter Effort in Days (1 day = 8 hrs)[/TD]
[/TR]
[TR]
[TD="class: xl73, width: 103, bgcolor: #D9D9D9"]04-Jul-12[/TD]
[TD="class: xl70, width: 103, bgcolor: #D9D9D9"]11-Jul-12[/TD]
[TD="class: xl70, width: 103, bgcolor: #D9D9D9"]18-Jul-12[/TD]
[TD="class: xl70, width: 103, bgcolor: #D9D9D9"]25-Jul-12[/TD]
[TD="class: xl70, width: 103, bgcolor: #D9D9D9"]01-Aug-12[/TD]
[TD="class: xl70, width: 103, bgcolor: #D9D9D9"]08-Aug-12[/TD]
[TD="class: xl70, width: 103, bgcolor: #D9D9D9"]15-Aug-12[/TD]
[TD="class: xl70, width: 103, bgcolor: #D9D9D9"]22-Aug-12[/TD]
[/TR]
[TR]
[TD="class: xl72, width: 103, bgcolor: #C5D9F1"]4.0[/TD]
[TD="class: xl72, width: 103, bgcolor: #C5D9F1"]4.0[/TD]
[TD="class: xl72, width: 103, bgcolor: #C5D9F1"]6.0[/TD]
[TD="class: xl72, width: 103, bgcolor: #C5D9F1"]7.0[/TD]
[TD="class: xl72, width: 103, bgcolor: #C5D9F1"]5.0[/TD]
[TD="class: xl72, width: 103, bgcolor: #C5D9F1"][/TD]
[TD="class: xl72, width: 103, bgcolor: #C5D9F1"][/TD]
[TD="class: xl72, width: 103, bgcolor: #C5D9F1"][/TD]
[/TR]
</tbody>[/TABLE]


Forecast Data:
[TABLE="width: 616"]
<colgroup><col style="width: 77pt; mso-width-source: userset; mso-width-alt: 3766;" span="8" width="103"> <tbody>[TR]
[TD="class: xl71, width: 412, bgcolor: #D9D9D9, colspan: 4"]Week Ending Sunday Enter Effort in Days (1 day = 8 hrs)[/TD]
[TD="class: xl71, width: 412, bgcolor: #D9D9D9, colspan: 4"]Week Ending Sunday Enter Effort in Days (1 day = 8 hrs)[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 103, bgcolor: #D9D9D9"]04-Jul-12[/TD]
[TD="class: xl69, width: 103, bgcolor: #D9D9D9"]11-Jul-12[/TD]
[TD="class: xl69, width: 103, bgcolor: #D9D9D9"]18-Jul-12[/TD]
[TD="class: xl69, width: 103, bgcolor: #D9D9D9"]25-Jul-12[/TD]
[TD="class: xl69, width: 103, bgcolor: #D9D9D9"]01-Aug-12[/TD]
[TD="class: xl69, width: 103, bgcolor: #D9D9D9"]08-Aug-12[/TD]
[TD="class: xl69, width: 103, bgcolor: #D9D9D9"]15-Aug-12[/TD]
[TD="class: xl69, width: 103, bgcolor: #D9D9D9"]22-Aug-12[/TD]
[/TR]
[TR]
[TD="class: xl74, width: 103, bgcolor: #C5D9F1"]2.0[/TD]
[TD="class: xl74, width: 103, bgcolor: #C5D9F1"]3.0[/TD]
[TD="class: xl74, width: 103, bgcolor: #C5D9F1"]5.0[/TD]
[TD="class: xl74, width: 103, bgcolor: #C5D9F1"]6.0[/TD]
[TD="class: xl74, width: 103, bgcolor: #C5D9F1"]7.0[/TD]
[TD="class: xl74, width: 103, bgcolor: #C5D9F1"]8.0[/TD]
[TD="class: xl74, width: 103, bgcolor: #C5D9F1"]4.0[/TD]
[TD="class: xl74, width: 103, bgcolor: #C5D9F1"]6.0[/TD]
[/TR]
</tbody>[/TABLE]


What I would like to do is to copy data from first one upto 01-Aug-12 into second sheet but not overwrite data in forecast sheet beyond that 01-Aug (current date).

would greatly appreciate help.

Regards

 

[John Davis]

Does this help?

Sub audiman09()

Range("A3").Select

Do While ActiveCell.Value <> ""

    ActiveCell.Offset(, 1).Select
    
Loop

Range(Range("A3"), ActiveCell.Offset(, -1)).Copy Sheets("Forecast").Range("A3")

End Sub

 

[audiman09]

Thanks for your help John.
It didn't really work but then probably I am doing it wrong. I copy and paste these instructions in a Marco (using record marco option) and then tried it. nothing happend.
I am very new to macros and so probably doing it wrong. Also I want todo this for all 15 resources in my actual sheet. will it work?

Does this help?

Sub audiman09()

Range("A3").Select

Do While ActiveCell.Value <> ""

    ActiveCell.Offset(, 1).Select
    
Loop

Range(Range("A3"), ActiveCell.Offset(, -1)).Copy Sheets("Forecast").Range("A3")

End Sub

 

[John Davis]

It worked when I tested it. Did you run it from the Actuals sheet? What are you're sheet names?

 

[audiman09]

Thanks for helping me with this one John.
The sheet names are "Labour Actuals" and Labour Forecast".

I did try to run it from "Labour Actuals" sheet.
I selected View-Macross-Record Macros
Macro Name= Copy_Actuals
and in Description copy and paste your instructions.
Then Stop Recording
View Macros & finally RUN... nothing happens.

BTW I tried renaming the sheets as "Actuals" and "forecast" but still no result.