VBA macro to copy row data into columns

[James2011]

Hi, I'm looking for an excel macro to dynamically copy row data and to paste into columns

The dataset below is a simplified version as the real version has a over a hundred names and spans 5 years hence it needs a macro to automate.


Copy from Sheet 1

Paste into Sheet2

I've searched for hours online looking for solutions but nothing hits the spot, any suggestions please?

Thanks
James

 

[My Aswer Is This]

Try this:
Run this script from sheet1:

Sub Copy_Data()
'Modified  11/18/2020  1:43:23 AM  EST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Dim LastColumn As Long
Dim Lastrowa As Long
Lastrowa = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row

    For i = 2 To Lastrow
        LastColumn = Sheets(1).Cells(i, Columns.Count).End(xlToLeft).Column
      
        Sheets(2).Cells(Lastrowa, 1).Resize(LastColumn - 2).Value = Sheets(1).Cells(i, 1).Value
        Sheets(2).Cells(Lastrowa, 2).Resize(LastColumn - 2).Value = Sheets(1).Cells(i, 2).Value

        Sheets(1).Cells(1, 3).Resize(, LastColumn - 2).Copy
        Sheets(2).Cells(Lastrowa, 3).PasteSpecial xlPasteValues, Transpose:=True

        Sheets(1).Cells(i, 3).Resize(, LastColumn - 2).Copy
        Sheets(2).Cells(Lastrowa, 4).PasteSpecial xlPasteValues, Transpose:=True
        Lastrowa = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row + 1
Next
Application.ScreenUpdating = True
End Sub

 

[sandy666]

hence it needs a macro to automate

really?

look at this

Source									Result
Name	Type	Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20		Name	Type	Date	Value
James	Contractor	10	20	30	40	50	60		James	Contractor	Jan-20	10
John	Employee	20	40	60	80	100	120		James	Contractor	Feb-20	20
Karen	Employee	30	60	90	120	150	180		James	Contractor	Mar-20	30
									James	Contractor	Apr-20	40
									James	Contractor	May-20	50
									James	Contractor	Jun-20	60
									John	Employee	Jan-20	20
									John	Employee	Feb-20	40
									John	Employee	Mar-20	60
									John	Employee	Apr-20	80
									John	Employee	May-20	100
									John	Employee	Jun-20	120
									Karen	Employee	Jan-20	30
									Karen	Employee	Feb-20	60
									Karen	Employee	Mar-20	90
									Karen	Employee	Apr-20	120
									Karen	Employee	May-20	150
									Karen	Employee	Jun-20	180

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"Name", "Type"}, "Date", "Value")
in
    Unpivot

 

[Dossfm0q]

Formula

Book1
	A	B	C	D	E	F	G	H
1	Name	Type	20-Jan	20-Feb	20-Mar	20-Apr	20-May	20-Jun
2	James	Contractor	10	20	30	40	50	60
3	John	Employee	20	40	60	80	100	120
4	Karen	Employee	30	60	90	120	150	180
Sheet1

Book1
	A	B	C	D
1	Name	Type	Date	Value
2	James	Contractor	20-Jan	10
3	James	Contractor	20-Feb	20
4	James	Contractor	20-Mar	30
5	James	Contractor	20-Apr	40
6	James	Contractor	20-May	50
7	James	Contractor	20-Jun	60
8	John	Employee	20-Jan	20
9	John	Employee	20-Feb	40
10	John	Employee	20-Mar	60
11	John	Employee	20-Apr	80
12	John	Employee	20-May	100
13	John	Employee	20-Jun	120
14	Karen	Employee	20-Jan	30
15	Karen	Employee	20-Feb	60
16	Karen	Employee	20-Mar	90
17	Karen	Employee	20-Apr	120
18	Karen	Employee	20-May	150
19	Karen	Employee	20-Jun	180
Sheet2

Cell Formulas
Range	Formula
A2:A19	A2	=INDIRECT(ADDRESS((CEILING(ROW()-ROW(A$2)+1,6)/6)+1,COLUMN(A$2),1,1,"Sheet1"))
B2:B19	B2	=INDIRECT(ADDRESS((CEILING(ROW()-ROW($B$2)+1,6)/6)+1,COLUMN(Sheet2!B2),1,1,"Sheet1"))
C2:C19	C2	=TEXT((INDIRECT(ADDRESS(1,(MOD((ROW()-ROW($C$1))-1,6)+1)+2,1,1,"Sheet1"))),"dd-mmm")
D2:D19	D2	=INDIRECT(ADDRESS((CEILING(ROW()-ROW($D$2)+1,6)/6)+1,(MOD((ROW()-ROW($D$1))-1,6)+1)+2,1,1,"Sheet1"))

 

[James2011]

Thank you for the responses, three completely different approaches so lots to think about though it probably depends on the amount of data. The formula method is best with relatively small amounts of data, the macro was ok posting 50,000 rows but struggled at 100,000 and in theory the power pivot approach could be simplest once you understand power pivots.

 

[sandy666]

in theory the power pivot approach could be simplest once you understand power pivots

this is not power pivot but power query.

Power Query documentation - Power Query

Power Query is used to filter, combine, and mash up data from one or more supported data sources.

docs.microsoft.com

 

[My Aswer Is This]

Thank you for the responses, three completely different approaches so lots to think about though it probably depends on the amount of data. The formula method is best with relatively small amounts of data, the macro was ok posting 50,000 rows but struggled at 100,000 and in theory the power pivot approach could be simplest once you understand power pivots.

So you had 100,000 rows and 5 years of columns 12 columns per year for a total of 60 columns?
I never use Power Query.

 

[Fluff]

Another macro, which should be able to handle that amount of data

Sub James()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, nr As Long
   
   Ary = Sheets("Sheet1").Range("A2").CurrentRegion.Value2
   ReDim Nary(1 To UBound(Ary) * UBound(Ary, 2), 1 To 4)
   
   For r = 2 To UBound(Ary)
      For c = 3 To UBound(Ary, 2)
         nr = nr + 1
         Nary(nr, 1) = Ary(r, 1)
         Nary(nr, 2) = Ary(r, 2)
         Nary(nr, 3) = Ary(1, c)
         Nary(nr, 4) = Ary(r, c)
      Next c
   Next r
   Sheets("sheet2").Range("A2").Resize(nr, 4).Value = Nary
End Sub