Pasting Q
 

I received the following code from Tom Olgivy which extracts values in
worksheet Recipes A9;B9;J28;K28;L28;N28 and O28 and pastes these values in
worksheet Master Costs starting at A1:G1 and works down depending on the
last value in Recipes A:

I want now to continue 'pasting' in Master Costs, but this time the 'source
data' comes from a worksheet called 'Meals'. My 'Meals' worksheet has the
same layout that is in the Recipe worksheet except I want to start pasting
these values from Meals worksheet whever the pasting stops in the code
below. The ending row obviously changes depending on the data in Recipes. -
I'm lost as to where I should place this code within the code below

Thanks



Sub Master_Cost_Post()
Dim i As Long, j As Long, k As Long, l As Long
Dim rng As Range, cell As Range
With Worksheets("Recipes")
Set rng = Union(.Range("A9"), .Range("B9"), .Range("J28"), .Range("K28"),
..Range("L28"), .Range("N28"), .Range("O28"))

i = 0
j = 0
l = 0
For Each cell In rng
j = cell.Row
k = 1
l = l + 1
Do While Not IsEmpty(.Cells(j, cell.Column))
.Cells(j, cell.Column).Copy
Worksheets("Master Costs") _
.Cells(k, l).PasteSpecial xlValues
k = k + 1
j = j + 22
Loop
Next
End With

Pasting Q
 

John,

Not tested, but I think this will work

Sub Master_Cost_Post()
Dim i As Long, j As Long, k As Long, l As Long
Dim rng As Range, cell As Range
With Worksheets("Recipes")
Set rng = Union(.Range("A9"), .Range("B9"), .Range("J28"), .Range("K28"),
..Range("L28"), .Range("N28"), .Range("O28"))

i = 0
j = 0
l = 0
For Each cell In rng
j = cell.Row
l = l + 1
k = Worksheets("Master Costs").cellS(Rows.Count, l).End(xlUp).Row + 1
Do While Not IsEmpty(.cellS(j, cell.Column))
.cellS(j, cell.Column).Copy
Worksheets("Master Costs") _
.cellS(k, l).PasteSpecial xlValues
k = k + 1
j = j + 22
Loop
Next
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"John" wrote in message
...
I received the following code from Tom Olgivy which extracts values in
worksheet Recipes A9;B9;J28;K28;L28;N28 and O28 and pastes these values in
worksheet Master Costs starting at A1:G1 and works down depending on the
last value in Recipes A:

I want now to continue 'pasting' in Master Costs, but this time the
'source
data' comes from a worksheet called 'Meals'. My 'Meals' worksheet has the
same layout that is in the Recipe worksheet except I want to start pasting
these values from Meals worksheet whever the pasting stops in the code
below. The ending row obviously changes depending on the data in
Recipes. -
I'm lost as to where I should place this code within the code below

Thanks



Sub Master_Cost_Post()
Dim i As Long, j As Long, k As Long, l As Long
Dim rng As Range, cell As Range
With Worksheets("Recipes")
Set rng = Union(.Range("A9"), .Range("B9"), .Range("J28"), .Range("K28"),
.Range("L28"), .Range("N28"), .Range("O28"))

i = 0
j = 0
l = 0
For Each cell In rng
j = cell.Row
k = 1
l = l + 1
Do While Not IsEmpty(.Cells(j, cell.Column))
.Cells(j, cell.Column).Copy
Worksheets("Master Costs") _
.Cells(k, l).PasteSpecial xlValues
k = k + 1
j = j + 22
Loop
Next
End With

Pasting Q
 

Thanks Bob, with no reference to the Meals worksheet within the code, how
does it extract data from that worksheet?


"Bob Phillips" wrote in message
...
John,

Not tested, but I think this will work

Sub Master_Cost_Post()
Dim i As Long, j As Long, k As Long, l As Long
Dim rng As Range, cell As Range
With Worksheets("Recipes")
Set rng = Union(.Range("A9"), .Range("B9"), .Range("J28"), .Range("K28"),
.Range("L28"), .Range("N28"), .Range("O28"))

