A macro to copy and concatenate text from a sheet to another

[freds45]

Hi ! :D

I have a sheet with some data, it is sorted by rows.
What I want is to write a macro that the text of each line on the first sheet, and copies this new line to a new sheet.
I'm a total newbie in vba programming and I searched the forum, but could not find anything ...
Can you help ? :wink:


"foo" "42" would give foo042 on the other sheet for example...

 

[Joe4]

Re: A macro to copy and concatenate text from a sheet to ano

WELCOME TO THE BOARD!

Here is some code that I think will do what you want. In my example, it is copying from Sheet1 to Sheet2. If your sheets are named otherwise, you will need to change the name references. I am also looking at column A to determine how many rows this needs to be done for.

Let me know if this works for you.

Sub TextCopy()

    Application.ScreenUpdating = False
    
    Dim i, j As Integer
    Dim Mystring As String
    
    Sheets("Sheet1").Select
'   Count how many rows to do
    For i = 1 To Sheets("Sheet1").Cells(65536, 1).End(xlUp).Row
        Mystring = ""
'   Count how many columns to do in each row
        For j = 1 To Sheets("Sheet1").Cells(i, 256).End(xlToLeft).Column
'   If there is something in cell, add it to string
            If Len(Sheets("Sheet1").Cells(i, j)) > 0 Then
                Mystring = Mystring & Cells(i, j)
            End If
        Next j
        Sheets("Sheet2").Cells(i, 1) = Mystring
    Next i
    
    Application.ScreenUpdating = True
    
End Sub

 

[freds45]

Re: A macro to copy and concatenate text from a sheet to ano

WELCOME TO THE BOARD!

Here is some code that I think will do what you want. In my example, it is copying from Sheet1 to Sheet2. If your sheets are named otherwise, you will need to change the name references. I am also looking at column A to determine how many rows this needs to be done for.

Let me know if this works for you.

Sub TextCopy()

    Application.ScreenUpdating = False
    
    Dim i, j As Integer
    Dim Mystring As String
    
    Sheets("Sheet1").Select
'   Count how many rows to do
    For i = 1 To Sheets("Sheet1").Cells(65536, 1).End(xlUp).Row
        Mystring = ""
'   Count how many columns to do in each row
        For j = 1 To Sheets("Sheet1").Cells(i, 256).End(xlToLeft).Column
'   If there is something in cell, add it to string
            If Len(Sheets("Sheet1").Cells(i, j)) > 0 Then
                Mystring = Mystring & Cells(i, j)
            End If
        Next j
        Sheets("Sheet2").Cells(i, 1) = Mystring
    Next i
    
    Application.ScreenUpdating = True
    
End Sub

Thanks a lot, you're great! It works! :wink:

 

[freds45]

Re: A macro to copy and concatenate text from a sheet to ano

well, there's still a problem, I don't see where it comes from

The text I get in my destination sheet comes from the sheet where the button is, not from the specified source sheet... What's strange is that it copies only the number of lines present in the source sheet !
If I move the button to the specified source sheet, it works... but I dont't want it there.

It's Excel 2000, running on Windows 98...
Anyone has an idea ?

 

[freds45]

Re: A macro to copy and concatenate text from a sheet to ano

it's ok


What had to be specified is

If Len(Sheets("Sheet1").Cells(i, j)) > 0 Then
Mystring = Mystring & Sheets("Sheet1").Cells(i, j)
End If