Macro that copies an unpredictable generated range so I can paste in other programs

[DaKhoda]

Good morning all, and thank you kindly for reading this.

I have a macro that copies rows from my DAILY OCCURENCE sheet that have a YES in them to my MANAGER SUMMARY SHEET and G1 is selected which has a hyperlink that opens a new email with the manager's email address and Subject pre-assigned.

I then have to manually go back to the newly generated summary sheet and select the cells and paste them into the email that was just opened.

Is there a way that the generated rows are already copied in memory and all i have to do is CTRL+V into the new email, or even better, the cells are pasted in the email automatically? So basically either the information being copied over stays in memory, or after it is copied into the summary sheet it is copied again. I just dont know how to copy unpredictable ranges generated by macros.

The code that generates my summary sheet for my manager is below.

Please Login or Register  to view this content.

Thanks heaps guys.

[nathansav]

Sheets("Manager Summary Sheet").Select could be ManagerSheet.Select


Not tested

Please Login or Register  to view this content.

Why not build the email from Excel to? You can remove the .send function to display the email before sending.

google Automating Outlook VBA.

[DaKhoda]

Thank you. Will give it a go.

[DaKhoda]

unfortunately it froze my email program and I think it selected and copied a large amount of cells as indicated by the selection box with an area beyond my actual used range.

[DaKhoda]

Found a solution copying a dynamic range and keeping it in memory to manually paste it anywhere without needing to use the mouse to drag and select.
Worksheet1 is the worksheet you want to copy the range from,
A1 is the cell with the begining of the range
G is the column with the last cell with the information you want to copy. The code is as follows:

Please Login or Register  to view this content.