Excel macro

[knockme2]

Hi Everyone,

I have an excel sheet (2010) which has raw data. I want to copy only certain bits of data into another sheet segregating the data.

I have attached the sample file

The data has to be taken from Raw data and copied in the sheet Macro-data as per the required fields until last empty cell.

Need 2 such macros for different types of data.

Raw data to be copied in the sheet Macro-data ---------> 1st Macro copy paste
Raw data1 and copied in the sheet Macro-data1 -------> 2nd Macro copy paste

Regards

Umesh

[aju.thomas]

Please find customised code for you.

Hope this will help you.

Option Explicit
Sub Rawdata()
Dim data(10) As String
Dim cnt, start, i, end_a, r_macro_data As Integer

    For cnt = 2 To ThisWorkbook.Sheets("raw data").Range("A1").End(xlDown).Row
        r_macro_data = ThisWorkbook.Sheets("Macro-data").Range("A1").End(xlDown).Row + 1
        ThisWorkbook.Sheets("Macro-data").Cells(r_macro_data, 1) = ThisWorkbook.Sheets("Raw Data").Cells(cnt, 1).Value
        start = 1
        For i = 1 To 5
            With ThisWorkbook.Sheets("Raw Data")
                start = InStr(start, .Cells(cnt, 2).Value, ":") + 2
                end_a = InStr(start, .Cells(cnt, 2).Value, Chr(10)) - 1
                If end_a = -1 Then end_a = Len(ThisWorkbook.Sheets("Raw Data").Cells(cnt, 2).Value) + 1
                If start = 2 Then start = end_a
                data(i) = Mid(.Cells(cnt, 2).Value, start, end_a - start)
            End With
            ThisWorkbook.Sheets("Macro-data").Cells(r_macro_data, 1 + i) = data(i)
        Next i
    ThisWorkbook.Sheets("Macro-data").Cells(r_macro_data, 7) = ThisWorkbook.Sheets("Raw Data").Cells(cnt, 3).Value
    Next cnt
End Sub


Sub Rawdata1()
Dim data(10) As String
Dim cnt, start, i, end_a, r_macro_data As Integer

    For cnt = 2 To ThisWorkbook.Sheets("raw data 1").Range("A1").End(xlDown).Row
        r_macro_data = ThisWorkbook.Sheets("Macro-data 1").Range("A1").End(xlDown).Row + 1
        ThisWorkbook.Sheets("Macro-data 1").Cells(r_macro_data, 1) = ThisWorkbook.Sheets("Raw Data 1").Cells(cnt, 1).Value
        start = 1
        For i = 1 To 3
            With ThisWorkbook.Sheets("Raw Data 1")
                start = InStr(start, .Cells(cnt, 2).Value, ":") + 2
                end_a = InStr(start, .Cells(cnt, 2).Value, Chr(10)) - 1
                If end_a = -1 Then end_a = Len(ThisWorkbook.Sheets("Raw Data 1").Cells(cnt, 2).Value) + 1
                If start = 2 Then start = end_a
                data(i) = Mid(.Cells(cnt, 2).Value, start, end_a - start)
            End With
            ThisWorkbook.Sheets("Macro-data 1").Cells(r_macro_data, 1 + i) = data(i)
        Next i
    Next cnt
End Sub

[knockme2]

Thank you for all your help.

It was really helpfull.

Need help with this two macros. I have created two excel file for it.

Macro file
Macro file 1

Earlier i had taken help for almost similar kind of data for macro file 1 but now this time the there is a slight difference in the data.

It has phone number instead of city.

Please find the below link from which i had taken help to complete my work.

http://www.mrexcel.com/forum/excel-questions/728709-copy-paste-macro-excel.html

Thanks a ton for helping me.

Please help me.

Regards
Umesh

[knockme2]

Need help....Aju Thomas please help

[aju.thomas]

Option Explicit
Sub Rawdata()
Dim data(10) As String
Dim cnt, start, i, end_a, r_macro_data As Integer

    For cnt = 2 To ThisWorkbook.Sheets("raw data 1").Range("A1").End(xlDown).Row
        i = 0
        r_macro_data = ThisWorkbook.Sheets("Macro-data 1").Range("A1").End(xlDown).Row + 1
        ThisWorkbook.Sheets("Macro-data 1").Cells(r_macro_data, 1) = ThisWorkbook.Sheets("Raw Data 1").Cells(cnt, 1).Value
        start = 1
            With ThisWorkbook.Sheets("Raw Data 1")
                start = InStr(start, .Cells(cnt, 2).Value, ":") + 2
                end_a = InStr(start, .Cells(cnt, 2).Value, Chr(10)) - 1
                If end_a = -1 Then end_a = Len(ThisWorkbook.Sheets("Raw Data 1").Cells(cnt, 2).Value) + 1
                If start = 2 Then start = end_a
                data(i) = Mid(.Cells(cnt, 2).Value, start, end_a - start)
            End With
            ThisWorkbook.Sheets("Macro-data 1").Cells(r_macro_data, 2 + i) = data(i)
        
        For i = 1 To 3
            With ThisWorkbook.Sheets("Raw Data 1")
                start = InStr(start, .Cells(cnt, 2).Value, ":")
                start = InStr(start, .Cells(cnt, 2).Value, Chr(10)) + 1
                end_a = InStr(start, .Cells(cnt, 2).Value, Chr(10))
                If end_a = 0 Then end_a = Len(ThisWorkbook.Sheets("Raw Data 1").Cells(cnt, 2).Value) + 1
                If start = 2 Then start = end_a
                data(i) = Mid(.Cells(cnt, 2).Value, start, end_a - start)
            End With
            ThisWorkbook.Sheets("Macro-data 1").Cells(r_macro_data, 2 + i) = data(i)
        Next i
    
    
            With ThisWorkbook.Sheets("Raw Data 1")
                start = InStr(start, .Cells(cnt, 2).Value, ":") + 2
                end_a = InStr(start, .Cells(cnt, 2).Value, Chr(10)) - 1
                If end_a = -1 Then end_a = Len(ThisWorkbook.Sheets("Raw Data 1").Cells(cnt, 2).Value) + 1
                If start = 2 Then start = end_a
                data(i) = Mid(.Cells(cnt, 2).Value, start, end_a - start)
            End With
            ThisWorkbook.Sheets("Macro-data 1").Cells(r_macro_data, 2 + i) = data(i)
            
    ThisWorkbook.Sheets("Macro-data 1").Cells(r_macro_data, 7) = ThisWorkbook.Sheets("Raw Data 1").Cells(cnt, 3).Value
    Next cnt
End Sub

[knockme2]

Many Thanks Aju