Page 1 of 2 12 LastLast
Results 1 to 10 of 13

macro to copy content from cells into one cell

This is a discussion on macro to copy content from cells into one cell within the Excel Questions forums, part of the Question Forums category; Hello guys, I have a question. What code do I need to make the data that is in more cells ...

  1. #1
    New Member
    Join Date
    Oct 2008
    Posts
    10

    Default macro to copy content from cells into one cell

    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

  2. #2
    Board Regular Mike Blackman's Avatar
    Join Date
    Jun 2007
    Location
    Basingstoke, UK
    Posts
    2,443

    Default Re: macro to copy content from cells into one cell

    Hi Boris,

    You can do this with formula;

    =A5&B5&C5&D5
    Regards

    Mikey B

    Assiduus Adduco de Silentium

  3. #3
    New Member
    Join Date
    Oct 2008
    Posts
    10

    Default Re: macro to copy content from cells into one cell

    Hello Schwarzmanne,

    Thanks for the fast response.

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

    Thanks again,

    Boris

  4. #4
    Board Regular Mike Blackman's Avatar
    Join Date
    Jun 2007
    Location
    Basingstoke, UK
    Posts
    2,443

    Default Re: macro to copy content from cells into one cell

    Hi Boris,

    Something like this maybe?

    Code:
    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.
    Regards

    Mikey B

    Assiduus Adduco de Silentium

  5. #5
    New Member
    Join Date
    Oct 2008
    Posts
    10

    Default Re: macro to copy content from cells into one cell

    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..
    Code:
    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
    Last edited by BorisMKD; Oct 20th, 2008 at 07:09 AM.

  6. #6
    Board Regular Mike Blackman's Avatar
    Join Date
    Jun 2007
    Location
    Basingstoke, UK
    Posts
    2,443

    Default Re: macro to copy content from cells into one cell

    HI Boris,

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

    Code:
    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?
    Regards

    Mikey B

    Assiduus Adduco de Silentium

  7. #7
    New Member
    Join Date
    Oct 2008
    Posts
    10

    Default Re: macro to copy content from cells into one cell

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

    Code:
    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

  8. #8
    Board Regular Mike Blackman's Avatar
    Join Date
    Jun 2007
    Location
    Basingstoke, UK
    Posts
    2,443

    Default Re: macro to copy content from cells into one cell

    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;

    Code:
    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
    Regards

    Mikey B

    Assiduus Adduco de Silentium

  9. #9
    New Member
    Join Date
    Oct 2008
    Posts
    10

    Default Re: macro to copy content from cells into one cell

    Hello Mikey,


    I tried something like this but itīs not working.
    Code:
        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

  10. #10
    Board Regular Mike Blackman's Avatar
    Join Date
    Jun 2007
    Location
    Basingstoke, UK
    Posts
    2,443

    Default Re: macro to copy content from cells into one cell

    Hi Boris,

    Have you tried to run my code?
    Regards

    Mikey B

    Assiduus Adduco de Silentium

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com