Put data from various cells to one cell

[K]

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

 

[Bob Phillips]

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

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

 

[K]

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


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?

 

[K]

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


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

 

[Bob Phillips]

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

--
__________________________________
HTH

Bob

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


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

 

[K]

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

--
__________________________________
HTH

Bob

















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 quotedtext -

- 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.

 

[Bob Phillips]

That is what merge columns 3 on means.

--
__________________________________
HTH

Bob

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

--
__________________________________
HTH

Bob

















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.