Not sure why my previous posting is not shwing up. here is the code again
Sub move_data()
Const FirstRow = 2
Const FirstCol = "A"
Level = InputBox("enter Column Number of Level : ")
StructRowCount = 1
With Sheets("Raw Data")
LastRow = .Cells(Rows.Count, FirstCol).End(xlUp).Row
For RawRowCount = FirstRow To LastRow
For LevelCount = 0 To (Level - 1)
Sheets("Structure"). _
Range("A" & StructRowCount). _
Offset(0, LevelCount).Value = _
.Range(FirstCol & RawRowCount). _
Offset(0, Level - LevelCount - 1). _
Value
Next LevelCount
StructRowCount = StructRowCount + 1
Next RawRowCount
End With
End Sub
"EE" wrote:
> Hi
>
> Thanks for your help in advance. I will try and explain the probelm
> with an example.
>
> Sheet 1: "Raw Data"
>
> Data1 Data2 Data3 Data4 Data5 Data6 Data7 Data8
> Some 1 51 41 75 50 A6 HGH NUM
> Some 2 58 70 62 72 B6 HGH NUM
> Some 3 61 24 74 43 A4 HGH NUM
>
> Sheet 2" "Structure"
>
> Structure6 Structure5 Structure4 Structure3
> Structure2 Structure1
> A6 A5 A4
> A3 A2 A1
> B6 B5 B4
> B3 B2 B1
>
> Column called Data 6 in Sheet 1 corresponds to the data in Seeet 2.
> Lets call it the LEVEL in the structure. My task is to copy all the
> data in each row of Sheet 1 for all LEVELs "Below" the relevant
> structure. For instance, in the first row, the Level is "A6", then in
> a new sheet I would like the entire row copied (many times) and the
> only cell that needs to be changed in each copy is A6. So the first
> row in this example will be repeated 6 times (once as A6, and in the
> other "copied rows", A6 will be replaced by A5, A4, A3, A2, A1 in the
> other 5 rows.)
>
> Just to exapnd on the point, in case of ROW 3, where the level is A4,
> the other 3 rows will have A3, A2, A1.
>
> The final output will look like this.
>
> Data1 Data2 Data3 Data4 Data5 Data6 Data7 Data8
> Some 1 51 41 75 50 A6 HGH NUM
> Some 1 51 41 75 50 A5 HGH NUM
> Some 1 51 41 75 50 A4 HGH NUM
> Some 1 51 41 75 50 A3 HGH NUM
> Some 1 51 41 75 50 A2 HGH NUM
> Some 1 51 41 75 50 A1 HGH NUM
>
> Some 3 61 24 74 43 A4 HGH NUM
> Some 3 61 24 74 43 A3 HGH NUM
> Some 3 61 24 74 43 A2 HGH NUM
> Some 3 61 24 74 43 A1 HGH NUM
>
> (((I used numbers (A6) here but the real data is all TEXT. SO the
> macro needs to look for the value in "data 6" column in the Structure
> sheet and create copies of that entire row with all values in the
> structure to the "right" of the "matching" value in structure
> sheet. ))))
>
> I guess I repeated myself twice or thrice here.
>
> Again thanks for any help / guidance you can provide.
>
> Best Regards
> Prasad
>
>
|
Similar Threads
