copy and paste multiple selection

Have had a look in Help but not able to find an answer to my problem, in
fact Help says that what I want is not possible so I thought I would turn to
the pro's.

I want to copy a selection of cells and paste them into a row on another
sheet.

An example would be to copy:

Sheets("Sheet1").Range("T5,C7:T9,T13,C15:T17,T19,C21:T23")

and put it into A4:F4 on Sheet2

I know I can do it one cell at a time but I was hoping that I could speed
things up and do it all in one go as the macro will be quite long.

Thanks in advance.

Gareth

Try this ( Be advised I'm not a pro):

Sub TransferData()
Dim SourceRng As Range, DestRng As Range
Dim c As Range
Dim Prompt As String, Title As String, Default As String
Dim i As Integer

Prompt = "Select source range..."
Title = "Data transfer"
Default = Selection.Address
On Error Resume Next
Set SourceRng = Application.InputBox(Prompt, Title, Default, Type:=8)
If Err.Number > 0 Then Exit Sub
Prompt = "Select destination cell..."
Set DestRng = Application.InputBox(Prompt, Title, Type:=8)
If Err.Number > 0 Then Exit Sub
On Error GoTo 0
i = 0
Application.ScreenUpdating = False
For Each c In SourceRng
i = i + 1
DestRng(1, i) = c.Value
Next
'DestRng.Parent.Activate
Application.ScreenUpdating = True
End Sub

Regards,
Greg


"Gareth" wrote:

> Have had a look in Help but not able to find an answer to my problem, in
> fact Help says that what I want is not possible so I thought I would turn to
> the pro's.
>
> I want to copy a selection of cells and paste them into a row on another
> sheet.
>
> An example would be to copy:
>
> Sheets("Sheet1").Range("T5,C7:T9,T13,C15:T17,T19,C21:T23")
>
> and put it into A4:F4 on Sheet2
>
> I know I can do it one cell at a time but I was hoping that I could speed
> things up and do it all in one go as the macro will be quite long.
>
> Thanks in advance.
>
> Gareth
>
>
>

Gareth,

You can't put 10 lbs in a 5 lb bag.

There are 165 cells in...
Range("T5,C7:T9,T13,C15:T17,T19,C21:T23")
and
6 cells in...
Range("A4:F4")

Your can do this...
Sheets("Sheet2").Range("A4:F4").Value = Sheets("Sheet1").Range("G4:L4").Value

Regards,
Jim Cone
San Francisco, USA


"Gareth" <[email protected]> wrote in message
news:[email protected]...
> Have had a look in Help but not able to find an answer to my problem, in
> fact Help says that what I want is not possible so I thought I would turn to
> the pro's.
> I want to copy a selection of cells and paste them into a row on another
> sheet.
> An example would be to copy:
>
> Sheets("Sheet1").Range("T5,C7:T9,T13,C15:T17,T19,C21:T23")
>
> and put it into A4:F4 on Sheet2
>
> I know I can do it one cell at a time but I was hoping that I could speed
> things up and do it all in one go as the macro will be quite long.
> Thanks in advance.
> Gareth

There are 165 cells in:
Sheets("Sheet1").Range("T5,C7:T9,T13,C15:T17,T19,C21:T23")

And 6 cells in A4:F4.

Isn't that a problem?



Gareth wrote:
>
> Have had a look in Help but not able to find an answer to my problem, in
> fact Help says that what I want is not possible so I thought I would turn to
> the pro's.
>
> I want to copy a selection of cells and paste them into a row on another
> sheet.
>
> An example would be to copy:
>
> Sheets("Sheet1").Range("T5,C7:T9,T13,C15:T17,T19,C21:T23")
>
> and put it into A4:F4 on Sheet2
>
> I know I can do it one cell at a time but I was hoping that I could speed
> things up and do it all in one go as the macro will be quite long.
>
> Thanks in advance.
>
> Gareth

--

Dave Peterson

c7:c9, etc, are merged cells if that makes ant difference........

"Jim Cone" <[email protected]> wrote in message
news:[email protected]...
> Gareth,
>
> You can't put 10 lbs in a 5 lb bag.
>
> There are 165 cells in...
> Range("T5,C7:T9,T13,C15:T17,T19,C21:T23")
> and
> 6 cells in...
> Range("A4:F4")
>
> Your can do this...
> Sheets("Sheet2").Range("A4:F4").Value =
Sheets("Sheet1").Range("G4:L4").Value
>
> Regards,
> Jim Cone
> San Francisco, USA
>
>
> "Gareth" <[email protected]> wrote in message
> news:[email protected]...
> > Have had a look in Help but not able to find an answer to my problem, in
> > fact Help says that what I want is not possible so I thought I would
turn to
> > the pro's.
> > I want to copy a selection of cells and paste them into a row on another
> > sheet.
> > An example would be to copy:
> >
> > Sheets("Sheet1").Range("T5,C7:T9,T13,C15:T17,T19,C21:T23")
> >
> > and put it into A4:F4 on Sheet2
> >
> > I know I can do it one cell at a time but I was hoping that I could
speed
> > things up and do it all in one go as the macro will be quite long.
> > Thanks in advance.
> > Gareth
>

Gareth,
If you have merged cells then you need to replace my macro with this. Please
advise if it works for you or not.

Sub TransferData()
Dim SourceRng As Range, DestRng As Range
Dim c As Range
Dim Prompt As String, Title As String, Default As String
Dim i As Integer

Prompt = "Select source range..."
Title = "Data transfer"
Default = Selection.Address
On Error Resume Next
Set SourceRng = Application.InputBox(Prompt, Title, Default, Type:=8)
If Err.Number > 0 Then Exit Sub
Prompt = "Select destination cell..."
Set DestRng = Application.InputBox(Prompt, Title, Type:=8)
If Err.Number > 0 Then Exit Sub
On Error GoTo 0
i = 0
Application.ScreenUpdating = False
For Each c In SourceRng.Cells
If Len(c) > 0 Then
i = i + 1
DestRng(1, i) = c
End If
Next
Application.ScreenUpdating = True
End Sub

Regards,
Greg

"Gareth" wrote:

> c7:c9, etc, are merged cells if that makes ant difference........
>
> "Jim Cone" <[email protected]> wrote in message
> news:[email protected]...
> > Gareth,
> >
> > You can't put 10 lbs in a 5 lb bag.
> >
> > There are 165 cells in...
> > Range("T5,C7:T9,T13,C15:T17,T19,C21:T23")
> > and
> > 6 cells in...
> > Range("A4:F4")
> >
> > Your can do this...
> > Sheets("Sheet2").Range("A4:F4").Value =
> Sheets("Sheet1").Range("G4:L4").Value
> >
> > Regards,
> > Jim Cone
> > San Francisco, USA
> >
> >
> > "Gareth" <[email protected]> wrote in message
> > news:[email protected]...
> > > Have had a look in Help but not able to find an answer to my problem, in
> > > fact Help says that what I want is not possible so I thought I would
> turn to
> > > the pro's.
> > > I want to copy a selection of cells and paste them into a row on another
> > > sheet.
> > > An example would be to copy:
> > >
> > > Sheets("Sheet1").Range("T5,C7:T9,T13,C15:T17,T19,C21:T23")
> > >
> > > and put it into A4:F4 on Sheet2
> > >
> > > I know I can do it one cell at a time but I was hoping that I could
> speed
> > > things up and do it all in one go as the macro will be quite long.
> > > Thanks in advance.
> > > Gareth
> >
>
>
>

Gareth,

Yes it does. The following works...
'------------------
Sub Test()
Dim rngA As Excel.Range
Dim lngN As Long

Set rngA = Range("T5,C7:T9,T13,C15:T17,T19,C21:T23")

For lngN = 1 To rngA.Areas.Count
Range("A4")(1, lngN).Value = rngA.Areas(lngN)(1).Value
Next
Set rngA = Nothing
End Sub
'-----------------------

Jim Cone
San Francisco, USA



"Gareth" <[email protected]> wrote in message
news:[email protected]...
> c7:c9, etc, are merged cells if that makes ant difference........



> "Jim Cone" <[email protected]> wrote in message
> news:[email protected]...
> > Gareth,
> > You can't put 10 lbs in a 5 lb bag.
> > There are 165 cells in...
> > Range("T5,C7:T9,T13,C15:T17,T19,C21:T23")
> > and
> > 6 cells in...
> > Range("A4:F4")
> > Your can do this...
> > Sheets("Sheet2").Range("A4:F4").Value =
> Sheets("Sheet1").Range("G4:L4").Value
> > Regards,
> > Jim Cone
> > San Francisco, USA

Jim,

My read when Gareth said C7:C9 (instead of C7:T9) are merged cells was that
the columns of cells within the contiguous ranges were merged independantly -
i.e. C7:C9 are one merged range and D7:D9 another etc. Your interpretation
makes more sence for the particular example because the number of areas
matches the cells in A4:F4. However, he did indicate that this was only an
example, leaving the possibility of there being multiple merged ranges or
just multiple cells within contiguous blocks. Therefore, use of Areas would
not work. Obviously, what is needed is more insight from Gareth. Thought I'd
point out my interpretation before moving on.

Regards,
Greg

"Jim Cone" wrote:

> Gareth,
>
> Yes it does. The following works...
> '------------------
> Sub Test()
> Dim rngA As Excel.Range
> Dim lngN As Long
>
> Set rngA = Range("T5,C7:T9,T13,C15:T17,T19,C21:T23")
>
> For lngN = 1 To rngA.Areas.Count
> Range("A4")(1, lngN).Value = rngA.Areas(lngN)(1).Value
> Next
> Set rngA = Nothing
> End Sub
> '-----------------------
>
> Jim Cone
> San Francisco, USA
>
>
>
> "Gareth" <[email protected]> wrote in message
> news:[email protected]...
> > c7:c9, etc, are merged cells if that makes ant difference........
>
>
>
> > "Jim Cone" <[email protected]> wrote in message
> > news:[email protected]...
> > > Gareth,
> > > You can't put 10 lbs in a 5 lb bag.
> > > There are 165 cells in...
> > > Range("T5,C7:T9,T13,C15:T17,T19,C21:T23")
> > > and
> > > 6 cells in...
> > > Range("A4:F4")
> > > Your can do this...
> > > Sheets("Sheet2").Range("A4:F4").Value =
> > Sheets("Sheet1").Range("G4:L4").Value
> > > Regards,
> > > Jim Cone
> > > San Francisco, USA
>
>

I'm using excel 2007.
I would like to convert multiple selections of cells (each cell has a furmula) to values.
I tried to do this by: selecting the multiple cells==> copy==>paste special==> values, but I get the error message "The command can not be used on multiple selections.".
I assume this can be done with a macro?
Thanks you, Alon.