Transfer Data From Workbook To Workbook

**Dave69rock** · 11-09-2008, 07:17 PM

Hi

Thanks to everyone that has helped in the past.

I'm after some Vb Code for windows97 That will Export Data from one Workbook to another.

Please find attached both workbooks. Both workbooks will always be in the same folder.

I have a Rota sheet that i update weekly, once completed i need to export the data to the Master Rota workbook.

I need it to find the Staff Id Number then find the correct Date and import the Data in the correct cells.

A simple Copy and Paste is not an option with the master rota sheet

Cheers Dave

**rylo** · 11-09-2008, 08:50 PM

Hi

See if this gets you started. It doesn't close down the output workbook at this stage as I want you to make sure it copies things across properly.

Please Login or Register  to view this content.

rylo

**Dave69rock** · 11-09-2008, 09:28 PM

Hi Rylo

This is looking like what i need.

At the moment it only copies the data into the first week of the Master rota If i change the dates of the rota sheet it doesn't match the dates and copy underneath.

In the rota sheet i will be using under cell F50 will have hundreds of rows of formula, so using endup will this cause problems

Dave

**rylo** · 11-09-2008, 10:14 PM

Dave

Silly me - forgot to find things in the right sheet.

try this

Please Login or Register  to view this content.

rylo

**Dave69rock** · 11-09-2008, 10:28 PM

Hi

This is looking great will need to run some more test but so far i'm having no problems.

Dave

**Dave69rock** · 11-09-2008, 11:34 PM

Thanks for your help so Far

Because i have Data under Row F50 on the rota sheet(not the example sheet i posted) i keep getting an error its todo with the End up statement but i'm not sure how to set the range.

Dave

**rylo** · 11-09-2008, 11:47 PM

Dave

I see what you mean now.

Change the for each row to

Please Login or Register  to view this content.

It does assume that there will be a break between the last of the staff ids and any other data...

rylo

**Dave69rock** · 11-10-2008, 12:06 AM

Hi

This is now working so Far

Thanks for your help

Dave

**Dave69rock** · 11-10-2008, 12:15 AM

Hi again

There will be some blank Rows in between some of the ID Numbers in the rota sheet so i'm not sure that end up or end down will be ok.

Dave

**rylo** · 11-10-2008, 12:31 AM

Dave

So could you use the fixed range F6:F49 and only action non blank cells????

rylo

**Dave69rock** · 11-10-2008, 12:42 AM

Hi

Yes you could you use the fixed range F6:F49 and only action non blank cells

Dave

**rylo** · 11-10-2008, 12:57 AM

Dave

Here goes..

Please Login or Register  to view this content.

rylo

**Dave69rock** · 11-10-2008, 01:47 AM

many thanks this works really well.

I have just spotted a floor in the Master rota Sheet, as this is not my sheet and is produced elsewhere. the date is formatted as text which i wasn't aware of at first very sorry??? so cell G2 is "6" G3 "Oct" G4 is "Mon" i can change my Rota sheet dates to text but can i still copy the data between the two sheets with out changing the code too much.

Sorry to be a pain
Dave

**Dave69rock** · 11-10-2008, 09:41 AM

Hi

Please find attached the sheets of where I'm up to now.

Now i know the dates are formatted as text, is there a way i can still copy from one sheet to the other.Dave

**rylo** · 11-10-2008, 06:56 PM

Dave

I don't know if it was your experimenting or not, but the 7 in rota was not the same as the 7 in Master. The other dates were OK, but that one was not. Once I made them the same then

Please Login or Register  to view this content.

seemed to run OK.

If the 7 was correct on both items, then you may have to make sure that both sheets have the days as numeric just to be sure. If you make sure the master was numeric, then it wouldn't be too hard to enhance the code to do the conversion before processing.

rylo

rylo

**Dave69rock** · 11-10-2008, 08:39 PM

Hi

thanks for your time this seems ok at the moment, and yes the master will always be numeric and i can make sure the Rota will be aswell.

I will do some tests with this but its looking pretty much like what i need.

Dave

**Dave69rock** · 11-10-2008, 08:51 PM

I would like to keep the date format in the Rota Sheet instead of text but if thats a problem then thats ok.

Dave

**rylo** · 11-10-2008, 09:03 PM

Dave

Not sure from the last 2 posts if you want anything further or not......

rylo

**Dave69rock** · 11-10-2008, 09:22 PM

Hi

If i try to export from the rota the MY master sheet i get and error from vb. its to do with the dates because if i copy the cells with the dates from the rota to the master the export it works ok.

When i checked the cell format in MY master sheet it is set as general.
Dave

**Dave69rock** · 11-10-2008, 09:31 PM

