VBA copy and paste range values every 5 minutes to next blank column in another sheet

Dear Community,


I have very little VBA experience and therefore am asking for any help I can get to write a VBA code to achieve the following:

I have two sheets.

Sheet 1 has live data being fed into it every 5 mins, comprising 7 columns and 100 rows

Sheet 2 is currently blank, but is where I want to paste special (values) copied from Sheet 1

I would like the copy and paste special (values) to occur evry 5 minutes to coincide with the update period for data being fed into Sheet 1.

However, I would like every subsequent copy and paste operation to be appended to the next blank row in Sheet 2 ie directly under the last copy and paste special (values) info and again, and again and again.......(I see excel 2016 has 1,048,576 rows so this I imagine is ok)

This could potential copy and paste for maybe 5 days so unsure also of how to keep this running.

Any help would be much appreciated.

Thank you for taking the time to read.

Try this:
To start the process, remove the "stop" from the cell ("L1") and select some other one (for example, press "Delete" and then press enter key).
Copying is started after changing data in cell 'A1'.

In 'ThisWorkbook' module:

Please Login or Register  to view this content.

In Sheet1 module:

Please Login or Register  to view this content.

But it is not very tested.

porucha vevrku,

Many thank for your assistance and apologies if i confused the matter by title the topic incorrectly. The objective is to paste to next blank row and not next blank column as title suggests.
I've have followed your code and instructions but nothing appears to happen.
I think I am doing something run because even when I go into the modules and workbook neither codes appear in list from which you can run.

I think I may be overlooking something probably quite obvious but cant find how to resolve.

Many thanks again, but do hope you're able to help me over this hump.

Kind Regards

Originally Posted by New2Code

... The objective is to paste to next blank row and not next blank column as title suggests...

These are not columns, only rows.

Please Login or Register  to view this content.

Originally Posted by New2Code

...I've have followed your code and instructions but nothing appears to happen...

Try to go through all code with the 'F8' key in the vba editor.

Hello again.
Yes understand that you solution is for pasting into new things blank rows which is what I am definitely after.
However, even after using F8 nothing is been highlighted to identify what I need to change in order to get the macros listed....I really do not know what to do from here.
Is there maybe a simplier VBA?

The word "stop" is used to deactivate the macro.
If you want to enable them, you must remove the word "stop" from the cell 'L1' and select any other cell, for example 'A1', after all, here, some of your copying mechanism will transfer data to excel.
Then wait for the data to be transferred to Excel.
After that, after about 2 seconds, the data will be sent to sheet 2.

Location of macros in modules according to the attached images: