Pasting Q

[John]

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

 

[Bob Phillips]

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]

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

 

[Bob Phillips]

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]

Thanks Bob

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)