Put data from various cells to one cell
 

Hi all, I am looking for macro which can put data from various cells
to one cell. I have data in my sheet like this (see below)

A B C
…….columns
B1 XX1 REALLOC WB ST …data in 3
cells
C20 XX12 TRANSFERRED DD
UK5 XX123 REALLOC WB DE
K56 XX UPDATED XX36
ZA8 XX12 FIGUERS NY

And I want macro to put data in sheet like this (see below)

A …….column
B1 XX1 REALLOC WB ST ….data in one
cell
C20 XX12 TRANSFERRED DD
UK5 XX123 REALLOC WB DE
K56 XX UPDATED XX36
ZA8 XX12 FIGUERS NY

It would be very nice that when I click button then macro should
prompt Inputbox saying select the range of data cells and when I press
"OK" it should promt another Inputbox saying select destination cell
for data and then by clicking on "OK" it should put data in that
cell. I have tried doing it by " A1&" "&B1&" "&Cl " but i
dont get exact picture what it appears on sheet and i endup putting
spaces manully between the data to make them look in cell exactly the
way it look when it is in various cells. macro should atumatically
put spaces between data and make them exactly accurate horizontically
and vertically the way it look in variuos cells. I hope i was able to
explain my question. Please can any friend help me on this

Put data from various cells to one cell
 

Public Sub ProcessData()
Dim i As Long, j As Long
Dim LastCol As Long
Dim rngTarget As Range
Dim rngSource As Range

With ActiveSheet

On Error Resume Next
Set rngSource = Application.InputBox("Select source range", Type:=8)
On Error GoTo 0
If rngSource Is Nothing Then Exit Sub

On Error Resume Next
Set rngTarget = Application.InputBox("Select target cell", Type:=8)
On Error GoTo 0
If rngTarget Is Nothing Then Exit Sub

rngSource.Copy rngTarget
With rngSource

For i = 1 To .Rows.Count - 1

LastCol = .Cells(i, .Columns.Count).End(xlUp).Column -
..Cells(1, 1).Column + 1
For j = LastCol - 1 To 3 Step -1

rngTarget.Cells(i, j).Value = rngTarget.Cells(i,
j).Value & " " & _
rngTarget.Cells(i, j +
1).Value
rngTarget.Cells(i, j + 1).Value = ""
Next j
Next i
End With
End With

End Sub



--
__________________________________
HTH

Bob

"K" wrote in message
...
Hi all, I am looking for macro which can put data from various cells
to one cell. I have data in my sheet like this (see below)

A B C
…….columns
B1 XX1 REALLOC WB ST …data in 3
cells
C20 XX12 TRANSFERRED DD
UK5 XX123 REALLOC WB DE
K56 XX UPDATED XX36
ZA8 XX12 FIGUERS NY

And I want macro to put data in sheet like this (see below)

A …….column
B1 XX1 REALLOC WB ST ….data in one
cell
C20 XX12 TRANSFERRED DD
UK5 XX123 REALLOC WB DE
K56 XX UPDATED XX36
ZA8 XX12 FIGUERS NY

It would be very nice that when I click button then macro should
prompt Inputbox saying select the range of data cells and when I press
"OK" it should promt another Inputbox saying select destination cell
for data and then by clicking on "OK" it should put data in that
cell. I have tried doing it by " A1&" "&B1&" "&Cl " but i
dont get exact picture what it appears on sheet and i endup putting
spaces manully between the data to make them look in cell exactly the
way it look when it is in various cells. macro should atumatically
put spaces between data and make them exactly accurate horizontically
and vertically the way it look in variuos cells. I hope i was able to
explain my question. Please can any friend help me on this

Put data from various cells to one cell
 

On Sep 11, 12:27*pm, "Bob Phillips" wrote:
Public Sub ProcessData()
Dim i As Long, j As Long
Dim LastCol As Long
Dim rngTarget As Range
Dim rngSource As Range

* * With ActiveSheet

