Optimize VBA copy/paste

[Giulianeo]

Hey guys,

I am basically trying to make my code run faster, I am trying to convert a section of the code that copies and pastes data across two workbooks to be faster and cleaner.

Here is the original paste/copy data:

Sub WALLCERTIFICATE()'
' Macro2 Macro
'
Dim M As Workbook
Set M = ActiveWorkbook


' Copies / paste data from trainer scoresheet to digital certificate.
    Range("A1").Select
    Selection.Copy
    Workbooks.Open "V:\DEPARTMENTS\DRIVER TRAINING\COORDINATION\Scoresheets\Wall Certificate Macro\Digital Wall Certificate.xlsx"
    Windows("Digital Wall Certificate.xlsx").Activate
    Sheets("ColourFast").Select
    Range("A37").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    M.Activate
    Sheets("COURSE").Visible = True
    Sheets("Colourfast Printing").Visible = True
    Sheets("Student").Visible = True
    Sheets("Colourfast Printing").Select
    Range("A1:P33").Select
    Selection.Copy
    Windows("Digital Wall Certificate.xlsx").Activate
    Sheets("ColourFast").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

And here is the "cleaner" version

Sub WALLCERTIFICATE()'
' Macro2 Macro
'


Application.ScreenUpdating = False
Dim M As Workbook
Dim K As Workbook
Set M = ActiveWorkbook
Set K = Workbooks.Open("V:\DEPARTMENTS\DRIVER TRAINING\COORDINATION\Scoresheets\Wall Certificate Macro\Digital Wall Certificate.xlsx")


' Copies / paste data from trainer scoresheet to digital certificate.
    
    M.Activate
    Sheets("COURSE").Visible = True
    Sheets("Colourfast Printing").Visible = True
    Sheets("Student").Visible = True
    M.Sheets("Colourfast Printing").Range("A1").Value = K.Sheets("Colourfast").Range("A37").Value
    M.Sheets("Colourfast Printing").Range("A1:P33").Value = K.Sheets("Colourfast").Range("A1").Value

Of course it doesn't work at all, so would anybody be so kind as to help me figure out why?

Thank you!!!

 

[Giulianeo]

have never encountered anything like and cannot see anything in the code that would cause the problem, so cannot help.

I figured it out, it was the "Application.Visible = True" line below, as I suspected it was related to outlook.. when I deleted or disabled the line, no longer did I get grey workbooks opening after running the entire macro. I wanted to share in case it helps someone else in the future.

[COLOR=#333333]' Try to send[/COLOR]    On Error Resume Next
    .Send
   [COLOR=#ff0000] Application.Visible = True[/COLOR]
     If Err Then
      MsgBox "E-mail not sent, please ensure the email field is not empty and double check it for any spelling errors.", vbExclamation
    Else
      MsgBox "E-mail successfully sent.", vbInformation
    End If
    On Error GoTo 0
    [COLOR=#333333]    End With[/COLOR]

Thanks!!

 

[Fluff]

Glad you sorted it & thanks for the feedback