Using Macros to clean and input data in a worksheet

[anuradhagrewal]

Hi
Happy New Year
I seek your help in writing a macros with the following parameters
I have data in the range from A1:P20000 in worksheet named COMBINED.
Now this is what I kindly desire
1)The contents of column P are cut and inserted in the place of column B or after column A
2)The dates are in dd-mmm-yy format needs to change to YYYYMMDD format.
3)The following columns need to be deleted

• Column C
• Column D
• Column H
• Column J
• Column K
• Column M-O

4)Post this I need to input the following Values in the given fields

• Column A : <Name>
• Column B: <Date>
• Column C: <Location 1>
• Column D: <location 2>
• Column E: <Dept>
• Column F: <Designation>
• Column G: <Address>

5)I then need to save the file as Data.csv file.

I request your kind help on creating such a macros.

Much Obliged

Anu

 

[mohadin]

Hi
What about

Sub test()
    Columns("P:P").Cut Destination:=Columns("B:B")
    Set DELCOL = Union(Columns("C:D"), Columns("H:H"), Columns("J:K"), Columns("M:O"))
    DELCOL.Delete
    Columns("B:B").NumberFormat = ("YYYYMMDD")
    Range("A1:G1") = Array("Name", "Date", "Location 1", "Location 2", "Dept", "Designation", "Address")
End Sub

 

[anuradhagrewal]

Hi
What about

Sub test()
    Columns("P:P").Cut Destination:=Columns("B:B")
    Set DELCOL = Union(Columns("C:D"), Columns("H:H"), Columns("J:K"), Columns("M:O"))
    DELCOL.Delete
    Columns("B:B").NumberFormat = ("YYYYMMDD")
    Range("A1:G1") = Array("Name", "Date", "Location 1", "Location 2", "Dept", "Designation", "Address")
End Sub

Thanks Mohadin it worked as a dream..
However I have to add a few more parameters

Now the Range is from A1:M12000
The following is what I seek your humble opinion is as follows

1)In column B I need the data to be filtered with the variable "Present". Except those cells in Column B having the word "Present" all other rows need to be deleted.
2)Once the column B contains only the word "present" then I need the content of Column K to be overwritten in Column B.
3)Now the date is in Column K in dd-mmm-yy format and needs to be as previously to yyyymmdd when it is replaced in Column B.

4)The following columns need to be then deleted

• Column F
• Column H
• Column J
• Column L-M

5)Post this I need to input the following Values in the given fields

• Column A : <Name>
• Column B: <Date>
• Column C: <Location 1>
• Column D: <location 2>
• Column E: <Dept>
• Column F: <Designation>
• Column G: <Address

Thanks a Ton in advance ?

Regards

Anu