Moving data from one sheet to a template, matching numbers

[PVT]

Hello,

I need help trying to get data from sheet 1 into sheet 2, in the right
spot. Here's what my data looks like:
(sheet 1)

D 240102 x 90 200
D 240103 y 9787 100
D 240107 z 8897 200
D 240110 a 45 640

(blank row)
S 240229 j 4545 100
S 240232 k 544545 200
S 240233 l 454556 124
S 240238 m 45456 135

(blank row)


And here's the template where it needs to go (only the numbers in
columns 4 and 5 need to be filled in underneath each other for the
matching 240xxx number, the template with the numbers in column B is
already filled in)

(sheet 2)
D 240101

D 240102 x 90
200
D 240103 y 9787
100
D 240104

D 240105

D 240106

D 240107 z 8897
200
D 240108

D 240109

D 240110 a 45
640
D 240111

(blank row)
S 240229 j 4545
100
S 240230

S 240231

S 240232 k 544545
200
S 240233 l 454556
124
S 240234

S 240236

S 240237

S 240238 m 45456
135

Possible ranges are 240101 - 240199 and 240229 - 240285.

Any help would be greatly appreciated!!!

 

[joel]

Try this code

Sub MoveData()

With Sheets("Sheet1")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
RowCount = 1
Do While RowCount <= LastRow
ID = .Range("B" & RowCount)
If ID <> "" Then
Num1 = .Range("D" & RowCount)
Num2 = .Range("E" & RowCount)

With Sheets("Sheet2")
Set c = .Columns("B").Find(what:=ID, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Cannot find ID : " & ID)
Else
.Range("D" & c.Row) = Num1
.Range("A" & (c.Row + 1)) = Num2
End If

End With
End If
RowCount = RowCount + 1
Loop

End With

End Su

 

[PVT]

That works like a charm, thank you!!!!

Now let me take it one step further. Say this scenario is repeated 10
times (for 10 different branches, with different numbers for each
branch). So on sheet 1 there is a column A which states the branch
(1-10). On sheet 2, the template is repeated 10 times with in column A
the branch number. How do you build in a loop that compares the branch
number on sheet 1 to the branch number in sheet 2, and repeats the
macro for each branch?

 

[joel]

I modified the code to look for every occurance of the ID number i
column B on sheet 2

Sub MoveData()

With Sheets("Sheet1")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
RowCount = 1
Do While RowCount <= LastRow
ID = .Range("B" & RowCount)
If ID <> "" Then
Num1 = .Range("D" & RowCount)
Num2 = .Range("E" & RowCount)

With Sheets("Sheet2")
Set c = .Columns("B").Find(what:=ID, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Cannot find ID : " & ID)
Else
FirstAddr = c.Address
Do
.Range("D" & c.Row) = Num1
.Range("A" & (c.Row + 1)) = Num2
Set c = .Columns("B").FindNext(after:=c)
Loop While Not c Is Nothing And c.Address <> FirstAddr
End If

End With
End If
RowCount = RowCount + 1
Loop

End With

End Su

 

[PVT]

That was not completely what I meant. Here's how sheet 1 looks now
with two branches:

(sheet 1)

1 D 240102 x 90 200
1 D 240103 y 9787 100
1 D 240107 z 8897 200
1 D 240110 a 45 640

(blank row)
1 S 240229 j 4545 100
1 S 240232 k 544545 200
1 S 240233 l 454556 124
1 S 240238 m 45456 135

(blank row)


This now gets moved to sheet 2 as above but with an additional column
A that has the branch 1 and 2




2 D 240101 x 40 200
2 D 240105 y 87 400
2 D 240107 z 8897 200
2 D 240110 a 45 640

(blank row)
2 S 240229 j 4545 100
2 S 240232 k 544545 200
2 S 240233 l 454556 124
2 S 240240 m 4 35

(blank row)


(sheet 2)
1 D 240101 x 40
200
1 D 240102

1 D 240103 y 9787
100
1 D 240104

1 D 240105

1 D 240106

1 D 240107 z 8897
200
1 D 240108

1 D 240109

1 D 240110 a 45
640
1 D 240111

(blank row)
1 S 240229 j 4545
100
1 S 240230

1 S 240231

1 S 240232 k 544545
200
1 S 240233 l 454556
124
1 S 240234

1 S 240236

1 S 240237

1 S 240238 m 45456
135


2 D 240101

2 D 240102 x 90
200
2 D 240103 y 9787
100
2 D 240104

2 D 240105

2 D 240106

2 D 240107 z 8897
200
2 D 240108

2 D 240109

2 D 240110 a 45
640
2 D 240111

(blank row)
2 S 240229 j 4545
100
2 S 240230

2 S 240231

2 S 240232 k 544545
200
2 S 240233 l 454556
124
2 S 240234

2 S 240236

2 S 240237

2 S 240238 m 45456
135
2 S 240239

2 S 240240 4
35

 

[joel]

I think futher explanation is needed. What do you mean by an additiona
column A. A worksheet cannot have two column A's

 

[PVT]

Sorry if that was unclear. I meant that we are have now extended our
original project by inserting a column A that has the branch number.
This column was inserted both in sheet 1 and sheet 2. If branch=1 in
sheet 1, it is the same in sheet 2. So it goes through branch 1 and
does our original project, then goes through branch 2 and does the
same thing, all the way down. Hopefully that makes more sense?

Thanks again for helping me with this!