| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste to Multiple Selections
Hi
I am trying to find a way to be able to paste to multiple selections in a number of grouped sheets. In the example below it only refers to 2 cell locations whereas there are actually more. Thanks in antisipations. Mick Sheets(Array ("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "S ep", "Oct", "Nov", _ "Dec")).Select Sheets("Jan").Activate Worksheets("DataSheet").Range("G10,G11").Copy ActiveSheet.Paste Destination:=Worksheets("Jan").Range ("A4:A5") 'Selection.Paste Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Paste to Multiple Selections
Mack,
You should group the sheets after the copy, and use the FillAcrossSheets method: Worksheets("DataSheet").Range("G10:G11").Copy Sheets(Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", _ "Aug", "Sep", "Oct", "Nov", "Dec")).Select Range("A4:A5").Select ActiveSheet.Paste Selection.PasteSpecial Paste:=xlValues ActiveWindow.SelectedSheets.FillAcrossSheets _ Range("A4:A5"), xlFillWithContents HTH, Bernie MS Excel MVP "Mick" wrote in message ... Hi I am trying to find a way to be able to paste to multiple selections in a number of grouped sheets. In the example below it only refers to 2 cell locations whereas there are actually more. Thanks in antisipations. Mick Sheets(Array ("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "S ep", "Oct", "Nov", _ "Dec")).Select Sheets("Jan").Activate Worksheets("DataSheet").Range("G10,G11").Copy ActiveSheet.Paste Destination:=Worksheets("Jan").Range ("A4:A5") 'Selection.Paste Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Paste to Multiple Selections
Bernie
Thanks, unfortunately the ranges used in the example were in correct, in as much as they are not adjacent i.e. G10:G11 should read G10,G14 and A4:A5 should read A4,A15. I can get the code to work if I past to adjacent cells but not non adjacent cells - Hope that make sense "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Mack, You should group the sheets after the copy, and use the FillAcrossSheets method: Worksheets("DataSheet").Range("G10:G11").Copy Sheets(Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", _ "Aug", "Sep", "Oct", "Nov", "Dec")).Select Range("A4:A5").Select ActiveSheet.Paste Selection.PasteSpecial Paste:=xlValues ActiveWindow.SelectedSheets.FillAcrossSheets _ Range("A4:A5"), xlFillWithContents HTH, Bernie MS Excel MVP "Mick" wrote in message ... Hi I am trying to find a way to be able to paste to multiple selections in a number of grouped sheets. In the example below it only refers to 2 cell locations whereas there are actually more. Thanks in antisipations. Mick Sheets(Array ("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "S ep", "Oct", "Nov", _ "Dec")).Select Sheets("Jan").Activate Worksheets("DataSheet").Range("G10,G11").Copy ActiveSheet.Paste Destination:=Worksheets("Jan").Range ("A4:A5") 'Selection.Paste Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Paste to Multiple Selections
Mick,
Just do it twice: Worksheets("DataSheet").Range("G10").Copy Sheets(Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", _ "Aug", "Sep", "Oct", "Nov", "Dec")).Select Range("A4").Select ActiveSheet.Paste Selection.PasteSpecial Paste:=xlValues ActiveWindow.SelectedSheets.FillAcrossSheets _ Range("A4"), xlFillWithContents Worksheets("DataSheet").Range("G14").Copy Sheets(Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", _ "Aug", "Sep", "Oct", "Nov", "Dec")).Select Range("A15").Select ActiveSheet.Paste Selection.PasteSpecial Paste:=xlValues ActiveWindow.SelectedSheets.FillAcrossSheets _ Range("A15"), xlFillWithContents HTH, Bernie MS Excel MVP "Mick Southam" wrote in message ... Bernie Thanks, unfortunately the ranges used in the example were in correct, in as much as they are not adjacent i.e. G10:G11 should read G10,G14 and A4:A5 should read A4,A15. I can get the code to work if I past to adjacent cells but not non adjacent cells - Hope that make sense "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Mack, You should group the sheets after the copy, and use the FillAcrossSheets method: Worksheets("DataSheet").Range("G10:G11").Copy Sheets(Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", _ "Aug", "Sep", "Oct", "Nov", "Dec")).Select Range("A4:A5").Select ActiveSheet.Paste Selection.PasteSpecial Paste:=xlValues ActiveWindow.SelectedSheets.FillAcrossSheets _ Range("A4:A5"), xlFillWithContents HTH, Bernie MS Excel MVP "Mick" wrote in message ... Hi I am trying to find a way to be able to paste to multiple selections in a number of grouped sheets. In the example below it only refers to 2 cell locations whereas there are actually more. Thanks in antisipations. Mick Sheets(Array ("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "S ep", "Oct", "Nov", _ "Dec")).Select Sheets("Jan").Activate Worksheets("DataSheet").Range("G10,G11").Copy ActiveSheet.Paste Destination:=Worksheets("Jan").Range ("A4:A5") 'Selection.Paste Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Paste to Multiple Selections
Bernie
If say, there were 20 or more non adjacent cells in the reference you were trying to paste to, how would you do it then? Thanks Mick "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Mick, Just do it twice: Worksheets("DataSheet").Range("G10").Copy Sheets(Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", _ "Aug", "Sep", "Oct", "Nov", "Dec")).Select Range("A4").Select ActiveSheet.Paste Selection.PasteSpecial Paste:=xlValues ActiveWindow.SelectedSheets.FillAcrossSheets _ Range("A4"), xlFillWithContents Worksheets("DataSheet").Range("G14").Copy Sheets(Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", _ "Aug", "Sep", "Oct", "Nov", "Dec")).Select Range("A15").Select ActiveSheet.Paste Selection.PasteSpecial Paste:=xlValues ActiveWindow.SelectedSheets.FillAcrossSheets _ Range("A15"), xlFillWithContents HTH, Bernie MS Excel MVP "Mick Southam" wrote in message ... Bernie Thanks, unfortunately the ranges used in the example were in correct, in as much as they are not adjacent i.e. G10:G11 should read G10,G14 and A4:A5 should read A4,A15. I can get the code to work if I past to adjacent cells but not non adjacent cells - Hope that make sense "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Mack, You should group the sheets after the copy, and use the FillAcrossSheets method: Worksheets("DataSheet").Range("G10:G11").Copy Sheets(Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", _ "Aug", "Sep", "Oct", "Nov", "Dec")).Select Range("A4:A5").Select ActiveSheet.Paste Selection.PasteSpecial Paste:=xlValues ActiveWindow.SelectedSheets.FillAcrossSheets _ Range("A4:A5"), xlFillWithContents HTH, Bernie MS Excel MVP "Mick" wrote in message ... Hi I am trying to find a way to be able to paste to multiple selections in a number of grouped sheets. In the example below it only refers to 2 cell locations whereas there are actually more. Thanks in antisipations. Mick Sheets(Array ("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "S ep", "Oct", "Nov", _ "Dec")).Select Sheets("Jan").Activate Worksheets("DataSheet").Range("G10,G11").Copy ActiveSheet.Paste Destination:=Worksheets("Jan").Range ("A4:A5") 'Selection.Paste Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Paste to Multiple Selections
Mick,
If there weren't a pattern to the cells, I would do something like Dim i As Integer Dim mySource As Range Dim myTarget As Range Set mySource = Worksheets("DataSheet").Range("G10,G14,G18, H5, K12") Set myTarget = Worksheets("Jan").Range("A4,A15,C12,D16,E19") For i = 1 to mySource.Cells.Count mySource(i).Copy myTarget(i) myTarget(i).Value = myTarget(i).Value Sheets(Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", _ "Aug", "Sep", "Oct", "Nov", "Dec")).Select Worksheets("Jan").Activate ActiveWindow.SelectedSheets.FillAcrossSheets _ myTarget(i), xlFillWithContents Next i If there were a pattern to the cells, then I would use two indices. HTH, Bernie MS Excel MVP "Mick Southam" wrote in message ... Bernie If say, there were 20 or more non adjacent cells in the reference you were trying to paste to, how would you do it then? Thanks Mick "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Mick, Just do it twice: Worksheets("DataSheet").Range("G10").Copy Sheets(Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", _ "Aug", "Sep", "Oct", "Nov", "Dec")).Select Range("A4").Select ActiveSheet.Paste Selection.PasteSpecial Paste:=xlValues ActiveWindow.SelectedSheets.FillAcrossSheets _ Range("A4"), xlFillWithContents Worksheets("DataSheet").Range("G14").Copy Sheets(Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", _ "Aug", "Sep", "Oct", "Nov", "Dec")).Select Range("A15").Select ActiveSheet.Paste Selection.PasteSpecial Paste:=xlValues ActiveWindow.SelectedSheets.FillAcrossSheets _ Range("A15"), xlFillWithContents HTH, Bernie MS Excel MVP "Mick Southam" wrote in message ... Bernie Thanks, unfortunately the ranges used in the example were in correct, in as much as they are not adjacent i.e. G10:G11 should read G10,G14 and A4:A5 should read A4,A15. I can get the code to work if I past to adjacent cells but not non adjacent cells - Hope that make sense "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Mack, You should group the sheets after the copy, and use the FillAcrossSheets method: Worksheets("DataSheet").Range("G10:G11").Copy Sheets(Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", _ "Aug", "Sep", "Oct", "Nov", "Dec")).Select Range("A4:A5").Select ActiveSheet.Paste Selection.PasteSpecial Paste:=xlValues ActiveWindow.SelectedSheets.FillAcrossSheets _ Range("A4:A5"), xlFillWithContents HTH, Bernie MS Excel MVP "Mick" wrote in message ... Hi I am trying to find a way to be able to paste to multiple selections in a number of grouped sheets. In the example below it only refers to 2 cell locations whereas there are actually more. Thanks in antisipations. Mick Sheets(Array ("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "S ep", "Oct", "Nov", _ "Dec")).Select Sheets("Jan").Activate Worksheets("DataSheet").Range("G10,G11").Copy ActiveSheet.Paste Destination:=Worksheets("Jan").Range ("A4:A5") 'Selection.Paste Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Multiple selections | Excel Discussion (Misc queries) | |||
| copy and paste with multiple selections in Microsoft Excel | Excel Discussion (Misc queries) | |||
| copy and paste with multiple selections in Microsoft Excel | Excel Discussion (Misc queries) | |||
| Vlookup for multiple selections | Excel Worksheet Functions | |||
| Copy and paste selections and formatting to new worksheet | Excel Programming | |||
Hybrid Mode
