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

 

[dmt32]

No worries looks like you have found a solution.

Dave

 

[Vbalearner85]

No worries looks like you have found a solution.

Dave

Yes Dave...got the solution.......thanks for your help !!!!

 

[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

Hi,

As I am updating my Macros, looking for some enhancements

How can I modify macro to auto change sheets names in the same macro, starting from sheet "DData" (9-10.30am)>>"EData" (10.30am-12 Noon)>>"FData" (12 Noon -1.30pm)and then "Gdata" ((1.30pm-3pm) and then copy data into these sheets as per time conditions. As of now I use 4 macros to run from Workbook module based upon time, looking to consolidate it to single macro

Regards,
PK

 

[Anthony47]

You may use for example If /ElseIf /End If to decide which worksheet has to be used; for example:

Sub AnyData()
Dim lastrow As Long
Dim aSheet As String

'A) Decide which sheet:
If Now > TimeSerial(15, 0, 0) Then
    Exit Sub                                     'Not sure what to do when its too late...
ElseIf Now > TimeSerial(13, 30, 0) Then
    aSheet = "GData"
ElseIf Now > TimeSerial(12, 0, 0) Then
    aSheet = "FData"
ElseIf Now > TimeSerial(10, 30, 0) Then
    aSheet = "EData"
ElseIf Now > TimeSerial(9, 30, 0) Then
    aSheet = "DData"
Else
    Exit Sub                                     '..or when it's too early
End If
'Transfer to "that" sheet:
With ThisWorkbook
    lastrow = .Sheets(aSheet).Cells(Rows.Count, "A").End(xlUp).Row
    .Sheets(aSheet).Cells(lastrow + 1, 1).Resize(141, 13).Value = .Sheets("CData").Range("A2:M142").Value
End With
End Sub

Bye

 

[Vbalearner85]

Hi,
Not sure what is wrong..but code is not working...

 

[Anthony47]

OMG...
I confused Now (date + time) with Time (only time of the day)
So you should replace all those "Now" with "Time", i.e.

Sub AnyData()
Dim lastrow As Long
Dim aSheet As String
'
Debug.Print vbCrLf & "Start at: " & Time
'A) Decide which sheet:
If Time > TimeSerial(15, 0, 0) Then
    Exit Sub
ElseIf Time > TimeSerial(13, 30, 0) Then
    aSheet = "GData"
ElseIf Time > TimeSerial(12, 0, 0) Then
    aSheet = "FData"
ElseIf Time > TimeSerial(10, 30, 0) Then
    aSheet = "EData"
ElseIf Time > TimeSerial(9, 30, 0) Then
    aSheet = "DData"
End If
Debug.Print "Selected sh: " & aSheet
'Transfer to "that" sheet:
With ThisWorkbook
    lastrow = .Sheets(aSheet).Cells(Rows.Count, "A").End(xlUp).Row
    .Sheets(aSheet).Cells(lastrow + 1, 1).Resize(141, 13).Value = .Sheets("CData").Range("A2:M142").Value
End With
Debug.Print "LastRow: " & lastrow
End Sub

I have added 3 "Debug.Print" that should help in debugging further error conditions
The instruction will log in the "Immediate Window" of vba, the time when the macro was run, which sheet was selected (if any), which was "lastrow" on that sheet at that time

To look at these value, open the "Immediate Window": from vba editor press Contr-g, or use Menu /View /Immediate window
For information about the Visual Basic user interface: Visual Basic user interface help

In case the macro fails, open the vba Immediate window, copy the last 10 rows of what is in that window and insert the text in your next message.

If the macro behave correctly, you may leave those "debug.print" lines in the code, or remove them; if the macro is run not quite often then I should leave them there.

Bye

 

[Vbalearner85]

OMG...
I confused Now (date + time) with Time (only time of the day)
So you should replace all those "Now" with "Time", i.e.

Sub AnyData()
Dim lastrow As Long
Dim aSheet As String
'
Debug.Print vbCrLf & "Start at: " & Time
'A) Decide which sheet:
If Time > TimeSerial(15, 0, 0) Then
    Exit Sub
ElseIf Time > TimeSerial(13, 30, 0) Then
    aSheet = "GData"
ElseIf Time > TimeSerial(12, 0, 0) Then
    aSheet = "FData"
ElseIf Time > TimeSerial(10, 30, 0) Then
    aSheet = "EData"
ElseIf Time > TimeSerial(9, 30, 0) Then
    aSheet = "DData"
End If
Debug.Print "Selected sh: " & aSheet
'Transfer to "that" sheet:
With ThisWorkbook
    lastrow = .Sheets(aSheet).Cells(Rows.Count, "A").End(xlUp).Row
    .Sheets(aSheet).Cells(lastrow + 1, 1).Resize(141, 13).Value = .Sheets("CData").Range("A2:M142").Value
End With
Debug.Print "LastRow: " & lastrow
End Sub

I have added 3 "Debug.Print" that should help in debugging further error conditions
The instruction will log in the "Immediate Window" of vba, the time when the macro was run, which sheet was selected (if any), which was "lastrow" on that sheet at that time

To look at these value, open the "Immediate Window": from vba editor press Contr-g, or use Menu /View /Immediate window
For information about the Visual Basic user interface: Visual Basic user interface help

In case the macro fails, open the vba Immediate window, copy the last 10 rows of what is in that window and insert the text in your next message.

If the macro behave correctly, you may leave those "debug.print" lines in the code, or remove them; if the macro is run not quite often then I should leave them there.

Bye

Great sir,

Working as expected!! Kudos to your efforts

 

[Anthony47]

Very well (I was not ready for a second failure )!