Macro- To Copy & Paste data based on date value

reyrey

New Member
Joined
Jun 30, 2011
Messages
49
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-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p> </o:p>For example:<o:p></o:p>
<o:p> </o:p>If data contains 06/01/2011 in cell A1, then data needs to be pasted to "Jun" worksheet within the same workbook. <o:p></o:p>
<o:p> </o:p>
Can someone assist or can give some direction? Thank you<o:p></o:p>
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Thanks for the quick reposnse!!! Sorry for not being giving you the details...

Pretty much I have data from "Sheet 1" Columns cell A4 to N4 & Rows A4 to A100 with the date located @ B1.

If cell B1 contains "06/01/2011" then i want to copy range (A4:N100) from "Sheet 1" to existing TAB that's named "Jun".
 
Upvote 0
Only data that contains month format (mm/dd/yyyy)

for example:

if cell B1 contains "01/01/2011" then data needs to be pasted to "Jan" tab
if cell B1 contains "02/01/2011" then data needs to be pasted to "Feb" tab
if cell B1 contains "03/01/2011" then data needs to be pasted to "Mar" tab
 
Upvote 0
Are you saying that the template sheet only has 1 date at a time and that range A4:N100 has the data that corresponds with the date? Is the date always in cell B1?

Just want to be sure.
 
Upvote 0
Code:
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.
 
Last edited:
Upvote 0
Thank you soooo much it works!

I really do appreciate you taking the time to help me out <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top