macro to copy content from cells into one cell

[BorisMKD]

Hello guys,
I have a question.
What code do I need to make the data that is in more cells to be shown as data in one cell ?
ex. A5 D5 to be shown as D2(in a new workbook)
1 1 11
And that for every row ?
Thanks, Boris

 

[Mike Blackman]

Hi Boris,

You can do this with formula;

=A5&B5&C5&D5

 

[BorisMKD]

Hello Schwarzmanne,

Thanks for the fast response.

The question is how can I implement this into VBA Code ?

Thanks again,

Boris

 

[Mike Blackman]

Hi Boris,

Something like this maybe?

Dim lRow As Long

lRow = Range("A" & Rows.count).End(xlUp).Row

For i = 2 To lRow

Cells(i, 6) = Cells(i, 1) & Cells(i, 2) & Cells(i, 3) & Cells(i, 4) & Cells(i, 5)

Next i

You will see the values of A2, B2, C2, D2 & E2 in cell F2, this will also loop thru all the active rows on the spreadsheet.

 

[BorisMKD]

Hi Mikey,

Thanks for your response.

maybe If I post the code from my macro & My workbook here, it would be clearer for you to understand me, what I really want to do..

Option Explicit
Sub Test1()
    Dim Ws1    As Worksheet
    Dim Ws2    As Worksheet
    Dim rngVisible As Range
    Dim fRng   As Range
    'AutoFiltering
    Set Ws1 = Workbooks("Standard_List1.xls").Sheets("SL1-6")
    Set Ws2 = Workbooks("Standard_List1.xls").Sheets("SCS_List")
    Set fRng = Ws1.Cells(6, 1).CurrentRegion
    fRng.AutoFilter
    fRng.AutoFilter Field:=13, Criteria1:="YES"
    On Error Resume Next
    Set rngVisible = fRng.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    'Now starting to copy the
    Sheets("SCS_List").Range("B2:J1000").ClearContents
    Sheets("Sl1-6").Select
    Range("A5:A5").Select
    Range("A5:A5", ActiveCell.End(xlDown)).Select
    Selection.Copy
    Sheets("SCS_List").Select
    Range("B2:B2").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

As you see I want to copy from one worksheet to the other. There´s some error with the code as well, I don´t know what kind of..

Could you hepl me please ?

(I need a code in this form maybe, that continues to copy from C3&D3 into D2 in the other worksheet)

Thanks a lot,

Boris

 

[Mike Blackman]

HI Boris,

Here is your code, seems to work fine, I'm not sure where the concatenation of the cells comes into it?

Sub Test1()

    Sheets("SCS_List").Range("B2:J1000").ClearContents

    With Sheets("SL1-6")
        With .UsedRange
            .AutoFilter
            .AutoFilter Field:=13, Criteria1:="YES"
        End With
            .Range(.Range("A5"), .Range("A5").End(xlDown)).SpecialCells(xlCellTypeVisible).Copy
            Sheets("SCS_List").Range("B2").PasteSpecial Paste:=xlValues
    End With

End Sub

This code Autofilters and brings thru the visible values from column A but where does the concat come into it?

 

[BorisMKD]

the code works fine. I´ve changed some things as well. This is the "final" version.

Option Explicit
Sub Test1()
    Dim Ws1    As Worksheet
    Dim Ws2    As Worksheet
    Dim rngVisible As Range
    Dim fRng   As Range
    'AutoFiltering
    Set Ws1 = Workbooks("Standard_List1.xls").Sheets("SL1-6")
    Set Ws2 = Workbooks("Standard_List1.xls").Sheets("SCS_List")
    Set fRng = Ws1.Cells(6, 1).CurrentRegion
    fRng.AutoFilter
    fRng.AutoFilter Field:=13, Criteria1:="YES"
    On Error Resume Next
    Set rngVisible = fRng.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    'Now starting to copy
    Sheets("SCS_List").Range("B2:J1000").ClearContents
    Sheets("Sl1-6").Select
    Range("A3:A3").Select
    Range("A3:A3", ActiveCell.End(xlDown)).Select
    Selection.Copy
    Sheets("SCS_List").Select
    Range("B2:B2").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

Now, I want to copy C5&D5 from the first worksheet into D2 in the other workbook. And so on,
C(x)&D(x) to be copied in the next free cell in D in the other workbook.
Like I did in my code...

Thanks, Boris

 

[Mike Blackman]

Hi Boris,

From testing my code seems considerably quicker and is less messy, but thats fine.

I am assuming that you only want to bring across the concatenated values again of visible cells. Are the items in Column A on the Data sheet Uniques?

If so I'd maybe suggest codeing some Vlookups;

cells(i,4) = Application.vlookup(......)&Application.vlookup(......)

If not try;

Sub Test1()

    Dim lRow As Long

    Sheets("SCS_List").Range("B2:J1000").ClearContents

    With Sheets("SL1-6")
        With .UsedRange
            .AutoFilter
            .AutoFilter Field:=13, Criteria1:="YES"
        End With
        .Range(.Range("A5"), .Range("D5").End(xlDown)).SpecialCells(xlCellTypeVisible).Copy
        With Sheets("SCS_List")
            .Range("B2").PasteSpecial Paste:=xlValues
            lRow = .Range("B" & Rows.count).End(xlUp).Row
            .Range("C2:C" & lRow).Clear
            
            For i = 2 To lRow
                .Cells(i, 4) = .Cells(i, 4) & .Cells(i, 5)
            Next i
            
            .Range("E2:EC" & lRow).Clear
        End With
    End With

 

[BorisMKD]

Hello Mikey,


I tried something like this but it´s not working.

    Sheets("Sl1-6").Select
    Range("C3:C3,D3:D3").Select
    Range("C3:C3,D3:D3", ActiveCell.End(xlDown)).Select
    Selection.Copy
    Sheets("SCS_List").Select
    Range("D2:D2").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Could you help me, please ?

THanks, BOris

 

[Mike Blackman]

Hi Boris,

Have you tried to run my code?