Copying Data from one sheet to another... issue using End(xldown) in subroutine

[joe31623]

The attached file is a crude solution to take data from one worksheet and paste it into another worksheet that I wrote for another excel forum user. To the intended recipient of the attached file, please continue our discussion here.

If anyone else cares to take a look or offer any feedback of how something may be done more efficiently, I welcome your feedback as well. For example, one potential improvement I would like to make is to not re-write over existing data. Instead of using a clever/effective use of the .End(xldown) method, I deleted data and passed a counter: debugCT into a subroutine that is called in a do-while loop. I commented out some lines of code in the following subroutine which approached that solution.

Please Login or Register  to view this content.

So I am writing data to cells: Range(Cells(8,4),Cells(8,9) during the first time the above subroutine is called and I wish to write to row 9, 10, etc. through subsequent passes. I also wish to design the code so that if there is existing data in rows 8 through N, an arbitrary row number, I begin writing data to row N + 1. Again, any feedback is welcome.

[John19]

why does it not show Monday 21 December TR data "8:00 AM TR Consumer Confidence DEC"?

it seems to start with GB data at 11.00 AM? but TR data at 8.00 AM is before - see attached file


also when you try to populate again, it goes into a loop asking "overwrite data" for every entry. can it refresh the data without asking when you populate.

Even better, remove the populate button altogether and have it automatically populate when the sheet is refreshed for external data retrieval.

Overall I'm very pleased with it, it's exactly what I was after

many thanks

PopulateTable1.xlsm

[joe31623]

Is Monday 21 December TR data "8:00 AM TR Consumer Confidence DEC" the only data you're missing?

In regards to removing the populate button, it is easy to run VBA code every time any cell is changed and I think it is easy to run VBA code any time a specific cell is changed. We wouldn't want to run the code anytime any cell is changed So I would need to know more about how the table is populated. That data is automatically downloaded from a webthesite, right? Multiple data are added at once, right?

Would formatting the table so that the borders outline the data be useful or are you going to use this to copy and paste into a master worksheet?

[John19]

yes I've just thoroughly check each entry that is the only missing one - "Monday 21 December TR 8:00 AM TR Consumer Confidence DEC"

[John19]

Yes the data is download automatically using excel's get data from web function. The site updates the data daily so I would refresh the sheet regularly, I have excel set to update external links at startup. BUT I would save excel during the day so if I started up again in the same day, it would have to refresh from the site again - so that I always have up to date information.

ONE IMPORTANT POINT ABOUT YOUR QUESTION REGARDING CELLS CHANGING: THE SITE DOES NOT CHANGE DATA DURING THE DAY, IT ONLY UPDATES DAILY. AND YES MULTIPLE DATA ARE ADDED AT ONCE DAILY.

Borders: the length of the table changes daily, but it would be good if I could have an outer border as in my file. I intend on adding colours and other formatting later e.g. internal borders.

I will be integrating this to my worksheet, to sheets called "7 Day CALENDAR" and "DATA10" - so please tell me how to do this? where do I copy codes to?

[John19]

about the populate button, I don't know much about VBA codes or how the whole thing works, but it would be good if I don't have to press anything for the data to appear in the table, because the data is already downloaded, can it not go ahead and sort it to appear in the table straight away on start-up?

[John19]

I meant to say I will integrate it into my "workbook" with 2 sheets called "7 Day CALENDAR" - containing the table and "DATA10" - contained downloaded data.

[John19]

just looked at today's data (Tuesday 22/12/215) again it's omitting the morning data for some reason:

"12:05 AM GB GfK Consumer Confidence DEC"
"09:30 AM GB Public Sector Net Borrowing NOV2

are missing

[joe31623]

Ok... I'll take a look. It will be some time before I check it out again. I'll be without a personal computer for 2-3 weeks; however, I may get to it on someone else's computer shortly after the holidays.

I expect that issue is something simple I'll take care of and then I'll go to work on the rest.

Merry Christmas, Happy Holidays or whatever means something to you.

[John19]

Happy Christmas and New Year to you also,

much appreciated,

thank you.

[John19]

hi there, any chance you can work on it now?

thanks

[joe31623]

I can revisit this in about 24 hours.

[joe31623]

John,

In order to debug the code, I need the data you're running the code on in order to figure out why it's not capturing those data points. Please post both data sets you ran the code on:
where it missed:
"12:05 AM GB GfK Consumer Confidence DEC"
"09:30 AM GB Public Sector Net Borrowing NOV2"
&
where it missed:
"Monday 21 December TR 8:00 AM TR Consumer Confidence DEC"

Joe

[John19]

Joe,

The excel document fetches the data from the site automatically (the site has fresh data daily). So just refresh the document to get the latest data and then populate the table. The error (missing data) is consistent, in other words it always fails to pick up the data at the beginning.

I just ran it now (16/01/2016) and it failed to pick up the US data for 18/01/2016 - "Martin L. King Day" - instead it started to read the calendar from 19/01/2016.

So the missing data I quoted you earlier ("12:05 AM GB GfK Consumer Confidence DEC", etc..) belonged to the day that information was published on the site (http://www.tradingeconomics.com/calendar), all you have to do to get the error again, is refresh the sheet and you will see the missing data for the current day.

The problem appears to relate to way the POPULATE function interrogates the list from the calendar that's downloaded - for some reason it ALWAYS misses the data at the beginning of the list, for US, UK and TR.

[John19]

OK I've found the one of the missing data sets for the week 21 DECEMBER 2015 - see attached excel file

Note: the first data on the calendar list should be "8:00 AM TR Consumer Confidence DEC" - BUT it's missing

missing data.xlsm

[joe31623]

Thanks... I'll take a look by the end of the week. I've learned a lot since I worked on your project.

[John19]

Hi Joe,

sorry to pester you again but any chance you could continue? you've written such a good code so far be a waste to leave it now, please please finish it.

sorry again.

thanks

[joe31623]

Sure... I've learned a lot about excel VBA since then.

Thanks for the reminder. I'll take a look tonight.

[joe31623]

John,

I took another look. You're too kind to say my previous code is good. In my opinion, this code is crap but it should do what you want. And it's fast so you will not notice any differences in speed between optimal code and what is in the attached. I re-wrote the part that almost certainly caused the macro to miss the first part of the data in my previous version, but I kept part of it that may make it difficult to follow.

Improvements to the end result in terms of formatting are possible.

Let me know if there are any issues with the attached.

[John19]

my external data connection on the sheet is not there? doesn't work - I tried to create another connection but errors occur? in your previous versions you had left the data connection intact but now it's deleted?

thanks

[joe31623]

That is because the attached file was using the old code I corrected in the last file I attached.

It requires you reference the Microsoft Scripting Runtime library because it uses a dictionary object.

I included code in the attached document that does not require you reference that library. If you copy and paste the macro in the attached, it will work. It picked up the set of data you mentioned was missing.

[joe31623]

John,

Sorry for the confusion. It looks like my last reply was a response to one of your previous posts.

I did not change any external data links (nor did I see any), so I'm not sure that is on my end. To be sure, here are the changes I made to the original .xlsm document I posted in this thread (months ago). In the attached document, I took no code away. I only added code. Though I didn't see that you had any code...

Changes:
1: I appended the name _Old to the old macro so you can distinguish between the two subroutines (all the code that was there is there now) -- this was & is located in ThisWorkbook
2: I added a new module and re-named it.
3: I added the subroutine identical to what I last posted (so you don't need to reference the Microsoft Scripting Runtime library)
4: I linked the macro button on your Data From The Web worksheet to the subroutine in the new module
5: I created a new worksheet: Copy Of Table so you can check to see the macro works as expected.

Note: the improvement I made eliminates the prompt to overwrite data.

[John19]

why does it not show Monday 21 December TR data "8:00 AM TR Consumer Confidence DEC"?

it seems to start with GB data at 11.00 AM? but TR data at 8.00 AM is before - see attached file


also when you try to populate again, it goes into a loop asking "overwrite data" for every entry. can it refresh the data without asking when you populate.

Even better, remove the populate button altogether and have it automatically populate when the sheet is refreshed for external data retrieval.

Overall I'm very pleased with it, it's exactly what I was after

many thanks

Attachment 436203

if you look at this file you sent me earlier (12-19-2015) - you can see the data connection.

But now this data connection does not exist so I can't update the table? how do I put it back?

[joe31623]

John,

Did you try the file I uploaded today at 10:44 AM? I took the same file and only added a module with the code that doesn't miss any data.

I can work on the other requests, but I want to make sure the code works properly in terms of the data connection. I don't understand the data connection you're referring to. Is it code? If so, where is it? Please describe the data connection and upload a file with a data connection.

[John19]

Joe,

yes I did look at the file you sent today.

OK do this - download and open the file above you were working on in December 2015 (Attachment 436203) - then look at DATA>Connections - you will see the external data connection to the site where I get the data from.
NOW - do the same with the file you sent today DATA>Connections - now data connection exists?

[joe31623]

Yep... I was able to get it working. Sorry, I've never worked with Data Connections.

I would take that file and add the following code in place of the existing code. If there are any issues with that, let me know. I can work to automatically refresh the data so no button is required, but don't you want add to existing data rather than overwrite it (excluding repeated data points, of course)?

I'll also want to clean up the code below to eliminate unnecessary variables, unnecessary functions, and inefficiently convoluted logic.

Please Login or Register  to view this content.

[John19]

I can't comment on the code because I don't understand it.

I WOULD WANT IT TO OVERWRITE EXISTING DATA WITH THE NEW DATA ON THE SITE - SO THAT MY EXCEL DOCUMENT ONLY CONTAINS THE EXACT DATA ON THE SITE CURRENTLY - I DON'T WANT IT HOLDING ONTO OLD DATA NOT DISPLAYED ON THE SITE.

BEST NOT TO DO AUTO REFRESH (AUTO REFRESH FOR THE ETERNAL DATA) - I CAN PRESS THE FRESH BUTTON ON EXCEL TOOLBAR WHEN I NEED TO.

BUT IT SHOULD POPULATE THE TABLE WHEN EVER NEW DATA IS RETRIEVED EXTERNALLY. I DON'T WANT A BUTTON TO POPULATE THE TABLE - THIS SHOULD BE AUTOMATIC NOT GETTING EXTERNAL DATA VIA THE DATA CONNECTION TO THE SITE.

[John19]

hi Joe,

any chance you could work on it this weekend?

thanks

[John19]

Joe, are you going to be able to complete this for me? I think maybe you don't have the time to work on it? I really need it soon.

Please let me know so I can decide whether to post it again.

thanks

[joe31623]

John,

I'm sorry if there was some confusion, but I do have it completed up to the point of your basic request. I'll give you my e-mail in a private message so you can get a hold of me more reliably.

I re-designed the portion of the code that does not miss any data and there are no issues with the external connection. All you have to do is copy-and paste the last code I posted into attachment 436203 that you referenced above. If you have any issues doing this, I'll send you a private e-mail with the working attachment.

All further work I'd like to do will be completed approximately once a week. Improvements will include: better formatting, not overwriting data, and automatically populating the table so you don't have to push a button to run the macro. I think we should abandon this thread. Please respond to my private message and we'll take this project further because it interests me and it seems like a very small effort on my part to make a substantial impact for you.

Joe

[John19]

Joe, I'm lost, what do I do with this code?

[John19]

Yep... I was able to get it working. Sorry, I've never worked with Data Connections.

I would take that file and add the following code in place of the existing code. If there are any issues with that, let me know. I can work to automatically refresh the data so no button is required, but don't you want add to existing data rather than overwrite it (excluding repeated data points, of course)?

I'll also want to clean up the code below to eliminate unnecessary variables, unnecessary functions, and inefficiently convoluted logic.

Please Login or Register  to view this content.

Joe, I'm lost, what do I do with this code?

[John19]

Joe,

yes I did look at the file you sent today.

OK do this - download and open the file above you were working on in December 2015 (Attachment 436203) - then look at DATA>Connections - you will see the external data connection to the site where I get the data from.
NOW - do the same with the file you sent today DATA>Connections - now data connection exists?

Did you manage to look at the data connection as I described above? I currently do not have a working file.

[joe31623]

John,

Yes. Please send me a private message with your e-mail and I'll send you a working file. This thread can no longer help the forum.

Joe

[joe31623]

Note that the most-recent format about how it downloads has changed so I edited the code to be able to accommodate spaces in the downloaded data.

[John19]

did you get my email address?

[joe31623]

Yep. Check your inbox.

[John19]

have a look at this screenshot of an error in the table.

Also can you do it so that the table is updated automatically when external data is refreshed?
thanks