i = 0
j = 0
l = 0
For Each cell In rng
j = cell.Row
l = l + 1
k = Worksheets("Master Costs").cellS(Rows.Count, l).End(xlUp).Row + 1
Do While Not IsEmpty(.cellS(j, cell.Column))
.cellS(j, cell.Column).Copy
Worksheets("Master Costs") _
.cellS(k, l).PasteSpecial xlValues
k = k + 1
j = j + 22
Loop
Next
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"John" wrote in message
...
I received the following code from Tom Olgivy which extracts values in
worksheet Recipes A9;B9;J28;K28;L28;N28 and O28 and pastes these values
in
worksheet Master Costs starting at A1:G1 and works down depending on the
last value in Recipes A:

I want now to continue 'pasting' in Master Costs, but this time the
'source
data' comes from a worksheet called 'Meals'. My 'Meals' worksheet has
the
same layout that is in the Recipe worksheet except I want to start
pasting
these values from Meals worksheet whever the pasting stops in the code
below. The ending row obviously changes depending on the data in
Recipes. -
I'm lost as to where I should place this code within the code below

Thanks



Sub Master_Cost_Post()
Dim i As Long, j As Long, k As Long, l As Long
Dim rng As Range, cell As Range
With Worksheets("Recipes")
Set rng = Union(.Range("A9"), .Range("B9"), .Range("J28"),
..Range("K28"),
.Range("L28"), .Range("N28"), .Range("O28"))

i = 0
j = 0
l = 0
For Each cell In rng
j = cell.Row
k = 1
l = l + 1
Do While Not IsEmpty(.Cells(j, cell.Column))
.Cells(j, cell.Column).Copy
Worksheets("Master Costs") _
.Cells(k, l).PasteSpecial xlValues
k = k + 1
j = j + 22
Loop
Next
End With

Pasting Q
 

You need to repeat the code replacing Recipes with Meals. Probably best to
put into a separate function and call for each worksheet

Master_Cost_Post Worksheets("Recipes")
Master_Cost_Post Worksheets("Meals")

Sub Master_Cost_Post(sh As Worksheet)
Dim i As Long, j As Long, k As Long, l As Long
Dim rng As Range, cell As Range
With Worksheets("Recipes")
Set rng = Union(.Range("A9"), .Range("B9"), .Range("J28"),
..Range("K28"), .Range("L28"), .Range("N28"), .Range("O28"))

i = 0 : j = 0 : l = 0
For Each cell In rng
j = cell.Row
l = l + 1
k = Worksheets("Master Costs").cellS(Rows.Count,
l).End(xlUp).Row + 1
Do While Not IsEmpty(.cellS(j, cell.Column))
.cellS(j, cell.Column).Copy
Worksheets("Master Costs") _
.cellS(k, l).PasteSpecial xlValues
k = k + 1
j = j + 22
Loop
Next
End With
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"John" wrote in message
...
Thanks Bob, with no reference to the Meals worksheet within the code, how
does it extract data from that worksheet?


"Bob Phillips" wrote in message
...
John,

Not tested, but I think this will work

Sub Master_Cost_Post()
Dim i As Long, j As Long, k As Long, l As Long
Dim rng As Range, cell As Range
With Worksheets("Recipes")
Set rng = Union(.Range("A9"), .Range("B9"), .Range("J28"),
..Range("K28"),
.Range("L28"), .Range("N28"), .Range("O28"))

i = 0
j = 0
l = 0
For Each cell In rng
j = cell.Row
l = l + 1
k = Worksheets("Master Costs").cellS(Rows.Count, l).End(xlUp).Row + 1
Do While Not IsEmpty(.cellS(j, cell.Column))
.cellS(j, cell.Column).Copy
Worksheets("Master Costs") _
.cellS(k, l).PasteSpecial xlValues
k = k + 1
j = j + 22
Loop
Next
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"John" wrote in message
...
I received the following code from Tom Olgivy which extracts values in
worksheet Recipes A9;B9;J28;K28;L28;N28 and O28 and pastes these
values
in
worksheet Master Costs starting at A1:G1 and works down depending on
the
last value in Recipes A:

I want now to continue 'pasting' in Master Costs, but this time the
'source
data' comes from a worksheet called 'Meals'. My 'Meals' worksheet has
the
same layout that is in the Recipe worksheet except I want to start
pasting
these values from Meals worksheet whever the pasting stops in the code
below. The ending row obviously changes depending on the data in
Recipes. -
I'm lost as to where I should place this code within the code below

