VBA to loop through columns and stack/reorder

[wagon_rider]

Hi everyone,
I would like like to take two tables of data and make them into a long list.
(rows 7-102 is data "in") and (105 to 200 is data "out")
Columns are days of the week for a quarter.

I am trying to make one column with:
day1, 00:15, in
day1, 00:30, in
(every 15mins) for the day
day1, 00:15, out
day1, 00:30, out
then starting on the next column in the table day 2
day 2, 00:15, out

Sub list()
'
' list Macro
'
Columns("A:H").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
'set headers
Range("A1").Value = "time"
Range("B1").Value = "date"
Range("C1").Value = "idcolumn"
Range("D1").Value = "datetimecolumn"
    Range("E1").Value = "cash"
' Time

Range("A2:A97").Value = Range("I7:I102").Value
Range("A98:A193").Value = Range("I7:I102").Value

'date
Range("B2:B193").Value = Range("J6").Value
Range("D2").Select
Selection.NumberFormat = "yyyy-mm-ddThh:mm:ss"
ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-3]"
Range("D2").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("D2:D193")
With Range("D2:D193").CurrentRegion
.Value = .Value
End With


'id
Range("C2:C97").Value = [I6] & ActiveSheet.Name
Range("C98:C193").Value = [I104] & ActiveSheet.Name

'Values
Range("E2:E97").Value = Range("J7:J102").Value
Range("E98:E193").Value = Range("J105:J200").Value
 
End Sub

I have managed one column but I have lost confidence / steam.... with looping through the other 90 columns! perhaps someone can assist please?

I have made code to insert rows in front of my data tables, and with a date/time and in/out column







I have been out of the business of writing vba for sometime, but today I'm trying.... badly...

 

[wagon_rider]

you can see "money out" starts in a separate table below:

 

[wagon_rider]

Many thanks in advance for any help

 

[lousymc]

Try this. I have modified your code.

Sub list()



lastdate = Range(Range("J6"), Range("J6").End(xlToRight)).Columns.Count

lastdate = lastdate * 5

x = 0

For i = 1 To lastdate Step 5


' list Macro
'
Range(Columns(i), Columns(i + 4)).Select

'set headers
Cells(1, i).Value = "time"
Cells(1, i + 1).Value = "date"
Cells(1, i + 2).Value = "idcolumn"
Cells(1, i + 3).Value = "datetimecolumn"
Cells(1, i + 4).Value = "cash"
' Time

Range(Cells(2, i), Cells(97, i)).Value = Range(Cells(7, i + 8), Cells(102, i + 8)).Value

Range(Cells(98, i), Cells(193, i)).Value = Range(Cells(7, i + 8), Cells(102, i + 8)).Value


'date
Range(Cells(2, i + 1), Cells(193, i + 1)).Value = Cells(6, i + 9 + x).Value



Range("D2").Select

Cells(2, i + 3).Select
Selection.NumberFormat = "yyyy-mm-ddThh:mm:ss"
ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-3]"
Cells(2, i + 3).Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range(Cells(2, i + 3), Cells(193, i + 3))

With Range(Cells(2, i + 3), Cells(193, i + 3)).CurrentRegion
.Value = .Value
End With


'id

Range(Cells(2, i + 2), Cells(97, i + 2)).Value = Cells(6, i + 8).Value & ActiveSheet.Name

Range(Cells(98, i + 2), Cells(193, i + 2)).Value = Cells(104, i + 8).Value & ActiveSheet.Name


'Values

Range(Cells(2, i + 4), Cells(97, i + 4)).Value = Range(Cells(7, i + 9), Cells(102, i + 9 + x)).Value

Range(Cells(98, i + 4), Cells(105, i + 4)).Value = Range(Cells(105, i + 9), Cells(200, i + 9 + x)).Value
 

    If i < lastdate - 5 Then
        Range(Columns(i + 5), Columns(i + 9)).Select
        Selection.Insert Shift:=xlToRight
    End If

x = x + 1

Next i

End Sub

 

[wagon_rider]

It's not exactly what I was after though I think i can work with it, Thank you.. Going to work through what you have done and I will report back!

 

[wagon_rider]

Ok I'm getting sort of somewhere...!

I'm stuck at using j to paste my range to the end in columns B and C. Col A works well!

I'm trying to break the bits down to get back into coding - so below is only some of the steps of above :D


my end goal is to have one list of 3 column, time, day and value, stacked in col a,b,c
1.4.19 in
1.4.19 out
2.4.20 in
2.4.20 out
3.4.20 in
3.4.20 out
(91 days in total - but variable)

Sub mondaylistloopuse()

Dim i As Integer '(cols)
Dim j As Integer '(rows)

'find table size
lastdate = Range(Range("j6"), Range("j6").End(xlToRight)).Columns.Count
cols = lastdate * 192


x = 0
For j = 1 To lastdate
For i = 1 To cols Step 96

Range(Cells(i, 1), Cells(i + 95, 1)).Value = Range(Cells(7, 9), Cells(102, 9)).Value 'copytimes to first section
Range(Cells(i + 96, 1), Cells(i + 191, 1)).Value = Range(Cells(7, 9), Cells(102, 9)).Value 'copy times to second section
'above works

'Values
Range(Cells(i, 3), Cells(i + 95, 3)).Value = Range(Cells(7, j + 9), Cells(102, j + 9)).Value 'copydata to first section
Range(Cells(i + 96, 3), Cells(i + 191, 3)).Value = Range(Cells(7, j + 9), Cells(102, j + 9)).Value 'copy data to second section

'Range(Cells(i, 2), Cells(i + 191, 2)).Value = Cells(6, i + 9).Value

Next i
Next j

Thank you so much for teh assistance. i know this is trival to the right person!!!

 

[lousymc]

How about this.

Sub mondaylistloopuse()

Dim i As Integer '(cols)
Dim j As Integer '(rows)

'find table size
lastdate = Range(Range("j6"), Range("j6").End(xlToRight)).Columns.Count
'cols = lastdate * 192

i = 1

For j = 1 To lastdate


Range(Cells(i, 1), Cells(i + 95, 1)).Value = Range(Cells(7, 9), Cells(102, 9)).Value 'copytimes to first section
Range(Cells(i + 96, 1), Cells(i + 191, 1)).Value = Range(Cells(7, 9), Cells(102, 9)).Value 'copy times to second section
'above works

'Values
Range(Cells(i, 3), Cells(i + 95, 3)).Value = Range(Cells(7, j + 9), Cells(102, j + 9)).Value 'copydata to first section
Range(Cells(i + 96, 3), Cells(i + 191, 3)).Value = Range(Cells(105, j + 9), Cells(200, j + 9)).Value 'copy data to second section


Range(Cells(i, 2), Cells(i + 191, 2)).Value = Cells(6, j + 9).Value

i = Cells(Rows.Count, 1).End(xlUp).Row + 1

Next j


End Sub

 

[wagon_rider]

Hurrah! a box of cyber roses to you

Thank you so much!!