* * * * On Error Resume Next
* * * * Set rngSource = Application.InputBox("Select source range", Type:=8)
* * * * On Error GoTo 0
* * * * If rngSource Is Nothing Then Exit Sub

* * * * On Error Resume Next
* * * * Set rngTarget = Application.InputBox("Select target cell", Type:=8)
* * * * On Error GoTo 0
* * * * If rngTarget Is Nothing Then Exit Sub

* * * * rngSource.Copy rngTarget
* * * * With rngSource

* * * * * * For i = 1 To .Rows.Count - 1

* * * * * * * * LastCol = .Cells(i, .Columns.Count).End(xlUp).Column -
.Cells(1, 1).Column + 1
* * * * * * * * For j = LastCol - 1 To 3 Step -1

* * * * * * * * * * rngTarget.Cells(i, j).Value = rngTarget.Cells(i,
j).Value & " " & _
* * * * * * * * * * * * * * * * * * * * * * * *rngTarget.Cells(i, j +
1).Value
* * * * * * * * * * rngTarget.Cells(i, j + 1).Value = ""
* * * * * * * * Next j
* * * * * * Next i
* * * * End With
* * End With

End Sub

--
__________________________________
HTH

Bob

"K" wrote in message

...
Hi all, I am looking for macro which can put data from various cells
to one cell. *I have data in my sheet like this (see below)

* * * *A * * * * * * * *B * * * * * * * * * * * *C
…….columns
* * *B1 * * * * * * XX1 * * * * * REALLOC WB ST *…data in 3
cells
* * *C20 * * * * * XX12 * * * * TRANSFERRED DD
* * *UK5 * * * * * XX123 * * * REALLOC WB DE
* * *K56 * * * * * XX * * * * * * UPDATED XX36
* * *ZA8 * * * * * XX12 * * * * FIGUERS NY

And I want macro to put data in sheet like this (see below)

* * * *A * …….column
* * *B1 * * * * * * XX1 * * * * * REALLOC WB ST ….data in one
cell
* * *C20 * * * * * XX12 * * * * TRANSFERRED DD
* * *UK5 * * * * * XX123 * * * REALLOC WB DE
* * *K56 * * * * * XX * * * * * * UPDATED XX36
* * *ZA8 * * * * * XX12 * * * * FIGUERS NY

It would be very nice that when I click button then macro should
prompt Inputbox saying select the range of data cells and when I press
"OK" it should promt another Inputbox saying select destination cell
for data and then by clicking on "OK" it should put data in that
cell. *I have tried *doing it by " *A1&" * *"&B1&" * * "&Cl *" but i
dont get exact picture what it appears on sheet and i endup putting
spaces manully between the data to make them look in cell exactly the
way it look when it is in various cells. * macro should atumatically
put spaces between data and make them exactly accurate horizontically
and vertically the way it look in variuos cells. *I hope i was able to
explain my question. *Please can any friend *help me on this

Thanks for reply Bob. i am getting error on line LastCol
= .Cells(i, .Columns.Count).End(xlUp).Column -
..Cells(1, 1).Column + 1
when i paste your code into module. Any idea what i am doing wrong?

Put data from various cells to one cell
 

On Sep 11, 12:27*pm, "Bob Phillips" wrote:
Public Sub ProcessData()
Dim i As Long, j As Long
Dim LastCol As Long
Dim rngTarget As Range
Dim rngSource As Range

* * With ActiveSheet

* * * * On Error Resume Next
* * * * Set rngSource = Application.InputBox("Select source range", Type:=8)
* * * * On Error GoTo 0
* * * * If rngSource Is Nothing Then Exit Sub

* * * * On Error Resume Next
* * * * Set rngTarget = Application.InputBox("Select target cell", Type:=8)
* * * * On Error GoTo 0
* * * * If rngTarget Is Nothing Then Exit Sub

* * * * rngSource.Copy rngTarget
* * * * With rngSource

* * * * * * For i = 1 To .Rows.Count - 1