Thanks



Sub Master_Cost_Post()
Dim i As Long, j As Long, k As Long, l As Long
Dim rng As Range, cell As Range
With Worksheets("Recipes")
Set rng = Union(.Range("A9"), .Range("B9"), .Range("J28"),
.Range("K28"),
.Range("L28"), .Range("N28"), .Range("O28"))

i = 0
j = 0
l = 0
For Each cell In rng
j = cell.Row
k = 1
l = l + 1
Do While Not IsEmpty(.Cells(j, cell.Column))
.Cells(j, cell.Column).Copy
Worksheets("Master Costs") _
.Cells(k, l).PasteSpecial xlValues
k = k + 1
j = j + 22
Loop
Next
End With

Pasting Q
 

Thanks Bob


"Bob Phillips" wrote in message
...
You need to repeat the code replacing Recipes with Meals. Probably best to
put into a separate function and call for each worksheet

Master_Cost_Post Worksheets("Recipes")
Master_Cost_Post Worksheets("Meals")

Sub Master_Cost_Post(sh As Worksheet)
Dim i As Long, j As Long, k As Long, l As Long
Dim rng As Range, cell As Range
With Worksheets("Recipes")
Set rng = Union(.Range("A9"), .Range("B9"), .Range("J28"),
.Range("K28"), .Range("L28"), .Range("N28"), .Range("O28"))

i = 0 : j = 0 : l = 0
For Each cell In rng
j = cell.Row
l = l + 1
k = Worksheets("Master Costs").cellS(Rows.Count,
l).End(xlUp).Row + 1
Do While Not IsEmpty(.cellS(j, cell.Column))
.cellS(j, cell.Column).Copy
Worksheets("Master Costs") _
.cellS(k, l).PasteSpecial xlValues
k = k + 1
j = j + 22
Loop
Next
End With
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"John" wrote in message
...
Thanks Bob, with no reference to the Meals worksheet within the code,
how
does it extract data from that worksheet?


"Bob Phillips" wrote in message
...
John,

Not tested, but I think this will work

Sub Master_Cost_Post()
Dim i As Long, j As Long, k As Long, l As Long
Dim rng As Range, cell As Range
With Worksheets("Recipes")
Set rng = Union(.Range("A9"), .Range("B9"), .Range("J28"),
.Range("K28"),
.Range("L28"), .Range("N28"), .Range("O28"))

i = 0
j = 0
l = 0
For Each cell In rng
j = cell.Row
l = l + 1
k = Worksheets("Master Costs").cellS(Rows.Count, l).End(xlUp).Row +
1
Do While Not IsEmpty(.cellS(j, cell.Column))
.cellS(j, cell.Column).Copy
Worksheets("Master Costs") _
.cellS(k, l).PasteSpecial xlValues
k = k + 1
j = j + 22
Loop
Next
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"John" wrote in message
...
I received the following code from Tom Olgivy which extracts values
in
worksheet Recipes A9;B9;J28;K28;L28;N28 and O28 and pastes these
values
in
worksheet Master Costs starting at A1:G1 and works down depending on
the
last value in Recipes A:

I want now to continue 'pasting' in Master Costs, but this time the
'source
data' comes from a worksheet called 'Meals'. My 'Meals' worksheet
has
the
same layout that is in the Recipe worksheet except I want to start
pasting
these values from Meals worksheet whever the pasting stops in the
code
below. The ending row obviously changes depending on the data in
Recipes. -
I'm lost as to where I should place this code within the code below

Thanks



Sub Master_Cost_Post()
Dim i As Long, j As Long, k As Long, l As Long
Dim rng As Range, cell As Range
With Worksheets("Recipes")
Set rng = Union(.Range("A9"), .Range("B9"), .Range("J28"),
.Range("K28"),
.Range("L28"), .Range("N28"), .Range("O28"))

i = 0
j = 0
l = 0
For Each cell In rng
j = cell.Row
k = 1
l = l + 1
Do While Not IsEmpty(.Cells(j, cell.Column))
.Cells(j, cell.Column).Copy
Worksheets("Master Costs") _
.Cells(k, l).PasteSpecial xlValues
k = k + 1
j = j + 22
Loop
Next
End With