Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Macro to copy paste data

0

Hi Don

I added "Public interval As Variant" below "Option Explicit" as adviced in macro of module "Module1" of Sheet "Change" of file "Analysis.xlsm"

Then added the 2nd updated macro in another module "Module2" of Sheet1(Sheet1) of file "Analysis.xlsm"

The 1st macro of module "Module1" is working perfectly once the "StartTimer" command is selected. 

However, data from range G12:G25 of Sheet "WorkArea" of file "MarketWatch.xls" STILL DOESN'T appear on range A1:A14 of Sheet "Sheet1" of file "Analysis.xlsm" as required every five minutes.

I even tried the updated macro on a new sample file instead of file "Analysis.xlsm" but it still doesn't gave the required output of copy paste. Hence, I suspect something is still wrong or missing in the updated macro.

I have again attached both the files i.e. file."Analysis.xlsm" and file "MarketWatch.xls" for your reference and would appreciate if you can analyse why isn't the macro working.

Please note:

File "MarketWatch" is a streaming stock watchlist and it stops streaming if it is edited

Thanks

Viral

excelmacros-vba
MS Office Version: 2007
posted by: ViralGor (rep: 8) on Mon Jul 25, 2016 at 12:26 pm
Answer
Discuss

Discussion

Please type the used ranges and the sheets.
OR attach a sample file
MRVMV (rep: 52) Jul 26, '16 at 8:10 am
Not sure how to attach sample file.
It is a macro enabled file.
I want to copy Range V6:V19 and X6:X19 of Sheet1 of this file and paste it to another workbook every five minutes.
ViralGor (rep: 8) Jul 28, '16 at 11:50 am
Add to Discussion

Answers

0
Selected Answer

Try this:

(updated)

Sub timer_copy_data()

    interval = Now + TimeValue("00:05:00")

    Application.ScreenUpdating = False

    'Use the full path of the file to open it, including the files extension.
    'file where you want to get the data from
    Set Wb1 = Workbooks.Open("C:\sample_1.xlsx")
    'file where you want the data to go
    Set Wb2 = Workbooks.Open("C:\sample_2.xlsx")

    'Do stuff with the files
    'Wb1 and Wb2 references the workbook and the rest is regular syntax for doing stuff in the workbook
    'Reference them like this:
    Wb2.Sheets("Sheet2").Range("V6:V19").Value = Wb1.Sheets("Sheet1").Range("V6:V19").Value

    'When you are done, close the files.
    'False means don't save changes. Change to true if you want.
    'Do this for each workbook you want to close.
    Wb1.Close SaveChanges:=False
    Wb2.Close SaveChanges:=False

    Application.ScreenUpdating = True

    Application.OnTime interval, "timer_copy_data"

End Sub

Use this on SAMPLE DATA first! 

posted by: don (rep: 1989) on Sat Jul 30, 2016 at 4:13 pm
Discuss

Discussion

Hi Don

Thank you for the code.
However, it doesn't work.
It doesn't do the required action of copy paste.
On the contrary it is creating certain issues even with other macro enabled sheets.
I would appreciate if you suggest how to upload sample file in this discussion.
I would like to forward sample file for your better understanding.

Thanks

Viral
ViralGor (rep: 8) Aug 3, '16 at 11:49 am
This is the copy/paste line:
Wb2.Sheets("Sheet2").Range("V6:V19").Value = Wb1.Sheets("Sheet1").Range("V6:V19").Value

It takes the values from one range and puts them into the new range in the other workbook.

If you want to upload a sample file, edit your question and on the edit page you can upload one.
don (rep: 1989) Aug 3, '16 at 11:57 am
Hello

I hope you have received the 2 sample files uploaded by me on 25/07
A confirmation would be appreciated :)

Thanks

Viral
ViralGor (rep: 8) Aug 16, '16 at 8:17 am
Didn't see it. I will look now.
don (rep: 1989) Aug 16, '16 at 12:44 pm
Remove Option Explicit from module 1 in Analysis.xlsm or add Public interval As Variant under it. The timer will then work. Also, I made one small change to the macro above that might have caused an issue. Try the new macro out now along with making one of the above changes.
don (rep: 1989) Aug 16, '16 at 1:10 pm
Hi Don