* * * * * * * * LastCol = .Cells(i, .Columns.Count).End(xlUp).Column -
.Cells(1, 1).Column + 1
* * * * * * * * For j = LastCol - 1 To 3 Step -1

* * * * * * * * * * rngTarget.Cells(i, j).Value = rngTarget.Cells(i,
j).Value & " " & _
* * * * * * * * * * * * * * * * * * * * * * * *rngTarget.Cells(i, j +
1).Value
* * * * * * * * * * rngTarget.Cells(i, j + 1).Value = ""
* * * * * * * * Next j
* * * * * * Next i
* * * * End With
* * End With

End Sub

--
__________________________________
HTH

Bob

"K" wrote in message

...
Hi all, I am looking for macro which can put data from various cells
to one cell. *I have data in my sheet like this (see below)

* * * *A * * * * * * * *B * * * * * * * * * * * *C
…….columns
* * *B1 * * * * * * XX1 * * * * * REALLOC WB ST *…data in 3
cells
* * *C20 * * * * * XX12 * * * * TRANSFERRED DD
* * *UK5 * * * * * XX123 * * * REALLOC WB DE
* * *K56 * * * * * XX * * * * * * UPDATED XX36
* * *ZA8 * * * * * XX12 * * * * FIGUERS NY

And I want macro to put data in sheet like this (see below)

* * * *A * …….column
* * *B1 * * * * * * XX1 * * * * * REALLOC WB ST ….data in one
cell
* * *C20 * * * * * XX12 * * * * TRANSFERRED DD
* * *UK5 * * * * * XX123 * * * REALLOC WB DE
* * *K56 * * * * * XX * * * * * * UPDATED XX36
* * *ZA8 * * * * * XX12 * * * * FIGUERS NY

It would be very nice that when I click button then macro should
prompt Inputbox saying select the range of data cells and when I press
"OK" it should promt another Inputbox saying select destination cell
for data and then by clicking on "OK" it should put data in that
cell. *I have tried *doing it by " *A1&" * *"&B1&" * * "&Cl *" but i
dont get exact picture what it appears on sheet and i endup putting
spaces manully between the data to make them look in cell exactly the
way it look when it is in various cells. * macro should atumatically
put spaces between data and make them exactly accurate horizontically
and vertically the way it look in variuos cells. *I hope i was able to
explain my question. *Please can any friend *help me on this

Hi Bob, its ok now i fixed that thing . i did try your code but its
not putting various cells data into one cell. what it does is take
data from source range and then put it in same way in destination
range but not in one cell. any idea how this can be resolved

Put data from various cells to one cell
 

It does in my tests. First it copies the data across wholesale, then it
merges columns 3 on.

--
__________________________________
HTH

Bob

"K" wrote in message
...
On Sep 11, 12:27 pm, "Bob Phillips" wrote:
Public Sub ProcessData()
Dim i As Long, j As Long
Dim LastCol As Long
Dim rngTarget As Range
Dim rngSource As Range

With ActiveSheet

On Error Resume Next
Set rngSource = Application.InputBox("Select source range", Type:=8)
On Error GoTo 0
If rngSource Is Nothing Then Exit Sub

On Error Resume Next
Set rngTarget = Application.InputBox("Select target cell", Type:=8)
On Error GoTo 0
If rngTarget Is Nothing Then Exit Sub

rngSource.Copy rngTarget
With rngSource

For i = 1 To .Rows.Count - 1

LastCol = .Cells(i, .Columns.Count).End(xlUp).Column -
.Cells(1, 1).Column + 1
For j = LastCol - 1 To 3 Step -1

rngTarget.Cells(i, j).Value = rngTarget.Cells(i,
j).Value & " " & _
rngTarget.Cells(i, j +
1).Value
rngTarget.Cells(i, j + 1).Value = ""
Next j
Next i
End With
End With

End Sub

--
__________________________________
HTH

Bob

"K" wrote in message

