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,C 21: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,C 21: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" wrote in message
...
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,C 21: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

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

"Jim Cone" wrote in message
...
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" wrote in message
...
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,C 21: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" wrote in message
...
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" wrote in message
...
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,C 21: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" wrote in message
...
c7:c9, etc, are merged cells if that makes ant difference........



"Jim Cone" wrote in message
...
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" wrote in message
...
c7:c9, etc, are merged cells if that makes ant difference........



"Jim Cone" wrote in message
...
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

There are 165 cells in:
Sheets("Sheet1").Range("T5,C7:T9,T13,C15:T17,T19,C 21: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,C 21: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