Copy data to another sheet in excel macro

[coolkiran]

Hello

I have set of data in one sheet, that i need to copy paste to another sheet. I have attached the sample excel file.

I have shown one example in this file. Please let me know is that possible in Excel Macro.

 

[Gangadhar Yeligaty]

Hi Kiran,

Can you please provide some more sample records?

The address columns is not in consistent order for all the records.

Regards,
Gangadhar Yeligaty

 

[coolkiran]

Thanks for your reply Gangadhar

Regarding Address column, always there will 3 lines, first line is address.
Second line, consists of City, Province and zip.
Ignore 3rd line.
For example Second line will be like below:
PIERREFONDS, QUE. H9H 2S3
Here PIERREFONDS is City
QUE. Is Province
and H9H 2S3

So City will be till ","
and Province will be till "."
and remaining is zip code.
This is common in all data's.

I have updated in file with few more data.

 

[Marc L]

Hi,

use Split function to separate data from a cell …
​

 

[coolkiran]

Thanks Marc,

Address part i think Split will work. But no idea on remaining part as in my excel file.

 

[Marc L]

This is common in all data's

Nope !

You must clean your file, erase cells full of spaces, …

Start your code with the use of Macro recorder …
​

 

[NARAYANK991]

Hi Kiran ,

If your data is in a standard format , it is always easier to use formulae rather than write a macro ; if your data varies in the number of lines allotted to each entry , then using a formula is more difficult compared to using code.

With your existing data , there is no problem extracting each element from column D since each entry has one line dedicated to it ; if the colon is going to be present in every entry , extracting the data using formulae is not difficult.

Narayan

 

[coolkiran]

Thanks Narayan,

My data will be in standard format as in my file. But will have more than 100 records, so formula i don't think how it will work. So i thought to use macro, by clicking button, if output generates in another sheet, that will make my work easier than writing formulas in each row.

 

[Marc L]

​

Sub DemoNope()
    For Each V In [{9,14}]
        S$ = Worksheets("Input").Cells(V, 3).Value
        SP = Split(S, ",")
        S$ = S & vbLf & vbLf & "City  :  " & SP(0) & vbLf

        If UBound(SP) Then
            SP = Split(SP(1), ".")
            S = S & vbLf & "Province  :  " & SP(0) & vbLf
            If UBound(SP) Then S = S & vbLf & "Postal Code  :  " & SP(1)
        End If

        MsgBox S
    Next
End Sub

​