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.

Sub CopyYES()

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual


Dim lngLastRow As Long
Dim ManagerSheet As Worksheet

Set ManagerSheet = Sheets("Manager Summary Sheet") ' Target sheet where all the YES occurrences go to.

lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row


With Range("D43", "h150" & lngLastRow)                       ' This is the actual range where occurences are logged on the OCCURRENCE sheet.
    .AutoFilter
    .AutoFilter Field:=4, Criteria1:="Yes"                         ' The criteria to filter is Yes.
    .Copy ManagerSheet.Range("A2")                              ' Copies and pastes to Manager Summary Sheet
End With


Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
    Selection.AutoFilter
    
    Sheets("Manager Summary Sheet").Select
    Cells.Select
    Cells.EntireColumn.AutoFit                                        ' Autofit rows to fit contents of cells
    
    Range("G1").Select                                                   ' G1 has the hyperlink to open the email with the manager's email address.
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    Range("A1").Select

End Sub

Thanks heaps guys.

[nathansav]

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


Not tested

ManagerSheet.Usedrange.select
selection.copy

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:

With Worksheets("worksheet1") 
.Range("A1:G" & .Cells(Rows.Count, "G").End(xlUp).Row).Copy
End With