Excel Macro - Copy and Paste to a selected cell

[jonsi]

Good morning

I have a Spreadsheet that is populated from a Form input. Choices from Drop Downs, Radio Buttons, Lookups and free text etc.
All the outputs returned from those Inputs go to a range called OUTPUT on a hidden sheet
I want to run a macro that copies all of this OUTPUT range and dumps (Paste Special Values) and transposes it into an Operator selectable or (better still) next available row in a Sheet in a Summary workbook.
I know I can do it to a specified row but how do I get it to pause while the Operator selects the correct row?

thanks
Jonsi

hwyl
Jonsi B-)
"If an apple a day keeps the Doctor away ...why don't Daleks live in Orchards?"

 

[kjv1611]

Are you certain you even need VBA? If it's truly a summary of the data, have you tried these options?
[ul]
[li]Pivot Table[/li]
[li]MS Query[/li]
[li]Power Query (newer versions of Excel)[/li]
[/ul]

If you are certain you need a VBA solution, and have no idea where to start but can manually do what you're thinking to do, try to record your actions with the macro recorder, and take a look there. Otherwise, it's advisable to ask VBA specific questions in the following forum:
forum707

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[jonsi]

not certain I need VBA ...

Guess I should have written this bit earlier ....

Several workbooks (differently named) will be opened, each identical in structure (they will all have used a copy of the same original workbook) but with different data in the OUTPUT range. I need to then collate that data into a single summary in another Workbook (essentially building a records file from which other analysis & reports will run).

Maybe I'm looking at it the wrong way. Maybe I should start my macro at the destination (Summary File) and 'go fetch' the data rather than try to 'send' the data??

hwyl
Jonsi B-)
"If an apple a day keeps the Doctor away ...why don't Daleks live in Orchards?"

 

[kjv1611]

Without getting into the weeds, I think what you're trying to do now is:
[ul]
[li]Go from one original source[/li]
[li]to multiple outputs[/li]
[li]from those outputs to one summary[/li]
[/ul]

If you really need the different outputs, then I suggest you do more like:
[ul]
[li]Go from one original source[/li]
[li]to multiple outputs[/li]
[li]from THE SOURCE to one summary[/li]
[/ul]

Then it'll be so much simpler, assuming you're not adding additional data to the multiple outputs that you're trying to summarize. If you're adding data to the outputs, is it possible to instead keep them all in the original source instead?

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[jonsi]

Without getting into the weeds, I think what you're trying to do now is:
•Go from one original source
•to multiple outputs
•from those outputs to one summary
If you really need the different outputs, then I suggest you do more like:
•Go from one original source
•to multiple outputs
•from THE SOURCE to one summary

Just to clarify I'll start at the beginning...

There's a structured Workbook, a template for want of a better word [original.xlsm] with one worksheet in the Workbook acting as a Form type input. Some of the answer options on this form are from Combo-boxes, some are simple radio buttons, some are Lookups and some are free text. Output of answers from the Combo boxes etc. are stored on another sheet in the same Workbook in a range called OUTPUT. That workbook, when complete, is saved as a file in it's own right [File1.xlsm] which also serves a function as a stand-alone record.

Another user will also open the same Template file [original.xlsm], answer the questions and uniquely save it [File2.xlsm]. It too has answers in a range called OUTPUT.

Then another user will open the Original file, answer the questions and uniquely save it [File3.xlsm]...It will also have answers in a range called OUTPUT

...and so on.

I need to then summarise the answers (OUTPUT ranges) from each of the Files by copying and pasting the OUTPUT ranges into a Summary File.

If it were just 3 files and just me doing this all the time then no problem because I'd manually intervene and know what I was doing but there will be dozens of files. I need to make this Copy and Paste function automatic (Macro) because different colleagues in different depts. will be using this. The filename cannot follow a defined naming protocol because it will contain a proper name which won't be known in advance. This means the various unique file's OUTPUT range needs to be 'fetched' then placed into the next available row in a completely separate sheet in a SUMMARY File.


hwyl
Jonsi B-)
"If an apple a day keeps the Doctor away ...why don't Daleks live in Orchards?"

 

[kjv1611]

OK, so the original file is more of a template than a data source. Yeah, that's far different. Here where I now work, we've got a system that works with loads of VBA for handlign "Templates" that are opened like what you're talking about, and then they follow a path. Currently, there is no summary type information pulled from them, but final results are uploaded to a backend SQL database for an application.

In your scenario, I do kind of wonder if it'd be worth setting it up to dump all the data to a database from the workbooks as they're saved, so then you can do whatever you need with the data from there. You could set it up to add something referencing the file name and/or date and/or username so you've got some tracking history and links back to the original documents. You could do it in Access or SQL Server or whatever database works best for you. Then for reporting, you could setup Excel workbooks that link to the database, all setup and refreshable. Sorry if I'm going in another direction altogether, but I think that's what I'd want to seriously consider if it were me.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

I'd try as much as possible to avoid having all these data fragments (separate files) and get them into a database ASAP.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kjv1611]

I imagine the reason for the fragments currently is for auditing purposes. I know that's the purpose where I work.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[jonsi]

I imagine the reason for the fragments currently is for auditing purposes. I know that's the purpose where I work.

not far off ...the files, although using the original template, create a legal record in their own right. To have them just as a line in a database doesn't achieve the same thing. Each record (or several records) could come from one of several locations.

hwyl
Jonsi B-)
"If an apple a day keeps the Doctor away ...why don't Daleks live in Orchards?"

 

[SkipVought]

Sounds like a transaction file with an EmployeeID, Transaction Code, Date Stamp. I'm

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kjv1611]

Yeah, if you can dump something like that:

Sounds like a transaction file with an EmployeeID, Transaction Code, Date Stamp.

to a database besides building the separate files, then that'd be ideal. That way you'll have both the audit trail, as far as "physical" files for reference, but also have the single data source.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[jonsi]

I think I've come up with a solution and I don't know why I didn't think of it before...

If I start the Macro in the Summary Workbook and 'go fetch' the OUTPUT range from the source file then this will work up to the point where it dumps the data back in the Summary File. As the Macro always starts from a fixed point (say A5) it will always dump data back to that same fixed point...

...unless, after dumping to A5, I INSERT a row as the last part of the macro, essentially creating a new (and empty) Row 5, the Summary sheet will always be ready for the next fetch of data from another file and won't overwrite data.

Thanks for your help and support guys.


hwyl
Jonsi B-)
"If an apple a day keeps the Doctor away ...why don't Daleks live in Orchards?"