Cut and paste data from one workbook to another | Excel forum

Hi Chris,

Thank you for the forum question.


First how to do it a certain time of the day:

We can use timed events in VBA but you will need to execute the event. It can be done with a macro or it could be a Open Workbook event.

Let us imaging that your macro is called CopyPaste.

If you in the visual basic editor in a module type:

Sub RunMyMacro()

Application.OnTime TimeValue("10:00:00"),"CopyPaste"

End Sub

If you run the RunMyMacro macro the macro will run the CopyPaste macro exactly 10 am.

If you want automatically to run the timed event when you open up your workbook, you can create a Open Workbook event. In the visual basic editor double click ThisWorkBook in the project explorer (the top left window where you can find the modules). This will open up a private module for the workbook. Type in the private module:

Private Sub Workbook_Open()
Application.OnTime TimeValue("10:00:00"),"CopyPaste"
End Sub

How to paste the cut data into the next available empty cell:

If your destination table's first cell is B4 then you can use the line below.

Range("B4").End(xlDown).Offset(1, 0).Select

I have amended your macro here:

Workbooks.Open Filename:= _
"\\SV-PR-FS05\shrGSTS$\Finance\Service Improvement Team\Projects\Histo sample data.xlsm" _
, UpdateLinks:=0
Range("B4").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
ActiveWindow.WindowState = xlMinimized

Range("B4").End(xlDown).Offset(1, 0).Select

ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.WindowState = xlMinimized
Windows("Histo sample data.xlsm").Activate
ActiveWindow.WindowState = xlMaximized
ActiveWindow.Close

I hope this make sense. Please tell me if not.

Good luck with the magic you can do with Excel VBA.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector