How do I return to the last cell I was in prior to VBA Code Running?

[Randy11]

Am messing around with Timed reporting in a workbook. I.E. every half an hour vba code runs to collect current volumes processed in a workbook. The issue I have is trying to figure out how I can have the code to run & either one of two solutions 1) Stay in the current cell when Code runs. 2) Return to the cell when code run is complete.

In Private Sub workbook Open have the following code (sample)
Application.OnTime TimeValue("06:00:00"), "Volume_Update1"
Application.OnTime TimeValue("06:30:00"), "Volume_Update2"
Application.OnTime TimeValue("07:00:00"), "Volume_Update3"

This calls the Volume_Update1 at 6:00 and on to the next time event. Below is a sample of this...

Sub Volume_Update1()

'Dim PrevCell As Range - Need help with this Previous Cell code....
'Set PrevCell = ActiveCell

'ActiveSheet.Protect UserInterFaceOnly:=True

Application.ScreenUpdating = False
Sheets("TrackSheet").Select
Range("K1").Select
Selection.Copy
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'PrevCell.Select -
Application.ScreenUpdating = True

End Sub

Help appreciated

 

[mintjulep]

Don't use .select

There is no need, and that is what moves the active cell around.

Change

  Sheets("TrackSheet").Select
        Range("K1").Select
            Selection.Copy
        Range("B3").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

to

With Sheets("TrackSheet")
.Range("B3").value = .Range("K1").value

End with

 

[Randy11]

This works great, thank you. Now have another small issue.
Want to do the same thing where I am transferring date from TrackSheet to ProdSheet into a appended list.

So ProdSheet has 6 column headers:
Current Date: Start Time: Close Time: Net Time: Final Volume: Productivity:

Each day when this workbook is closed, want to append the info from TrackSheet cells as follows:
A33 = Current Date etc etc.. to F3 = Productivity
So ProdSheet will accumulate daily prod totals ongoing.

Help Appreciated.

 

[SkipVought]

hi,

Do you mean F3 or F33 as in the same row as the A33?

If not, why not?

Do you mean it is ALWAYS row 33, or in THIS instance it is 33 because the last row of the table is 32 or 33?

What about the other columns of the table?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Randy11]

Thanks for pointing out my blips above Skip.
Range of data am trying to appended to list on ProdSheet tab is on TrackSheet A33:F33.
ProdSheet tab has labels in row 1. So need to start appending daily data below that. So 1st days data would land in A2:F2 on ProdSheet etc...
Thanks Again

 

[SkipVought]

What is the Next row to paste?

am transferring date from TrackSheet to ProdSheet

Are you ONLY "copying" ONE value???

    dim rng as range

    with Sheets("ProdSheet")
       if .Range("B4") = "" then
          set rng = .range("B3").end(xldown).offset(1)
       else
          set rng = .range("B4")
       end if
       rng.value = Sheets("TrackSheet").Range("K1")
    end with

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Randy11]

On ProdSheet want to start collecting the data in row 2, next time to row 3 etc. i.e look for next available row and place values.
Range on Tracksheet where info is, A33:F33

 

[Randy11]

Apologies, Transferring Data, not Date.
Range on Tracksheet where info is, A33:F33
Append to ProdSheet, start collecting the data in row 2 (A2:F2), next time to row 3 (A3:F3) etc. i.e look for next available row and place values.

Thank you...

 

[SkipVought]

Range on Tracksheet where info is, A33:F33

So you are saying that it is ALWAYS in row 33. Never ANYWHERE else.

Assuming that ROW 1 is the headings and propert table structuring...

    Dim rng As Range

    With Sheets("ProdSheet")
        Set rng = .Cells(.[A1].CurrentRegion.Rows.Count + 1, 1)
       
        Sheets("TrackSheet").Range("A33:F33").Copy
        rng.PasteSpecial xlPasteValues
    End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}