Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Macro- To Copy & Paste data based on date value

  1. #1
    New Member
    Join Date
    Jun 2011
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Macro- To Copy & Paste data based on date value

    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.
    For example:
    If data contains 06/01/2011 in cell A1, then data needs to be pasted to "Jun" worksheet within the same workbook.

    Can someone assist or can give some direction? Thank you

  2. #2
    Board Regular Alphacsulb's Avatar
    Join Date
    Mar 2008
    Posts
    398
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro- To Copy & Paste data based on date value

    What data is pasted into the "Jun" worksheet if the condition is true, all data in row 1?

    Use: http://www.excel-jeanie-html.de/html/down_en.php or something similar to give us an example of what it looks like.

  3. #3
    New Member
    Join Date
    Jun 2011
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro- To Copy & Paste data based on date value

    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".

  4. #4
    Board Regular Alphacsulb's Avatar
    Join Date
    Mar 2008
    Posts
    398
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro- To Copy & Paste data based on date value

    Only "06/01/2011", or anything in June?

  5. #5
    New Member
    Join Date
    Jun 2011
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro- To Copy & Paste data based on date value

    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

  6. #6
    Board Regular Alphacsulb's Avatar
    Join Date
    Mar 2008
    Posts
    398
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro- To Copy & Paste data based on date value

    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.

  7. #7
    New Member
    Join Date
    Jun 2011
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro- To Copy & Paste data based on date value

    Correct.... it only has one date located on B1 in template sheet

  8. #8
    Board Regular Alphacsulb's Avatar
    Join Date
    Mar 2008
    Posts
    398
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro- To Copy & Paste data based on date value

    *** duplicate post ***
    Last edited by Alphacsulb; Jul 14th, 2011 at 02:11 PM. Reason: opps.

  9. #9
    Board Regular Alphacsulb's Avatar
    Join Date
    Mar 2008
    Posts
    398
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro- To Copy & Paste data based on date value

    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 by Alphacsulb; Jul 14th, 2011 at 02:10 PM.

  10. #10
    New Member
    Join Date
    Jun 2011
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro- To Copy & Paste data based on date value

    Thank you soooo much it works!

    I really do appreciate you taking the time to help me out

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •