copying cell info from one worksheet to another based on a specific date

[VishalHPatel]

Is this possible?

I have 2 worksheets in an excel file. Sheet 2 updates everyday and is
fed from an input schedule. Every quarter end i would like to copy
that days info into Sheet 1. So for example, all through jan, feb and
march information is being fed into Sheet 2. However, on MARCH 31 I
would like to take the information shown in Sheet 2 and paste that info
into Sheet 1 (pretty much taking a snapshot of that quarters data).
Once April 1st hits, Sheet 2 goes about its business collecting data
thats being fed and Sheet 1 retains the info it took on March 31.

The idea is at the end of every quarter Sheet 1 populates info copied
and pasted from Sheet 2.......

Can anyone help me???

Cheers.

 

[Guest]

This is untried but I think I have it right. This assumes the quarter-end
code will always run on the exact last day of the quarter. If last business
day is required, additional Conditions will have to be added.

If TEXT(Today(), "m/d" = "3/31" _
Or TEXT(Today(), "m/d" = "6/30" _
Or TEXT(Today(), "m/d" = "9/30" Then
Sheets(2).Activate
Cells.Copy
Sheets(1).Activate
Range("A1").PasteSpecial Paste:=xlValues
End if

 

[Guest]

I should have tried it first. I will reply again when I have worked out the
"bugs".

Sorry,

Jim

 

[Guest]

Sub Testit()
If Date = "3/31/2006" _
Or Date = "6/30/2006" _
Or Date = "9/30/2006" Then
Sheets(2).Activate
Cells.Copy
Sheets(1).Activate
Range("A1").PasteSpecial Paste:=xlValues
End If

End Sub

 

[Guest]

This takes care of the year problem.

Sub Testit()
Sheets(2).Activate
Range("Z1").Activate
ActiveCell = "=TEXT(Today(),""m/d"")"
If ActiveCell.Value = "3/31" _
Or ActiveCell.Value = "6/30" _
Or ActiveCell.Value = "9/30" Then
Sheets(2).Activate
Cells.Copy
Sheets(1).Activate
Range("A1").PasteSpecial Paste:=xlValues
End If
End Sub

 

[VishalHPatel]

This looks great Jim, thanks for your help.

Just another question:

Say in Sheet 1 I have 4 sets of cells for quarter 1, 2, 3, and 4. So
on march 31 it copies from Sheet 2 and pastes in (sheet1) CELL SET 1
for quarter 1, on June 30 it copies from Sheet 2 and pastes in (sheet1)
CELL SET 2 for quarter 2 and so on.

Cheers.

 

[VishalHPatel]

Also, I only need a range of cells in Sheet 2 to be copied, not the
whole sheet....I really appreciate your help...thanks a bunch

 

[Guest]

If there is a specific range in sheets1 and 2 this will work.

Sheets(2).Activate
Range("Z1").Activate
ActiveCell = "=TEXT(Today(),""m/d"")"
x = Activecell
Range("A1:Q300").copy
Sheets(2).Activate
if x = "3/31" then
Range(firstquarter range).pastespecial paste:=xlValues
elseif x = "6/30" then
Range(secondquarter range).PasteSpecial paste:=xlValues
elseif x = "9/30" then
Range(thirdquarter range).PasteSpecial paste:=xlValues
Else
Range(fourthquarter range).PasteSpecial paste:=xlValues
end if

 

[VishalHPatel]

Thanks Jim,

I had it set so the individual would have to pick the query they want
for each quarter...this is so much more efficient....lol.

Thanks a lot.