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

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!

1MN_LD2527

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

<strike></strike><strike></strike>
741.0309

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

<colgroup><col width="64" style="width:48pt"></colgroup><tbody>
</tbody>
<strike></strike>
2/17/2018 14:06

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
<strike></strike><strike></strike>
1MN_LD2527
<strike></strike>
<strike></strike>
741.0306

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>
<strike></strike>
<strike></strike>
11/21/2018 18:38

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
1MN_LD2527
<strike></strike>
<strike></strike>
741.0298

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>
<strike></strike>
<strike></strike>
3/30/2018 9:54

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
1MN_LD2527
<strike></strike>
<strike></strike>
741.0285

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>
<strike></strike>
<strike></strike>
2/9/2018 12:57

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
1MN_LD2527
<strike></strike>
<strike></strike>
741.0275

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>
<strike></strike>
<strike></strike>
6/5/2018 5:59

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
1MN_LD2527
<strike></strike>
<strike></strike>
741.026

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>
<strike></strike>
<strike></strike>
4/5/2018 21:56

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
1MN_LD2527
<strike></strike>
<strike></strike>
741.0259

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>
<strike></strike>
<strike></strike>
12/18/2018 9:47

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
1MN_LD2527
<strike></strike>
<strike></strike>
741.0254

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>
<strike></strike>
<strike></strike>
10/17/2018 6:40

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
1MN_LD2527
<strike></strike>
<strike></strike>
741.0248

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>
<strike></strike>
<strike></strike>
1/15/2018 7:16

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
1MN_LD2527
<strike></strike>
<strike></strike>
741.0248

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>
<strike></strike>
<strike></strike>
3/1/2018 23:35

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
1MN_LD2527
<strike></strike>
<strike></strike>
741.0239

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>
<strike></strike>
<strike></strike>
12/17/2018 19:55

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
1MN_LD2527
<strike></strike>
<strike></strike>
741.0235

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>
<strike></strike>
<strike></strike>
4/8/2018 16:18

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
1MN_LD2527
<strike></strike>
<strike></strike>
741.0228

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>
<strike></strike>
<strike></strike>
11/28/2018 12:32

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
1MN_LD2527
<strike></strike>
<strike></strike>
741.0222

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>
<strike></strike>
<strike></strike>
4/24/2018 9:05

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>

<tbody>
</tbody>
<strike></strike>
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Joseph1012

New Member
Joined
Apr 9, 2019
Messages
4
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
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
I don't know if I understood correctly but is that what you want?

IDID2DateCustom
1MN_LD2527
741.0306​
21/11/2018 18:38​
Summer
1MN_LD2527
741.0285​
02/09/2018 12:57​
Summer
1MN_LD2527
741.0254​
17/10/2018 06:40​
Summer
1MN_LD2527
741.0235​
04/08/2018 16:18​
Summer
1MN_LD2527
741.0228​
28/11/2018 12:32​
Summer
IDID2DateCustom
1MN_LD2527
741.0309​
17/02/2018 14:06​
Winter
1MN_LD2527
741.0298​
30/03/2018 09:54​
Winter
1MN_LD2527
741.0275​
06/05/2018 05:59​
Winter
1MN_LD2527
741.026​
04/05/2018 21:56​
Winter
1MN_LD2527
741.0259​
18/12/2018 09:47​
Winter
1MN_LD2527
741.0248​
03/01/2018 23:35​
Winter
1MN_LD2527
741.0248​
15/01/2018 07:16​
Winter
1MN_LD2527
741.0239​
17/12/2018 19:55​
Winter
1MN_LD2527
741.0222​
24/04/2018 09:05​
Winter

btw. this is not vba
 

Joseph1012

New Member
Joined
Apr 9, 2019
Messages
4
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".
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

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:

Joseph1012

New Member
Joined
Apr 9, 2019
Messages
4
I have more than this section in my code. I was really just looking to do it in VBA.
Thanks for the info though
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
you didn't show/describe more so I can't say/show more

but no problem
have a nice day
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,130,295
Messages
5,641,395
Members
417,207
Latest member
Vxhaet

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
Top