Macro- To Copy & Paste data based on date value

[reyrey]

Newbie here and I don't know where to start... I'm trying to set up a macro to when data is pasted to a TEMPLATE worksheet tab it will automatically copy and paste data to another sheet based on the month value.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>For example:<o></o>
<o> </o>If data contains 06/01/2011 in cell A1, then data needs to be pasted to "Jun" worksheet within the same workbook. <o></o>
<o> </o>
Can someone assist or can give some direction? Thank you<o></o>

 

[Alphacsulb]

No problem, I'm glad it worked.

 

[sbtricky]

What would the code be for the "paste" line if i wanted to paste values only? (i.e. paste special - values)

Thanks in advance!

 

[Kapill]

Hi there,

I have the same problem, the difference is the range for which I want to copy the data changes every time so will it be possible to include a dialogue box asking for initial and final date and pasting date in between them in designated sheet ?
Thanks in advance

 

[xxrahst]

Sub MoveData()

For Each cell In Range("B1:B1") ' Where to look for the date
If cell.Value = "6/1/2011" Then ' The date
    Range("A4:N100").Select ' What is the range to copy
    Selection.Copy ' Copy it
    Sheets("Jun").Select ' Select the Sheet it applies to
    Range("A1").Select ' Where to paste the data
    ActiveSheet.Paste
    Sheets("Template").Select ' Go back to the templete sheet
    Range("A1").Select
    Application.CutCopyMode = False ' Remove the copy mode
End If
Next ' Move forward to the next

For Each cell In Range("B1:B1")
If cell.Value = "1/1/2011" Then
    Range("A4:N100").Select
    Selection.Copy
    Sheets("Jan").Select
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("Template").Select
    Range("A1").Select
    Application.CutCopyMode = False
End If
Next

For Each cell In Range("B1:B1")
If cell.Value = "2/1/2011" Then
    Range("A4:N100").Select
    Selection.Copy
    Sheets("Feb").Select
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("Template").Select
    Range("A1").Select
    Application.CutCopyMode = False
End If
Next

End Sub

This assumes your template tab is named Template. I did 3 months for you but you can look at my comments to see what the pattern is for the rest. Assign this macro to a button to run it.

Remember to backup your work before you try any macro.

HELLO, Im trying to do the same thing. But in my spread sheet I want the data to be populated on a daily basis with what was entered for that day? Is that possible?

 

[JohnH76]

HI Alphacsulb,

Whilst it has been a while since you have replied to this post, I have very similar question for you, but requires some assistance with adjusting your VBA Coding:

Sub Moving _Data()
1. For Each cell In Range("B1:B1") ' Where to look for the date
2. If cell.Value = "6/1/2011" Then ' The date
3.     Range("A4:N100").Select ' What is the range to copy
4.     Selection.Copy ' Copy it
    >>Sheets("Jun").Select ' Select the Sheet it applies to
1.     Range("A1").Select ' Where to paste the data
2.     ActiveSheet.Paste
3.     Sheets("Template").Select ' Go back to the templete sheet
4.     Range("A1").Select
5.     Application.CutCopyMode = False ' Remove the copy mode
End Sub

I can see what is being done here, such as finding data relating to a date, then copy and pasting the comparable
data to its relevant/corresponding sheet.

I need a similar bit of coding, but I can't seem to see beyond the problem... some kind of, Test If!

I see this could be quite a simple bit of coding for my needs. The first four and final five lines of the code is fine,
but I need to be able to get the code to access one sheet "Template" find the date ("B1" [June-16]) and match that
date against a corresponding row ("B1:B12") in another sheet "Annual", find the matching date ("June-16" [B6]) and paste the
content below that date ("C6:C128").

Are you able to help?

Your time is appreciated,
John