If using macro to open up one xls to copy paste entire sheet over. It messes up dates

**rain4u** · 03-17-2011, 04:25 AM

Hi
I have to work with some spreatsheets that are generated by the system that we use at work. I would like to use a spreadsheet (lets say test.xls) and use a macro to open window that prompts me to choose xls file (lets call this test2.xls), then the macro should copy entire sheet (from test2.xls) and paste it back the original spreadsheet (test.xls - sheet "test"). I have a code to do this action. this is what I have:

Please Login or Register  to view this content.

The only problem is that the some of the dates that are stored from column M to column O get all messed up. Quite randomly to be honest. Some of the dates are recognised as text others as dates (or numbers). The ones that are recognised as dates or numbers rather than text are always aligned to right. Now these ones will swap over the month and the date like this - if its 31/03/2011 then it turns into 03/31/2011. The ones that are recognised as text remain the same ofcourse. I have had similar problems before but have managed to work around. Does anyone have an idea how to fix this. I would really like to copy info over this way. I have attached both example spreadsheets.

Any help would be greately appreciated

Cheers
Rain

**snb** · 03-17-2011, 04:34 AM

try:

Please Login or Register  to view this content.

**royUK** · 03-17-2011, 04:42 AM

Maybe this

Please Login or Register  to view this content.

**rain4u** · 03-19-2011, 02:16 PM

I have to apologize first for not attaching the spreadsheets. As I did the posting at my workplace I rushed a bit. I no longer have access to those two specific example files but no worries I can quickly create a new ones. And this time I will name them properly otherwise its impossible for me to understand any offered codes. So the main file bears name YM26_converer and the system generated xls spreadsheet report bears name YM26. So after I have opened the file YM26_converter I would like to run macro to be prompted to select that system generated YM26 and then when selecting the file it should copy the entire active sheet to paste it to sheet called YM26_download. Alternatively it does not have to prompt with the window to select that YM26 file if it just copies the information over from that YM26 file that is in same folder as that YM26_converter. I aim to always call that report YM26. I would be happy with anything that will successfully copy over the info and keep the dates in intact.

Originally Posted by snb

try:

Please Login or Register  to view this content.

With this one, the code is all in red letters when pasted to VBA and it prompts with compile error

Please Login or Register  to view this content.

Though its missing somethingI have changed it for the time being and it is included with the newly attached files. Its currently like this:

Please Login or Register  to view this content.

With Roy's offered code I tried to change it to make it work with the new file names and sheet names but i get something wrong and it wont work. I have left it as it is on the attached spreadsheet.

Can I ask either of yours help again

**royUK** · 03-19-2011, 02:43 PM

This works for me

Please Login or Register  to view this content.

snb's code is missing a closing bracket

Please Login or Register  to view this content.

**rain4u** · 03-27-2011, 08:17 AM

Please Login or Register  to view this content.

Hi guys
I'm still having a problem with this Roy's offered macro. The code does copy over the information but it still gets the dates wrong. Please see my attachment. I have added this macro to xls spreadsheet. If you compare the column M on the source spreadsheet and on the destination spreadsheet you will see the problem. For an example take a look at M1 in source spreadsheet. It reads 18/05/2010, now when it is being transfered over to the destination sheet, then M1 reads 5/18/2010. The same problem applies to any cells that contain dates. Can some one run this macro and confirm it produces the same result. This is driving me nuts. Any help to remedy the problem would be very appreciated.

Cheers
Rain

**royUK** · 03-27-2011, 08:40 AM

The dates look identical to me in both the original and the imported sheets

**rain4u** · 03-27-2011, 09:03 AM

Originally Posted by royUK

The dates look identical to me in both the original and the imported sheets

I reckon this could be because of my home PC. My Windows language and region was first setup as Estonia and I have changed it now. Because I swear i didn't have the problem at work PC. Also at work we have office 2003 while I have 2002. I will try to install 2003 and see if it makes the difference. Need to dig out my **'s as I know I have it somewhere. Thank you for confirming that the macro works properly.

EDIT:

Problem solved. With Office 2003 everything is working fine! Thank you so much for the support!!!!

If using macro to open up one xls to copy paste entire sheet over. It messes up dates

LinkBack

Thread Tools

Rate This Thread

Display

If using macro to open up one xls to copy paste entire sheet over. It messes up dates

Re: If using macro to open up one xls to copy paste entire sheet over. It messes up d

Re: If using macro to open up one xls to copy paste entire sheet over. It messes up d

Re: If using macro to open up one xls to copy paste entire sheet over. It messes up d

Re: If using macro to open up one xls to copy paste entire sheet over. It messes up d

Re: If using macro to open up one xls to copy paste entire sheet over. It messes up d

Re: If using macro to open up one xls to copy paste entire sheet over. It messes up d

Re: If using macro to open up one xls to copy paste entire sheet over. It messes up d

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions