Copy row base on date, paste to another sheet by month

jimayers

Board Regular
Joined
Nov 14, 2010
Messages
99
I am trying to copy a row of information based on a date in column "H" in sheet 1, then paste/insert that row into the month specific section in sheet 2. Sheet 2 has 12 sections for the 12 months. So on sheet 2, range("H1: H8") is a header, with a big "January" in "H4" where every row below is pasted data from sheet 1 for January. AND so on!

I loop using
For y = 5 to Lastrow
....For counterx = 1 to 12
I am attempting to loop 12 times in every row of data in sheet 1 checking for one of the 12 months, then copying the row into sheet 2
I have been trying to use month() and /or monthname() with no success.

Anyhelp would be appreciated.
Thanks - Jim
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,415
Office Version
  1. 2013
Platform
  1. Windows
Here is an example of how I would do it.

Code:
Dim c As Range, i As Long
For i = 1 To 12
    With Sheets("Sheet1")
        For Each c In .Range("H5", .Cells(Rows.Count, 8).End(xlUp))
            If Month(c.Value) = i Then
                Select Case i
                    Case 1
                        'copy paste to January
                    Case 2
                        'copy paste to February
                    'on through Case 12
                End Select
            End If
        Next
    End With
Next
 

jimayers

Board Regular
Joined
Nov 14, 2010
Messages
99
Thank you...that worked. Although the code was a lot longer than excpected as I needed to write the copy/paste code for each of the 12 cases.
But I'm up and running now - yahoo!:)
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,626
Messages
5,523,978
Members
409,549
Latest member
agus setiawan

This Week's Hot Topics

Top