Two Problems 1. keep getting a clear clipboard message 2. wont close workbook completely

[Summerso1]

Hi,

I have to code below which works but there are two things that I need it to do.

1. at the point of paste don't ask if I was to clear the clipboard
2. completely close the Email_Report after its finished

Sub copy()

Sheets("DATA").Select




Dim wb1 As Workbook
Dim wb2 As Workbook


Set wb1 = Workbooks("Email Tool.xlsm")
Dim objApp As Excel.Application
Set objApp = CreateObject("Excel.Application")
objApp.Visible = True
Set wb2 = objApp.Workbooks.Open("H:\Online\Email\Reporting\Email_Report.xls")




wb2.Sheets("email_report").Range("C:M").copy




wb1.Sheets("DATA").Range("A1").Select


wb1.Sheets("DATA").Paste
Application.CutCopyMode = False


Range("A1:K9").Select
    Selection.Delete Shift:=xlUp


wb2.Close SaveChanges:=False


Set wb2 = Nothing
Set objApp = Nothing

 

[Summerso1]

Help!!!!! I wont make it on my own!!

 

[Darren Bartrup]

Try this code (on a copy of your work first)...

Sub Copy()

    Dim wb1 As Workbook, wb2 As Workbook
    
    Set wb1 = ThisWorkbook ' I'm guessing Workbooks("Email Tool.xlsm") is the one with the code in.
    Set wb2 = Workbooks.Open("H:\Online\Email\Reporting\Email_Report.xls")
    
    wb2.Sheets("email_report").Range("C:M").Copy Destination:=wb1.Sheets("Data").Range("A1")
    wb1.Sheets("Data").Range("A1:K9").Delete Shift:=xlUp 'This is deleting some of the data you've just pasted in???
    
    wb2.Close Savechanges:=False


End Sub