Paste to Multiple Selections

M

Mick

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
 
B

Bernie Deitrick

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
 
M

Mick Southam

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
 
B

Bernie Deitrick

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
 
M

Mick Southam

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
 
B

Bernie Deitrick

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
 
M

Mick Southam

Bernie

There are a couple of issues that I can't resolve. Perhaps I should go back
to the beginning and be more specific in what I am trying to achieve.

I have a number of sheets in the workbook and am concerned with getting data
out of the "DataSheet" into each of the month sheets. In the DataSheet I
have a number of columns which contain the data. For example G10:G59
contains names A10:A59 contains staff numbers and B10:B59 contains
callsigns. This information needs to go into column A of each month and has
to be values only as I don't want the formatting changed. My problem is
that all the cells are not adjacent to each other. For example in each
month, A4, A14, A24, A34 and so on contain the names. A5, A15, A25, A35 and
so on contain staff numbers and A13, A23, A33, A34 and so on contains the
callsign.

Whilst I can get your code to read the correct data, it insists in putting
the data into consecutive cells i.e. A4, A5, A6, A7 and so on. Also your
code changes the formatting in January but not in all the other months as I
need.

Thanks for all your help

Regards

Mick
 
Top