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

Resolved · Low Priority · Version 2007

Fri 30 Oct 2015, 12:42 Delegate Chris said...

Chris has attended:
Excel Advanced - Formulas & Functions course

Cut and paste data from one workbook to another

Hello,

First things first I am new to using macros so can you explain things as if you were talking to a child.

I am writing a macro that will cut data out of one workbook and paste it into another. I have had a look on Google and I have found that you can add into the macro to perform this at a certain time and also to paste the extracted data into the next available blank cell.

Here is the macro that I am using...

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").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.WindowState = xlMinimized
Windows("Histo sample data.xlsm").Activate
ActiveWindow.WindowState = xlMaximized
ActiveWindow.Close

How do i write to perform this at a certain time of the day and to paste the cut data into the next available empty cell? Also where would i need to insert these new rules into the existing macros?

Thanks,

Chris.

Fri 30 Oct 2015, 13:37 Trainer Jens said...

RE: Cut and paste data from one workbook to another

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

Fri 30 Oct 2015, 14:23 Delegate Chris said...

RE: Cut and paste data from one workbook to another

Thanks for that Jens, I will update my macro and try out your suggestion.

Just one more question... I will be running this at work and we operate using a shared drive, if i set the macro to cut and paste at a specific time of day does it matter if the document is open or not? Will it run while my computer is shutdown and then update when I start-up the computer?

(sorry that was actually 2 questions and i said just one more)

Thanks,

Chris.

Fri 30 Oct 2015, 16:14 Trainer Jens said...

RE: Cut and paste data from one workbook to another

Hi Chris,

The workbook must stay open. In the code you refer to ActiveWorkBook, ActiveWindow and ActiveSheet. It tells Excel that you want all this to happen in the ActiveWorkBook (the workbook you have open and work in).

Anyway Excel must be open to run macros. If you want to run the code at 10am the timed event must be executed before 10am and the Workbook must be open and active (You cannot work in another workbook at 10am. Then it will be the ActiveWorkBook and you will paste the data in it).

I realised that the code I wrote in my first answer is not working if there is only one row in the destination worksheet. Have a look at the code below:

Sub DestData()

Workbooks.Open ("C:\Users\jens\Desktop\mybook.xlsx")

Sheets(1).Range(Range("b4"), Range("b4").End(xlToRight).End(xlDown)).Copy

ThisWorkbook.Sheets(1).Activate

If Range("b4").Offset(1, 0).Value = "" Then
Range("b4").Offset(1, 0).Select
Else
Range("b4").End(xlDown).Offset(1, 0).Select
End If

ActiveSheet.Paste
Application.CutCopyMode = False

End Sub

I have been working in my own workbooks. Instead of the line:

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

I have changed it to:

If Range("b4").Offset(1, 0).Value = "" Then
Range("b4").Offset(1, 0).Select
Else
Range("b4").End(xlDown).Offset(1, 0).Select
End If

Then you will not get problems if you only have one row in the destination table. I am also doing a bit different from your example. I am telling Excel which Worksheet I want to copy and paste from and to. It is a good idea to specify this to make sure Excel do it correct.

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

Fri 6 Nov 2015: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

Manually rotating a 3D chart

To manually rotate a 3D chart;
Select the chart (edit mode) and click on one corner of the chart display area. A selection border will appear, using the left mouse button on one of the chart's "corners" (mouse pointer changes to a cross) drag the image which becomes a line representation of the chart (as you begin to drag) and rotate the chart to a view of your choice.
If you dont get it right first time, dont worry, the manoeuver is a little bit tricky.

View all Excel hints and tips