Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pasting | Excel Worksheet Functions | |||
Pasting on Filtered Data Sheets without pasting onto hidden cells | Excel Discussion (Misc queries) | |||
Pasting Into new sheet | Excel Discussion (Misc queries) | |||
Pasting Links | Excel Discussion (Misc queries) | |||
Pasting numbers and formulas without pasting format. | Excel Discussion (Misc queries) |