...
Hi all, I am looking for macro which can put data from various cells
to one cell. I have data in my sheet like this (see below)

A B C
…….columns
B1 XX1 REALLOC WB ST …data in 3
cells
C20 XX12 TRANSFERRED DD
UK5 XX123 REALLOC WB DE
K56 XX UPDATED XX36
ZA8 XX12 FIGUERS NY

And I want macro to put data in sheet like this (see below)

A …….column
B1 XX1 REALLOC WB ST ….data in one
cell
C20 XX12 TRANSFERRED DD
UK5 XX123 REALLOC WB DE
K56 XX UPDATED XX36
ZA8 XX12 FIGUERS NY

It would be very nice that when I click button then macro should
prompt Inputbox saying select the range of data cells and when I press
"OK" it should promt another Inputbox saying select destination cell
for data and then by clicking on "OK" it should put data in that
cell. I have tried doing it by " A1&" "&B1&" "&Cl " but i
dont get exact picture what it appears on sheet and i endup putting
spaces manully between the data to make them look in cell exactly the
way it look when it is in various cells. macro should atumatically
put spaces between data and make them exactly accurate horizontically
and vertically the way it look in variuos cells. I hope i was able to
explain my question. Please can any friend help me on this

Hi Bob, its ok now i fixed that thing . i did try your code but its
not putting various cells data into one cell. what it does is take
data from source range and then put it in same way in destination
range but not in one cell. any idea how this can be resolved

Put data from various cells to one cell
 

On Sep 11, 1:06*pm, "Bob Phillips" wrote:
It does in my tests. First it copies the data across wholesale, then it
merges columns 3 on.

--
__________________________________
HTH

Bob

"K" wrote in message

...
On Sep 11, 12:27 pm, "Bob Phillips" wrote:





Public Sub ProcessData()
Dim i As Long, j As Long
Dim LastCol As Long
Dim rngTarget As Range
Dim rngSource As Range

With ActiveSheet

On Error Resume Next
Set rngSource = Application.InputBox("Select source range", Type:=8)
On Error GoTo 0
If rngSource Is Nothing Then Exit Sub

On Error Resume Next
Set rngTarget = Application.InputBox("Select target cell", Type:=8)
On Error GoTo 0
If rngTarget Is Nothing Then Exit Sub

rngSource.Copy rngTarget
With rngSource

For i = 1 To .Rows.Count - 1

LastCol = .Cells(i, .Columns.Count).End(xlUp).Column -
.Cells(1, 1).Column + 1
For j = LastCol - 1 To 3 Step -1

rngTarget.Cells(i, j).Value = rngTarget.Cells(i,
j).Value & " " & _
rngTarget.Cells(i, j +
1).Value
rngTarget.Cells(i, j + 1).Value = ""
Next j
Next i
End With
End With

End Sub

--
__________________________________
HTH

Bob

"K" wrote in message

...
Hi all, I am looking for macro which can put data from various cells
to one cell. I have data in my sheet like this (see below)

A B C
…….columns
B1 XX1 REALLOC WB ST …data in 3
cells
C20 XX12 TRANSFERRED DD
UK5 XX123 REALLOC WB DE
K56 XX UPDATED XX36
ZA8 XX12 FIGUERS NY

And I want macro to put data in sheet like this (see below)

A …….column
B1 XX1 REALLOC WB ST ….data in one
cell
C20 XX12 TRANSFERRED DD
UK5 XX123 REALLOC WB DE
K56 XX UPDATED XX36
ZA8 XX12 FIGUERS NY

It would be very nice that when I click button then macro should
prompt Inputbox saying select the range of data cells and when I press
"OK" it should promt another Inputbox saying select destination cell
for data and then by clicking on "OK" it should put data in that
cell. I have tried doing it by " A1&" "&B1&" "&Cl " but i
dont get exact picture what it appears on sheet and i endup putting
spaces manully between the data to make them look in cell exactly the
way it look when it is in various cells. macro should atumatically
put spaces between data and make them exactly accurate horizontically
and vertically the way it look in variuos cells. I hope i was able to
explain my question. Please can any friend help me on this

