Macro to eliminate rows, columns and create a pivot table

TopLearner

Board Regular
Joined
Feb 25, 2022
Messages
57
Office Version
  1. 365
Platform
  1. Windows
Good afternoon,

I would like to create a Macro in VBA to eliminate the first row and then some columns whose headers I am listing below, and then create a pivot table.

1. To eliminate the first row

2. To eliminate columns: Employee number (A),Pay Class name (D), Pay type name E, Site (F),Pay category name (J)

3. To create a pivot table: Employee number, employee first name, employee last name, department name, job name and business name in the rows fields.

Can you please indicate the code to write in VBA?


Thanks
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
How about using Power Query (Get and Transform data) for that task?
You will have the data untouched, and Power Query output will not go to the worksheet but directly to Pivot Table
 
Upvote 0
BTW. You mentioned:
2. To eliminate columns: Employee number (A), ...
and then
3. To create a pivot table: Employee number
??? this column havs been removed in previous step (or you have 2 columns with the same header)
 
Upvote 0
BTW. You mentioned:

and then

??? this column havs been removed in previous step (or you have 2 columns with the same header)
Hi thanks for your answer,

Genuine mistakes. Please, see the right steps here


1. Delete the first row

2. Delete columns: Pay Class name, Pay type name, Site,Pay category name.

3. Create a pivot table with Employee number, employee first name, employee last name, department name, job name and business date in the rows field. With tabular form and removing subtotals per each one.

Thanks again
 
Upvote 0
How about using Power Query (Get and Transform data) for that task?
You will have the data untouched, and Power Query output will not go to the worksheet but directly to Pivot Table
Hi Kaper,

Thanks. However, I am extracting the data from another company software so how could I get the data updated by using the app link?
 
Upvote 0
There are chances that PQ can read the data stored by this software. But the answer is general, as the statement was general (another company software could be anything).
Also ... You can apply PQ to a table already imported into Excel file. And then you will have basically the same situation as with the "import and delete columns and row" macro - just one table with data and pivot table in your workbook. Power Query will remain invisible for most of the time.

But let me return to first sentence. If the other software stores data in useful form, your workbook will be quite lean. It will contain only Pivot table (and the PQ working in background)
 
Upvote 0
There are chances that PQ can read the data stored by this software. But the answer is general, as the statement was general (another company software could be anything).
Also ... You can apply PQ to a table already imported into Excel file. And then you will have basically the same situation as with the "import and delete columns and row" macro - just one table with data and pivot table in your workbook. Power Query will remain invisible for most of the time.

But let me return to first sentence. If the other software stores data in useful form, your workbook will be quite lean. It will contain only Pivot table (and the PQ working in background)
The other software cannot be formatted easily and that´s why I do the formatting on Excel (it is much quicker). So for instance, if I download the data from the next month, will I have to add it to the data model and then copy the formula bar from the previous query into the new one to apply the format? How about the pivot table, can be also done with Power BI?

Thanks
 
Upvote 0
Yes, it can
And as you have data in the data model, PQ is again a natural choice
 
Upvote 0
Yes, it can
And as you have data in the data model, PQ is again a natural choice
Thanks, Kaper. However, I think a macro could be faster as you just have to run it. From my understanding (I attaching an example so you can understand me), if I want to display the same table for each query (i.e. apply the same conditions to all of them) I have to copy and paste the code in the advanced editor of each one?

Copiar patron.png
Table.png
 
Upvote 0
You can make a Power Query that iterates on a table with the separate URLS.
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top