Macro to Export Data from one spreadsheet to a Report Spreadsheet

Hi,

I have an excel spreadsheet that analyzes data and produces results. Weekly, I put through alot of datasets and I think it would be handy to have a button that exports the crucial data to a common spreadsheet. I'm guessing I can use some sort of insert shift down to create the report.

Is it possible to do this?

The data I'm interested in having captured and listed in a REPORT.XLSM is all from sheets "DATA" and in order is:

L14 M39 N39 L33 N33 L10 M15 N15 L47 M12 M13

Any help is appreciated.

Thanks

Just curious to know if you've tried using a Pivot table for this purpose. the good thing about pivot tables is that they refresh when the original data refreshes, or at least you can run some code to refresh them given a certain trigger.

I need a separate file that I can export certain data to each time I run my main excel spreadsheet. This separate file will be a report file and each line in it will be the results of each time I run the main spreadsheet. Will a separate file with a pivot table accomplish this?

Sure it can be connected to any workbook. All you have to do is follow the Connection prompts after selecting the Insert/Pivot Table/Use an External Data Source.

The problem with that is I need to create a report with data added to it on each run of the main program.

I also save each run of the main program with the dataset name.

I think it would be easiest to just have a macro that exports the certain fields to a Report.xlsm, adding a line of results each time.

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

The data I'm interested in having captured and listed in a REPORT.XLSM is all from sheets "DATA" and in order is:

L14 M39 N39 L33 N33 L10 M15 N15 L47 M12 M13

The data should be inserted into REPORT.xlsm so that each run of the macro lists another line of data.


Any help is appreciated.

Something like

Please Login or Register  to view this content.

will do it. There are probably better ways to code this but that's what this dork's got for you!

Hey Thanks,

I appreciate it. I'll learn from it.

I'm just having problems getting it to work as I get the error "Subscript out of range" and it highlights:

Please Login or Register  to view this content.

I did create a REPORT.xlsm & put it in the same directory as the Sampledata.xlsm.

Did you get it to work?

Yes it worked for me. Was the workbook open when you ran the code? If not, the code will have to change a bit.