Copy rows multiple times changing value of a cell in one column each time

[EE]

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

 

[Guest]

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