LDiers
asked on
How can I use Excel VBA to copy cells from one sheet to another and transposing data to a list
Hi.
I'm tryin to figure out how to copy selected cells from one sheet to another and at the sametime transposing the data structure.
I would like to transpose from this:
to this:
Sofar I'm working on a makro e.g this:
Sincerely :-)
Example.xlsm
I'm tryin to figure out how to copy selected cells from one sheet to another and at the sametime transposing the data structure.
I would like to transpose from this:
to this:
Sofar I'm working on a makro e.g this:
Sub copy()
Dim strSeek As String
Dim strSeekWeek As String
Const strSourceSheet As String = "RawData"
Const strDestSheet As String = "Data"
Dim r As Integer
Dim found As Integer
Dim rng As Range
Dim c As Object
Dim lrow As Integer
Dim SletData As Range
Dim i As Integer
'delete
Sheets(strDestSheet).Range("A2:A5000").EntireRow.Delete
'select data
Sheets(strSourceSheet).Select
Range("A1", Range("A" & Rows.Count).End(xlUp)).Select
Set rng = Selection
'copy For Each c In rng
Sheets(strDestSheet).Range("A1").Offset(r, 0).Value = c.Offset(0, 0).Value
Sheets(strDestSheet).Range("A1").Offset(r, 1).Value = c.Offset(0, 1).Value
Sheets(strDestSheet).Range("A1").Offset(r, 2).Value = c.Offset(0, 2).Value
Sheets(strDestSheet).Range("A1").Offset(r, 3).Value = c.Offset(0, 3).Value
Sheets(strDestSheet).Range("A1").Offset(r, 4).Value = c.Offset(0, 4).Value
Sheets(strDestSheet).Range("A1").Offset(r, 5).Value = c.Offset(0, 5).Value
Sheets(strDestSheet).Range("A1").Offset(r, 6).Value = c.Offset(0, 6).Value
'missing out here
r = r + 1
Next c
MsgBox "Data copy", vbOKOnly
End Sub
but i'm having troble to handle the loops around each dataset in the "rawdata" section (every 15 rows are related to each other) and missing the code to built the new structureSincerely :-)
Example.xlsm
Do you need the days of the week names translated?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great solution, much more simple! Thank you so much