Need macro code to rearrange columns and to delete unwanted columns

Hi Team,

I need your help to get the simple macro to rearrange the columns using the data given in the attached file named "Raw data" and delete the unwanted columns.

Columns should rearrange as per the column header which I have given in the file named "report" and insert one column after B column(Date Created) & P Column (Last Updated Date).

Add the formula in C & Q column as I have given in the file named "report" and remove the formulas on both columns.

Note - In the attached sample reports I have given only 4 rows, so I need to use this macro for more rows, so sometimes it will be more than 500 as well.

Kindly check and assist me on this request.

I am new to the macros, so I manually updated yhe reports. Kindly help me in creating the macro for my report.

For the file you attached.

Please Login or Register  to view this content.

A different tact, using Advanced Filtering (assumes report worksheet only has headers and does not contain formula column headings)...

Please Login or Register  to view this content.

Thank you very much for the code.

I have one more request, I have attached 2 more reports named "Raw Data 1.xlsx" & "Consolidated report.xlsx".

I need your help to get the simple macro to rearrange the columns using the data given in the attached file named "Raw data 1" and delete the unwanted columns.

Columns should rearrange as per the column header which I have given in the file named "Consolidated report" and insert one column after B column(Date Created) & P Column (Last Updated Date).

Add the formula in C & Q column as I have given in the file named "report" and remove the formulas on both columns.

After the above steps, the data needs to split based on the Tenant code given in the S column by creating a new sheets (sheet name should be tenant code name) and delete the rows if the column D (Global Vendor) is having the below given names in each sheet, but in the consolidate report none of the rows should delete.

AT&T WIRELESS DO NOT USE
VERIZON WIRELESS
AT&T MOBILITY
T-MOBILE
ROGERS WIRELESS
TELUS MOBILITY
T-MOBILE UNITED STATES


Note - In the attached sample reports I have given only few rows, so I need to use this macro for more rows, so sometimes it will be more than 500 as well.

Kindly check and assist me on this request.

Hi dangelor

I am getting error using the macro code which you have given.

Hi Jindon,

I have only one file named "Raw Data 1.xlsx", some times even the file name will change. Actually I need the code to run in Raw data 1.xlsx file and I need the report which is similar to the Consolidate Report workbook with the steps given above. Kindly check and assist me.

Also the Wrap Text option should inactive in each sheet.

Then change to

Please Login or Register  to view this content.

Hi Jindon,

Thank you very much the code is working fine but it is running bit slow.

It would be slow when there are large amount of data in raw data.

You could try set ScreenUpdating False and True e.g.

Please Login or Register  to view this content.

Thank you very much Jindon.

As I requested earlier, please check and assist me on the below.

Also I need one last help, the macro code which you have given below is to run on the active sheet, so is there any chance where I can create one button to add this macro and once I clicked the button it should ask for the raw file and once the raw file is selected the consolidated report should ready in a different file.

Not clear.

All the reports should be created within the raw file or current workbook?

Originally Posted by jindon

Not clear.

All the reports should be created within the raw file or current workbook?

I need to create a macro button in one work book. Once I open that workbook and click on that button it should ask for the raw file. Once the raw file is selected, the above given macro should run and it should create consolidated report.

Reports to where?

Raw data workbook? or the one you are calling the sub from?

Sorry for the confusion. Once the raw file is selected using the macro button, the above given macro should run and the consolidated report should come in new workbook.

Currently the macro is running based on the column number to rearrange/delete the columns, instead can you please change that to column header name. So if I run the macro in any of the report, the column should rearrange/delete based on the column header name.

Please help me on the above request

How do you determine which columns should be left?

Do you have a list for the heading somewhere in worksheet?

Hi Jindon,

Thank you for checking, I have already provided the column header names what I need in the Consolidated report.xlsx‎ worksheet which I was provided earlier. Please don't do anything for now.
I will send you the the updated report what I need and the details by tomorrow, so that you can assist me on this request, instead of working on the old report. Thank you very much for your help.

It is late here (I'm about to got to sleep) and I might have missed something, but if I didn't and acknowledging that the OP asked for addition code afterwards which I am not addressing, then I think this non-looping macro will do what the codes posted in Messages #4 and #5 do (possibly even quicker)...

Please Login or Register  to view this content.