MS Excel VBA - ScreenUpdating False not working Excel 2016/2019

[Vbalearner85]

I have below macro to run for file "Data.xlsm" to copy data from sheet "Cdata" to "Ddata". I want this code to run without going to the sheets (to not show the vba actions)..but it is not working..Any suggestions as to how to make it happen ..so that I can work on some other tab in the same workbook..along with macro ruining in the background.

Sub Ddata()

Application.ScreenUpdating = False

Sheets("CData").Range("A2:M142").Copy
Sheets("Ddata").Activate

Dim lastrow As Long
lastrow = Range("A1048576").End(xlUp).Row

Cells(lastrow + 1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


End Sub

 

[Anthony47]

ScreenUpdating = False is not "perpetual", but will last only during the macro execution; therefore after End Sub you will see your workbook in its final condition, i.e. DData active
If you don't want to show DData then you should select the starting worksheet before ending the sub.

Or you could do the job in a complete different way, as you don't need to move the worlsheets to access their data:

Sub Ddata()

Dim lastrow As Long
lastrow = Sheets("Ddata").Range("A1048576").End(xlUp).Row
Sheets("Ddata").Cells(lastrow + 1, 1).Resize(141, 13).Value = Sheets("CData").Range("A2:M142").Value

End Sub

This macro will put the content of sheet CData onto sheet DData without the need to select neither the source nor the destination.

Bye

 

[dmt32]

Try this update to your code

Sub Ddata()
    Dim lastrow As Long
    
    Application.ScreenUpdating = False
    
    Worksheets("CData").Range("A2:M142").Copy
    
    With Worksheets("Ddata")
        lastrow = .Range("A1048576").End(xlUp).Row + 1
        .Cells(lastrow, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End With
    
    Application.CutCopyMode = False

End Sub

Dave

 

[Vbalearner85]

Thanks Anthony and Dave...tested both of above macros ....gave expected results...Thanks so much...

One small edit.request....if I am working with 2/3 open workbooks..how can I get it run on..specific workbook..."Data.xlsm" ....

 

[dmt32]

One small edit.request....if I am working with 2/3 open workbooks..how can I get it run on..specific workbook..."Data.xlsm" ....

Try update to code & see if does what you want

Sub Ddata()
    Dim lastrow As Long
    Dim wbData As Workbook
    
    Set wbData = Workbooks("Data.xlsm")
    
    Application.ScreenUpdating = False
    
    With wbData
        .Worksheets("CData").Range("A2:M142").Copy
    
    With .Worksheets("Ddata")
        lastrow = .Range("A1048576").End(xlUp).Row + 1
        .Cells(lastrow, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End With
    
    End With
    
    Application.CutCopyMode = False

End Sub

Dave

 

[Anthony47]

Don't miss @dmt32 answer, above

With my code, if the macro is saved within Data.xlsm workbook, you may use

Sub Ddata()

Dim lastrow As Long
With ThisWorkbook
    lastrow = .Sheets("Ddata").Range("A1048576").End(xlUp).Row
    .Sheets("Ddata").Cells(lastrow + 1, 1).Resize(141, 13).Value = .Sheets("CData").Range("A2:M142").Value
End With
End Sub

Bye

 

[Vbalearner85]

@Dave ..This works...but flickers screen to make workbook "Data.xlsm" visible during macro run and then comes back to the current other open workbook...how can we that macro action silent/invisible as well..so that I can work on other workbook seamlessly

 

[Vbalearner85]

Don't miss @dmt32 answer, above

With my code, if the macro is saved within Data.xlsm workbook, you may use

Sub Ddata()

Dim lastrow As Long
With ThisWorkbook
    lastrow = .Sheets("Ddata").Range("A1048576").End(xlUp).Row
    .Sheets("Ddata").Cells(lastrow + 1, 1).Resize(141, 13).Value = .Sheets("CData").Range("A2:M142").Value
End With
End Sub

Bye

Thanks....worked perfectly...as I wanted to..

 

[dmt32]

I suspect the placing of the ScreenUpdating line needs to be moved above Set statement

Application.ScreenUpdating = False
    
  Set wbData = Workbooks("Data.xlsm")

Dave

 

[Vbalearner85]

I suspect the placing of the ScreenUpdating line needs to be moved above Set statement

Application.ScreenUpdating = False
   
  Set wbData = Workbooks("Data.xlsm")

Dave

I tried doing that as well...but still persists..