Copying a row from one sheet to another based on Month and Sorted

J

Joseph1012

New Member
Joined
Apr 9, 2019
Messages
4
Hi,
This is my first post, so forgive me if I don't post the question the wrong way. I am trying to take a lot of data and split it into Summer and Winter Values, then sort the data in column B in descending order. I was thinking about creating two new sheets one Summer and one Winter and then sorting the data. I am not sure how to move the rows to the new sheet based on the Month Value.

If the Month is December to May the row should be written to Winter. If the Month is June to November it should go to Summer. I am given data for every min of the year. Below is a small subset of what the .csv file looks like.

I think this would be done with a loop but I am not sure how.
Thanks for any help you can give me on this!

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]1MN_LD2527[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD]<strike></strike><strike></strike>[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]741.0309[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"></colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: center"][/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[TABLE="width: 250"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]2/17/2018 14:06[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[/TR]
[TR]
[TD]<strike></strike><strike></strike>
1MN_LD2527​
<strike></strike>
[/TD]
[TD]<strike></strike>[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]741.0306[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD]<strike></strike>[TABLE="width: 113"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]11/21/2018 18:38[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[/TR]
[TR]
[TD]
1MN_LD2527​
<strike></strike>
[/TD]
[TD]<strike></strike>[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]741.0298[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD]<strike></strike>[TABLE="width: 113"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]3/30/2018 9:54[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[/TR]
[TR]
[TD]
1MN_LD2527​
<strike></strike>
[/TD]
[TD]<strike></strike>[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]741.0285[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD]<strike></strike>[TABLE="width: 113"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]2/9/2018 12:57[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[/TR]
[TR]
[TD]
1MN_LD2527​
<strike></strike>
[/TD]
[TD]<strike></strike>[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]741.0275[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD]<strike></strike>[TABLE="width: 113"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]6/5/2018 5:59[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[/TR]
[TR]
[TD]
1MN_LD2527​
<strike></strike>
[/TD]
[TD]<strike></strike>[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]741.026[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD]<strike></strike>[TABLE="width: 113"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]4/5/2018 21:56[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[/TR]
[TR]
[TD]
1MN_LD2527​
<strike></strike>
[/TD]
[TD]<strike></strike>[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]741.0259[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD]<strike></strike>[TABLE="width: 113"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]12/18/2018 9:47[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[/TR]
[TR]
[TD]
1MN_LD2527​
<strike></strike>
[/TD]
[TD]<strike></strike>[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]741.0254[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD]<strike></strike>[TABLE="width: 113"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]10/17/2018 6:40[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[/TR]
[TR]
[TD]
1MN_LD2527​
<strike></strike>
[/TD]
[TD]<strike></strike>[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]741.0248[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD]<strike></strike>[TABLE="width: 113"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]1/15/2018 7:16[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[/TR]
[TR]
[TD]
1MN_LD2527​
<strike></strike>
[/TD]
[TD]<strike></strike>[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]741.0248[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD]<strike></strike>[TABLE="width: 113"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]3/1/2018 23:35[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[/TR]
[TR]
[TD]
1MN_LD2527​
<strike></strike>
[/TD]
[TD]<strike></strike>[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]741.0239[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD]<strike></strike>[TABLE="width: 113"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]12/17/2018 19:55[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[/TR]
[TR]
[TD]
1MN_LD2527​
<strike></strike>
[/TD]
[TD]<strike></strike>[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]741.0235[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD]<strike></strike>[TABLE="width: 113"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]4/8/2018 16:18[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[/TR]
[TR]
[TD]
1MN_LD2527​
<strike></strike>
[/TD]
[TD]<strike></strike>[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]741.0228[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD]<strike></strike>[TABLE="width: 113"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]11/28/2018 12:32[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[/TR]
[TR]
[TD]
1MN_LD2527​
<strike></strike>
[/TD]
[TD]<strike></strike>[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]741.0222[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD]<strike></strike>[TABLE="width: 113"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]4/24/2018 9:05[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
This is what I have so far, based off of a similar project I found on-line. This still is not working though.
Code: 
Sub Test2()
Dim b2 As Workbook
Set b2 = ThisWorkbook
xrowx = 1
datecol = F
Do While xrowx <= WorksheetFunction.CountA(b2.Sheets(1).Range("A:A"))

mnt = datecol
If mnt = "01" Then
    emptyrow = WorksheetFunction.CountA(b2.Sheet2).Range("A:A") + 1
    col = 1
    Do While col <= datecol
    b2.Sheets2.Cells(emptyrow, col) = b2.Sheets(1).Cells(emptyrow, col)
    col = col + 1
    Loop
ElseIf mnt = "02" Then
    emptyrow = WorksheetFunction.CountA(b2.Sheets(3).Range("A:A") + 1)
    col = 1
    Do While col <= datecol
    b2.Sheet3.Cells(emptyrow, col) = b2.Sheets(1).Cells(emptyrow, col)
    col = col + 1
    Loop
xrowx = xrowx + 1
End If
Loop
End Sub
 
Upvote 0
I don't know if I understood correctly but is that what you want?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]ID[/td][td=bgcolor:#70AD47]ID2[/td][td=bgcolor:#70AD47]Date[/td][td=bgcolor:#70AD47]Custom[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]1MN_LD2527[/td][td=bgcolor:#E2EFDA]
741.0306​
[/td][td=bgcolor:#E2EFDA]
21/11/2018 18:38​
[/td][td=bgcolor:#E2EFDA]Summer[/td][/tr]

[tr=bgcolor:#FFFFFF][td]1MN_LD2527[/td][td]
741.0285​
[/td][td]
02/09/2018 12:57​
[/td][td]Summer[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]1MN_LD2527[/td][td=bgcolor:#E2EFDA]
741.0254​
[/td][td=bgcolor:#E2EFDA]
17/10/2018 06:40​
[/td][td=bgcolor:#E2EFDA]Summer[/td][/tr]

[tr=bgcolor:#FFFFFF][td]1MN_LD2527[/td][td]
741.0235​
[/td][td]
04/08/2018 16:18​
[/td][td]Summer[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]1MN_LD2527[/td][td=bgcolor:#E2EFDA]
741.0228​
[/td][td=bgcolor:#E2EFDA]
28/11/2018 12:32​
[/td][td=bgcolor:#E2EFDA]Summer[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]ID[/td][td=bgcolor:#70AD47]ID2[/td][td=bgcolor:#70AD47]Date[/td][td=bgcolor:#70AD47]Custom[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]1MN_LD2527[/td][td=bgcolor:#E2EFDA]
741.0309​
[/td][td=bgcolor:#E2EFDA]
17/02/2018 14:06​
[/td][td=bgcolor:#E2EFDA]Winter[/td][/tr]

[tr=bgcolor:#FFFFFF][td]1MN_LD2527[/td][td]
741.0298​
[/td][td]
30/03/2018 09:54​
[/td][td]Winter[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]1MN_LD2527[/td][td=bgcolor:#E2EFDA]
741.0275​
[/td][td=bgcolor:#E2EFDA]
06/05/2018 05:59​
[/td][td=bgcolor:#E2EFDA]Winter[/td][/tr]

[tr=bgcolor:#FFFFFF][td]1MN_LD2527[/td][td]
741.026​
[/td][td]
04/05/2018 21:56​
[/td][td]Winter[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]1MN_LD2527[/td][td=bgcolor:#E2EFDA]
741.0259​
[/td][td=bgcolor:#E2EFDA]
18/12/2018 09:47​
[/td][td=bgcolor:#E2EFDA]Winter[/td][/tr]

[tr=bgcolor:#FFFFFF][td]1MN_LD2527[/td][td]
741.0248​
[/td][td]
03/01/2018 23:35​
[/td][td]Winter[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]1MN_LD2527[/td][td=bgcolor:#E2EFDA]
741.0248​
[/td][td=bgcolor:#E2EFDA]
15/01/2018 07:16​
[/td][td=bgcolor:#E2EFDA]Winter[/td][/tr]

[tr=bgcolor:#FFFFFF][td]1MN_LD2527[/td][td]
741.0239​
[/td][td]
17/12/2018 19:55​
[/td][td]Winter[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]1MN_LD2527[/td][td=bgcolor:#E2EFDA]
741.0222​
[/td][td=bgcolor:#E2EFDA]
24/04/2018 09:05​
[/td][td=bgcolor:#E2EFDA]Winter[/td][/tr]
[/table]

btw. this is not vba
 
Upvote 0
That is sort of what I am looking for. I was going to have the summer values go to a page called "Summer", and the Winter values to "Winter". I then would need to sort the values so that the max for the Summer and Winter are both in the same location on the respective sheets. So Summer max would be at "Summer B1".
 
Upvote 0
If you want use PowerQuery (as me above) you can put each table to own sheet called as you wish in place wherever you wish, it can be B1
you can sort in PowerQuery Editor so don't need to sort in the sheet

here is a M-Code for PQ
(supported by Excel 2010/2013 add-in, Excel 2016 and higher Get&Transform - built-in)

Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type datetime}}, "en-US"),
    #"Inserted Month" = Table.AddColumn(#"Changed Type with Locale", "Month", each Date.Month([Date]), Int64.Type),
    #"Added Conditional Column" = Table.AddColumn(#"Inserted Month", "Custom", each if [Month] < 6 then "Winter" else if [Month] > 11 then "Winter" else "Summer"),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Custom] = "Summer")),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"ID2", Order.Descending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Month"})
in
    #"Removed Columns"[/SIZE]

this is filtered for Summer
then duplicate table in PQ Editor and filter for Winter
 
Last edited:
Upvote 0
I have more than this section in my code. I was really just looking to do it in VBA.
Thanks for the info though
 
Upvote 0
you didn't show/describe more so I can't say/show more

but no problem
have a nice day
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
Copy sheet with same row values to another sheet
Replies
2
Views
904
MARK858
MARK858
S
  • Question Question
Formula to Copy Data from one workbook and paste to every 2nd column in a different workbook
Replies
1
Views
658
JustynaMK
JustynaMK
L
  • Question Question
Macro - copying data between sheets
Replies
3
Views
674
Fluff
Fluff
R
  • Question Question
Copy rows from sheet1 to sheet2 based on date.
Replies
2
Views
871
MARK858
MARK858
M
  • Question Question
VBA Copy data from filtered table to another worksheet but keep some columns of original?
Replies
0
Views
396
Melimob
M

Forum statistics

Threads
1,223,903
Messages
6,175,279
Members
452,630
Latest member
OdubiYouth

Share this page

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