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!

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

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?

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
 
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
Copy sheet with same row values to another sheet
Replies
2
Views
742
MARK858
MARK858
S
  • Question
Formula to Copy Data from one workbook and paste to every 2nd column in a different workbook
Replies
1
Views
553
JustynaMK
JustynaMK
L
  • Question
Macro - copying data between sheets
Replies
3
Views
570
Fluff
Fluff
R
  • Question
Copy rows from sheet1 to sheet2 based on date.
Replies
2
Views
725
MARK858
MARK858
M
  • Question
VBA Copy data from filtered table to another worksheet but keep some columns of original?
Replies
0
Views
324
Melimob
M

Forum statistics

Threads
1,214,379
Messages
6,119,190
Members
448,874
Latest member
Lancelots

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