It still doesn't work.
Please look into it at your convenience.
I have uploaded the files again for trial of the updated macro.

Thanks 

VIral
ViralGor (rep: 8) Aug 18, '16 at 9:11 am
What part doesn't work? Did you try removing the Option Explicit or just declaring the variable interval in the macro? You need to give more info than "it still doesn't work".
don (rep: 1989) Aug 18, '16 at 12:53 pm
Hi Again

Yes, I did remove Option Explicit from module 1 in Analysis.xlsm and realised that after doing it the "StopTimer" command doesn't work and the macro keeps doing its job even after the button is hit. Hence I went ahead with the second option of adding Public interval As Variant under it and it started acting on both "StartTimer" and "StopTimer" command buttons whenever thet are hit.

I then added the updated macro to module "Module2" of Sheet "Sheet1" of file "Analysis.xlsm" and found that the data from file "MarketWatch.xls" is not getting pasted at the required range of "Sheet1" of file "Analysis.xlsm".

I even tried running the macro manually from the option "View" - "Macros" - "View Macros" - "Run" but still couldn't find the data for which the macro is made.

I have therefore uploaded both the files so that you can give it a try and investigate what's wrong with the macro.

Thanks as always

Viral
ViralGor (rep: 8) Aug 19, '16 at 10:27 am
Hi Don

Could you find some time to look into this issue.
I tried it several times by saving in the code in different sample files but the values are still not reflecting in the destination file.

Thanks

Viral 
ViralGor (rep: 8) Aug 24, '16 at 11:50 am
It sounds to me like there is a conflict between the timers. From what I can gather, Excel is having difficulty running multiple timers in the worksheet as macros. I think you should remove the timers and just leave one timer in place and have every macro that you want to run at that interval be called from that macro. That should fix your timer issue but it won't work like you want since it seems like one timer is to be turned off/on and the other is to be left running.

As such, I think that you need to change how you are approaching this problem. First, Excel is probably not what you really want for this. Second, if you really want to use Excel, get the copy/paste part of the macro working and then try to implement the timer.

If this is a mission-critical process, using a timer in Excel to have it run is a bad idea because, as you are now learning, Excel has issues with timers.
don (rep: 1989) Aug 24, '16 at 7:55 pm
Hi Don

I know I'm troubling you a lot.
Just wanted to check with you, can this problem be because I am working on MS Excel 2007 ?
Is this code compatible with MS Excel 2007 ?
If yes, could you please confirm whether the below mentioned procedure is correct way:

1] Open MS Excel file
2] Alt F11
3] VBA Project
4] Microsoft Excel Objects
5] Right click on Sheet1 (Sheet1)
6] Insert Module
7] Paste the code
8] Save and close the file

After I again open it, I should enable macro and it should start copying values from the source file automatically at specified intervals mentioned in the code.

Or do I need to change anything ?

Thanks

Viral
ViralGor (rep: 8) Aug 25, '16 at 11:30 am
That is the correct process.
'Use the full path of the file to open it, including the files extension.
    'file where you want to get the data from
    Set Wb1 = Workbooks.Open("C:\sample_1.xlsx")
    'file where you want the data to go
    Set Wb2 = Workbooks.Open("C:\sample_2.xlsx")
 
    'Do stuff with the files
    'Wb1 and Wb2 references the workbook and the rest is regular syntax for doing stuff in the workbook
    'Reference them like this:
    Wb2.Sheets("Sheet2").Range("V6:V19").Value = Wb1.Sheets("Sheet1").Range("V6:V19").Value

The comments in the code should help you but you basically need to tell the macro which files you are copying the data between and then which ranges.

Get this macro by itself working in its own file and then try to integrate everything else. When it comes to programming, do things in small pieces and slowly add everything else so you can make sure it works together nicely.
don (rep: 1989) Aug 25, '16 at 12:01 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login