Extract data from one xls file to another

[jguilfoyle]

Good Morning,
I have a report that is downloaded off a company database and creates a report that is attached.
Is there a way to create a macro to extract some data that I need and leave the rest:

I need to keep and everything in the assigned column
PB ID (A3), Issue (B3), Group Code (J3), Group Name (K3), Sub group id (L3), Sub Group Name (M3),Subject ID (L3), Subject Group Name (M3), Subject ID (N3), Subject (O3), PB Status (P3), CPM Report Name (T3), CPM Profile Name (U3), Profile State (V3).

The worksheet's name changes so I can't reference the worksheet. Attached is a copy of what I get when downloaded.

Your help is greatly appreciated.

[jguilfoyle]

I've done some work but am a beginner to say the least. I have this code doing some of the things I want but I really need it too gather the data from the columns vice rows.
Can anyone help. The sheet labeled 4425 is what I need it too look like but I need to gather the information in the columns.
Thanks
John
Here is the code to date.

Please Login or Register  to view this content.

[Kenneth Hobson]

Create an XLS, update an MDB or edit the existing XLS? If the latter, then maybe you can post XLS with the columns removed or added based on your example.

You referenced some columns twice but gave conflicting Column names. e.g. Sub Group id (L3), Subject ID (L3).

[stanleydgromjr]

jguilfoyle,

See the attached workbook "Remove Columns from a daily report - sRPT_1239627017(1) - jguilfoyle - SDG.xls" with macro "NewReport".

The macro will create a new sheet, copy the data, then uses an array to remove the unwanted columns.

Run the macro from sheet "RPT_1239627017". It will create a new sheet "New_RPT_1239627017".


Have a great day,
Stan

[jguilfoyle]

I am attaching 2 xls files:
RPT_1239643022.xls is the file as I receive from our database

Fields needed.xls - this is what I need to code to show mining the data from teh appropriate fiels from the original sheet above.

I really appreciate any help you or anyone can offer.
Thanks
John

[jguilfoyle]

Stanley
Thanks, it does what I need it too. However, I can't seem to save teh macro into xls. Is there a way to do taht? I don't have much experience doing this with xls. When I wrote a macro in word I saved it with no problem, but can't seem to do that with this one in xls.

The reason I ask is I will be using this for a number of different xls sheets and would hate to have to copy and paste the macro each time.
Thanks
John

[stanleydgromjr]

John,

You could copy the "NewReport" macro, into your PERSONAL.XLS file, into a Module.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).



Press and hold down the 'ALT' key, and press the 'F11' key.

On the left side of the display, click on the "+" box for "VBAProject (PERSONAL.XLS)"

On the 'Insert' menu, click 'Module'.

Copy the below code, and paste it into the Module (on the right pane).

Please Login or Register  to view this content.

Then you culd run macro "NewReport" from PERSONAL.XLS, against the worksheet "RPT_#########" that is active.


Have a great day,
Stan

[jguilfoyle]

Thanks, it works like a charm. Appreciate your help.
John

[spearo99]

Hi,

I am trying to modify the macro to suit my workbook and I can't seem to adjust the code. I have attached the way an export looks (column headings) and then the second tab show the order I need to have them come out following the macro. Any assistance appreciated.

[stanleydgromjr]

spearo99,

See the attached workbook "Array - delete unwanted columns - reorder columns - Macro Fields(1) - spearo99 - SDG10.xls" with a new "NewReport" macro.

With sheet "WS" as the active sheet, run the "NewReport" macro.

[spearo99]

Hi Stanley,

Thanks for the above, the only thing is when I run the macro there is a gap in the file with the column heading sub group not appearing. Not sure why as it is listed in the array?

[stanleydgromjr]

spearo99,

I had in the array, "Sub Group()". It should have been "Sub Group".

And, I added some code, that if a title was not found in row 1 or your data set, that all the correct titles would be in the new worksheet.

See the attached workbook "Array - delete unwanted columns - reorder columns - Macro Fields(1) - spearo99 - SDG10.xls" with the updated "NewReport" macro.

With sheet "WS" as the active sheet, run the "NewReport" macro.


How much time do you think my macro will save you in hours per day/week/month?