Many Thanks

Its working ok, silly me i was coping the format the wrong way round now it works fine Thank you for your time.

Dave

**rylo** · 11-10-2008, 09:33 PM

Dave

Example files please. I'm not sure which structure you are talking about (dates or day / month on separate rows) and if it is only the one cell that is a problem (as per the last example set) or a generic problem.

rylo

**Dave69rock** · 11-10-2008, 09:48 PM

Hi

Its working very well at the moment the problem i was having was the master sheet format was set at general and mine was set at Text.

Dave

**Dave69rock** · 11-15-2008, 03:30 AM

Hi Rylo
i'm having some problems with the code you did for me.

When i run the code at work on windows97 i'm getting and error and the Dates in the master rota that it matches are deleted. it does however copy all the times to the correct cells.

you also said that if the dates in the master rota were numeric, then it wouldn't be too hard to enhance the code to do the conversion before processing.

The format of the date cells in master are set as general.

Can you still help please

Dave

**rylo** · 11-15-2008, 07:02 PM

Dave

In your macro, once you have opened rota.xls and moved to the sheet rota then insert this code

Please Login or Register  to view this content.

It will format the cells to general, and convert to numeric.

rylo

**Dave69rock** · 11-16-2008, 02:48 AM

Hi Rylo

At the moment i have both sheets set as general and it all works ok i will insert this code after i have sorted the problem below out.

When i run this code on my pc which runs on windows 2000 all is fine, however
when i run it at work which runs on windows97 i get an error and it highlights the code I've put in red. and also deletes the dates from the master rota sheet. i think the dates are deleted because the macro hasn't completed its task

Please Login or Register  to view this content.

End Sub

Any idea's
Dave

**Dave69rock** · 11-16-2008, 03:32 AM

I found this about using Evaluate in windows97

http://support.microsoft.com/kb/168238

**rylo** · 11-16-2008, 06:28 PM

Hi

Try converting the formula to be valid in the spreadsheet, then put it into a cell somewhere that is not going to impact things.

You can then get the result of the formula, and put it to the variable outcol. I'm guessing that is where the problem lies. Once you have the result in the variable, you can delete the formula from the cell.

See how that goes. If it works, then it should be valid for both versions.

rylo

**Dave69rock** · 11-16-2008, 10:28 PM

Hi

I'm sure for someone with your knowledge that would be easy.

I have no idea where to start.

Any help would be great

Dave

**rylo** · 11-17-2008, 12:07 AM

Dave

This uses rota!A1 as its action cell.

change the line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

And immediately before the end sub add the line

Please Login or Register  to view this content.

rylo

**Dave69rock** · 11-18-2008, 02:02 AM

Hi Rylo

Thanks for your time on this

Its All starting to work now apart from one strange thing happening.

After i run the code everything does what its supposed to do, except when you go back into the master rota the Dates have been deleted from the second row e.g H2:n2 cells its only the week it matches up with.

Maybe this is a windows 97 problem, if so is there a way to copy the 7 dates back that it deleted.

It all works great on window 2000

Dave

**rylo** · 11-18-2008, 02:08 AM

Dave

I don't have 97 so can't replicate the problem. Can you step through the code and find which line is causing the date to be deleted?

rylo

**Dave69rock** · 11-18-2008, 02:29 AM

I protected the cells in the master rota and debugged it when it tried to delete.

This is the line it was on

Please Login or Register  to view this content.

Dave

**Dave69rock** · 11-18-2008, 02:46 AM

Oh one more thing i noticed

The sumproduct formula in windows 97 is giving a 0 total whereas in 2000 it gives a total of 14,21,28,35 Ect:

**Dave69rock** · 11-19-2008, 02:06 AM

Hi Rylo

I think i have got it to work

If the Master rota is protected when it tries to delete the dates and goes to run time error because they are protected if you click end it works so this is what i did let me know if you think it will cause problems later.

Please Login or Register  to view this content.

in red is what i've added and its working up to now.

I won't mark this thread as solved just yet, but many thanks for you help

Dave

**rylo** · 11-20-2008, 12:08 AM

Dave

If outsh is protected, then how do you expect to write the output to it???

rylo

**Dave69rock** · 11-20-2008, 12:27 AM

the only cells in the outsh that are protected are the date cells it still writes the data across to the correct place it just doesn't delete the date.

Dave

**rylo** · 11-20-2008, 12:42 AM

HI

OK - if that works, then keep going. Can't see that it would do any harm anywhere.

rylo

Transfer Data From Workbook To Workbook

LinkBack

Thread Tools

Rate This Thread

Display

Transfer Data From Workbook To Workbook

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions