VBA to return to last ACTIVE Worksheet to copy additional data

[Seaplane Jack]

Hello All:

This is frustrating as there are many "close but no cigar" solutions for my issue in cyberworld but none of them seem to do exactly what I need. Here we go:

From any given sheet, I want to be able to copy data from a cell and paste it to a specific sheet (we'll call it the "report" sheet) and then go back to that previously active sheet and copy additional data and also paste that to the report sheet.

I've seen several options for creating a button that will take you back to the last active worksheet, but nothing that simply runs within a single macro as part of it to go back and forth. The end result I'm looking for is to be able to copy data from the sheet a user is working with (1 of possibly 9) and paste it to the report sheet, but the data I need to copy is located in multiple areas of the original sheet (e.g. Cell A1 and Range B4:C30) so I (think I) need to go back and forth twice to copy - and then paste - the data from these two separate locations.

I'm trying to avoid writing multiple macros for every sheet a user can be working with just to copy a few cells of data to a report.

Help?

As always, thanks in advance for any guidance! ExcelForum rocks!!!

Seaplane Jack

[Norie]

You don't need to switch between sheets to copy/paste, so you could write code that never leavs the sheet you are copying from.

[TMS]

You don't need to select a sheet, or a cell/range on that sheet, in order to copy it and paste it somewhere else. And you don't need to select a target sheet in order to paste the data there. So, why do you want/need to do that?

With Sheet1 active, you could say:

Please Login or Register  to view this content.

Regards, TMS

[Seaplane Jack]

Ok - I think I'm following the logic, but how do I get the data from Cell A1 of the Active sheet pasted to cell AA1 of the report sheet - and likewise the data from range B4:C30 pasted to AB1:AC30 of the report sheet.

Perhaps I've got a mental block going on and I'm making this much harder than it needs to be!?!?

Thanks!

[Norie]

All you need is this, obviously change 'ReportSheet' to the correct name for your 'report' sheet.

Please Login or Register  to view this content.

[TMS]

and I'm making this much harder than it needs to be!?!?

Yep. Norie has provided the basic building block.

[Seaplane Jack]

Thanks, Norie! It works perfectly! Clearly I WAS making it much harder than it had to be. I didn't realize you don't actually need a "paste" command to paste!

Another satisfied customer...

As you were!

[TMS]

You're welcome. Thanks for the rep.