need macro for excel file

[ushaanu]

Hi,
Good Morning!
I have an excel file(Sheet-Junk Data) where there are many columns
My Requirement:
I want to keep only few columns like:
customer name ,amount

I am uploading file herewith for your reference. Kindly look into sheet 'Arranged Data' for output. May I request you to help me with VBA solution?
Thanks & Regards,

anu

 

[NARAYANK991]

Hi Anu ,

There are 2 ways of coding this :

1. We see which columns are involved , and use their numbers as static values e.g. Customer Name is column 2 , Machine No. is column 28 , and so on. We copy column 2 , column 28 ,... from the Input data tab.

2. We match the column labels i.e. if the first column on the Output data tab is labelled Customer Name , we look for a match in the header row on the Input data tab , and then copy that column data to the Output data tab.

Both the above methods have their pros and cons.

Which one do you prefer ?

Narayan

 

[Debraj]

Here is a less dynamic, but easy to grab code.. you can modify it according to requirement..

Sub CopySelected()
  Const SrSheet = "Junk Data"
  Const DstSheet = "Arranged Data"
  Sheets(DstSheet).UsedRange.Offset(1).ClearContents
  lR = Sheets(SrSheet).Range("a1").End(xlDown).Row
  With Sheets(DstSheet).Range("a2:j" & lR - 1)
    .Formula = "=HLOOKUP(A$1," & Sheets(SrSheet).Range("a1:aO" & lR).Address(, , , True) & ",ROW()+1,0)"
    .Value = .Value
  End With
End Sub

 

[rumshar]

Hi,
Good Morning!
I have an excel file(Sheet-Junk Data) where there are many columns
My Requirement:
I want to keep only few columns like:
customer name ,amount

I am uploading file herewith for your reference. Kindly look into sheet 'Arranged Data' for output. May I request you to help me with VBA solution?
Thanks & Regards,

anu

Hi Anu,
Here is my version. Since you had highlighted the columns to be retained, it was easy to delete all others. Make sure to take a backup before you run this macro.

With Regards
Rudra

 

[ushaanu]

hi ,

thanks for reply ,

if only column no 2,11,19,20,22,28,33,36,40,and 41 will be retain .

Regards
Anu