Copy and Pasting Days into different columns

S

segran

Active Member
Joined
Aug 20, 2004
Messages
335
Hi,

I have 2 columns - one with day of the week and the other with data. Each day of the week has 24 data points associated with it.

<html><head><title>Excel Jeanie HTML</title></head><body>

<!-- ######### Start Created Html Code To Copy ########## -->

Excel Workbook
JK
1DayEritrea
2Saturday15.6
315
414.4
514.7
616.8
720
825.7
928
1029.6
1131.88
1232.95
1332.5
1429
1526.95
1627.01
1727.3
1827.4
1930.2
2036.94
2138.72
2236.762
2333.336
2424.5
2521.5
26Tuesday15.8
2714.9
2814.5
2914.7
3015.8
3120.6
3227.4
3329.4
3431.2
3533.3
3636.65
3736.57
3833.11
3929.9
4029.04
4130.19
4230.9
4334.5
4441.7
4542.722
4641.347
4737.84
4833.125
4923.1
50Sunday17.5
5116.8
5216.3
5317.6
5417
5515.7
5623.695
5723.378
5826.52
5927.349
6029.213
6128.916
6227.999
6324.396
6427.233
6523.284
6622.832
6722.507
6834.2
6934.2
7035.4
7129
7222.3
7317.8
74Wednesday17.8
7515.1
7614.3
7714.8
7815.3
7915.8
8027.5
8128.1
8231.7
8332.8
8433.5
8533.3
8629
8725.5
8826
8926.4
9032.6
9136.4
9240.306
9339.808
9431.2
9524.4
9621.7
9721.7
98Friday15.4
9914.7
10014.5
10114.5
10216.6
10318.6
10427.5
10528.6
10631.9
10732.8
10833.2
10933.3
11029.2
11129.4
11226.8
11328.7
11430.5
11529.1
11635.3
11739.632
11836.836
11935.725
12025.2
12120.5
Hourly Data




<!-- ######### End Created Html Code To Copy ########## -->

</body></html>


I would appreciate your assistance in copying and pasting all Monday 24 set of data next to each other. For Example, if there was 10 Mondays in the list, there would the 10 x 24 data points. I want the 24 data points for each day the rest of the week.

Thanking you in advance for your assistance.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
Code: 
Sub G()

    Dim i As Long, j As Long, sMonth As String
    Dim arr As Variant, arr2() As Variant
    
    arr = Sheets("Sheet1").Range("A1").CurrentRegion
    
    j = 1
    sMonth = arr(1, 1)
    For i = 1 To UBound(arr, 1)
        If Not IsEmpty(arr(i, 1)) Then
            If arr(i, 1) = "Monday" Then
                sMonth = "Monday"
            Else
                sMonth = vbNullString
            End If
        End If
        If sMonth = "Monday" Then
            ReDim Preserve arr2(1 To j)
            arr2(j) = arr(i, 2)
            j = j + 1
        End If
    Next
    
    ' Transfer result.
    Sheets.Add.Range("A1").Resize(UBound(arr2), 1) = WorksheetFunction.Transpose(arr2)

End Sub
 
Upvote 0
Hi,

I get a run time error. Also, the data is in sheet called Hourly Data, and the data is in column J and K. Please assist.

Thank you
 
Upvote 0
No problem! Data is copied to worksheet "Hourly Data" to cell "A1". You can adjust this target cell.

Code: 
Sub G()

    Dim i As Long, j As Long, sMonth As String
    Dim arr As Variant, arr2() As Variant
    
    arr = Sheets("Sheet1").Range("J1").CurrentRegion
    
    j = 1
    sMonth = arr(1, 1)
    For i = 1 To UBound(arr, 1)
        If Not IsEmpty(arr(i, 1)) Then
            If arr(i, 1) = "Monday" Then
                sMonth = "Monday"
            Else
                sMonth = vbNullString
            End If
        End If
        If sMonth = "Monday" Then
            ReDim Preserve arr2(1 To j)
            arr2(j) = arr(i, 2)
            j = j + 1
        End If
    Next
    
    ' Transfer result.
    Sheets("Hourly Data").Range("A1").Resize(UBound(arr2), 1) = WorksheetFunction.Transpose(arr2)

End Sub
 
Upvote 0
Hi,

Sorry, but I still receive runtime error - this part gets highlighted in yellow:

Sheets("Hourly Data").Range("A1").Resize(UBound(arr2), 1) = WorksheetFunction.Transpose(arr2)

Also, the data is in Hourly data sheet in column J (the day of week) and column k (the data).

I do not want the data to be copied into A1 of the same sheet, but into another sheet.

Please assist, as I am not sure what is wrong.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
My formula is unnecessarily copied to the adjacent cell when entering data into another cell
Replies
6
Views
328
MrsGrayMarlin
M
H
  • Solved
Macro to extract text to Body in Email and Table at end of Body
Replies
1
Views
621
howard
H
H
  • Question Question
Date from CSV file not copying & pasting correctly. Day and Month are reversed.
Replies
1
Views
191
jkpieterse
jkpieterse
J
  • Question Question
Assistance changing VBA code to display as HTML or picture of a selection in Lotus Notes
Replies
2
Views
233
jularobert
J
itsgrady
  • Solved
Copy Last 10 Cells from A Table and Paste into Another Table.
Replies
5
Views
246
itsgrady
itsgrady

Forum statistics

Threads
1,224,586
Messages
6,179,718
Members
452,939
Latest member
WCrawford

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