asked on

Parse and copy data from one Excel sheet to another in the same Workbook

An Excel workbook contains Sheet1.xls, and Sheet2.xls.
On Sheet1.xls starting from A2,
Data exists all separated by commas.

For example
07/11/2019,Description,Size,11:06AM

I would like to take the data which is only in A2, in Sheet1 which is separated by commas and transpose that data into each corresponding line and column,
in Sheet2.xls

For example, in Sheet2,xls
A2 would contain data 07/11/2019
B2 would contain data Description
C2 would contain data Size
D2 would contain data 11:06AM

Is there a script which can be run so that when data appears in A2, in Sheet1.xls
it will parse the data and enter it into the corresponding line and by column in Sheet2.xls?

Keeping in mind that once data also appears in A3 in Sheet1.xls, that the data would be copied into
Sheet2.xls, however starting from A3, and then into B3, C3, D4 ..

Whenever data appears in the A column in Sheet1.xls that it will be parsed and copied into the corresponding line in Sheet2.xls

Can this be achieved with script or a macro, and be automated?

Thanks for your help.

Norie

You could try this Change event which goes in the Sheet1 module.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cl As Range
Dim arrData As Variant
Dim idxRw As Long

    If Target.Column = 1 Then
        For Each cl In Intersect(Target, Me.Columns(1)).Cells
            arrData = Split(cl.Value, ",")
            If IsArray(arrData) Then
                Sheets("Sheet2").Range(cl.Address).Resize(, UBound(arrData) + 1).Value = arrData
            End If
        Next cl
        
    End If
    
End Sub

Open in new window

E=mc2

ASKER

Thanks and what would be the best way to run this.
And perhaps refresh it to run again?

Norie

The code will be triggered whenever anything is entered in column A in Sheet1.

E=mc2

ASKER

If I wanted to change it from A2 instead of A1.. what would change?
Also, I notice that if I manually wipe out data it will not retrigger if data is back in A1..

Norie

The code will be triggered when a change is made anywhere in column A.

If you want to exclude row 1 you could use this, which will also clear the relevant cells on Sheet2 when you clear values in column A on Sheet1.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cl As Range
Dim arrData As Variant
Dim idxRw As Long

    If Target.Column = 1 And Target.Row>1Then
        For Each cl In Intersect(Target, Me.Columns(1)).Cells
            If Target.Value <>"" Then
                arrData = Split(cl.Value, ",")
                If IsArray(arrData) Then
                    Sheets("Sheet2").Range(cl.Address).Resize(, UBound(arrData) + 1).Value = arrData
                End If
            Else
                Sheets("Sheet2").Range(cl.Address).Value = ""
            End If
        Next cl
        
    End If
    
End Sub

Open in new window

E=mc2

ASKER

Thanks Norie, this almost works.
If by chance I delete the data in A2 for example then in sheet2 it only deletes the corresponding data in A2,
but the rest of the fields which were divided by a comma, still show up in B2, C2, D2, E2...etc..

Norie

Oops, forgot about the values in the other cells.:)

Try this.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cl As Range
Dim arrData As Variant
Dim idxRw As Long

    If Target.Column = 1 And Target.Row>1Then
        For Each cl In Intersect(Target, Me.Columns(1)).Cells
            If Target.Value <>"" Then
                arrData = Split(cl.Value, ",")
                If IsArray(arrData) Then
                    Sheets("Sheet2").Range(cl.Address).Resize(, UBound(arrData) + 1).Value = arrData
                End If
            Else
                Sheets("Sheet2").Range(cl.Address).Resize(,4).Value = ""
            End If
        Next cl
        
    End If
    
End Sub

Open in new window

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.