Hi Bob, its ok now i fixed that thing . *i did try your code but its
not putting various cells data into one cell. *what it does is take
data from source range and then put it in same way in destination
range but not in one cell. any idea how this can be resolved- Hide quoted text -

- Show quoted text -

I think you misunderstood my question. I don't want data to be merged
from column 3 on. I want data of 3 columns cells to appear in 1
column cell. Its ok I might come again with more clear explaination I
think its my faul that I wasn't able to explain what i want. Thanks
any way Bob for you time.

Put data from various cells to one cell
 

That is what merge columns 3 on means.

--
__________________________________
HTH

Bob

"K" wrote in message
...
On Sep 11, 1:06 pm, "Bob Phillips" wrote:
It does in my tests. First it copies the data across wholesale, then it
merges columns 3 on.

--
__________________________________
HTH

Bob

"K" wrote in message

...
On Sep 11, 12:27 pm, "Bob Phillips" wrote:





Public Sub ProcessData()
Dim i As Long, j As Long
Dim LastCol As Long
Dim rngTarget As Range
Dim rngSource As Range

With ActiveSheet

On Error Resume Next
Set rngSource = Application.InputBox("Select source range", Type:=8)
On Error GoTo 0
If rngSource Is Nothing Then Exit Sub

On Error Resume Next
Set rngTarget = Application.InputBox("Select target cell", Type:=8)
On Error GoTo 0
If rngTarget Is Nothing Then Exit Sub

rngSource.Copy rngTarget
With rngSource

For i = 1 To .Rows.Count - 1

LastCol = .Cells(i, .Columns.Count).End(xlUp).Column -
.Cells(1, 1).Column + 1
For j = LastCol - 1 To 3 Step -1

rngTarget.Cells(i, j).Value = rngTarget.Cells(i,
j).Value & " " & _
rngTarget.Cells(i, j +
1).Value
rngTarget.Cells(i, j + 1).Value = ""
Next j
Next i
End With
End With

End Sub

--
__________________________________
HTH

Bob

"K" wrote in message

...
Hi all, I am looking for macro which can put data from various cells
to one cell. I have data in my sheet like this (see below)

A B C
…….columns
B1 XX1 REALLOC WB ST …data in 3
cells
C20 XX12 TRANSFERRED DD
UK5 XX123 REALLOC WB DE
K56 XX UPDATED XX36
ZA8 XX12 FIGUERS NY

And I want macro to put data in sheet like this (see below)

A …….column
B1 XX1 REALLOC WB ST ….data in one
cell
C20 XX12 TRANSFERRED DD
UK5 XX123 REALLOC WB DE
K56 XX UPDATED XX36
ZA8 XX12 FIGUERS NY

It would be very nice that when I click button then macro should
prompt Inputbox saying select the range of data cells and when I press
"OK" it should promt another Inputbox saying select destination cell
for data and then by clicking on "OK" it should put data in that
cell. I have tried doing it by " A1&" "&B1&" "&Cl " but i
dont get exact picture what it appears on sheet and i endup putting
spaces manully between the data to make them look in cell exactly the
way it look when it is in various cells. macro should atumatically
put spaces between data and make them exactly accurate horizontically
and vertically the way it look in variuos cells. I hope i was able to
explain my question. Please can any friend help me on this

Hi Bob, its ok now i fixed that thing . i did try your code but its
not putting various cells data into one cell. what it does is take
data from source range and then put it in same way in destination
range but not in one cell. any idea how this can be resolved- Hide quoted
text -

- Show quoted text -

I think you misunderstood my question. I don't want data to be merged
from column 3 on. I want data of 3 columns cells to appear in 1
column cell. Its ok I might come again with more clear explaination I
think its my faul that I wasn't able to explain what i want. Thanks
any way Bob for you time.