transfer data from open workbook to closed workbook

tried recording a macro across the two workbooks to no avail
all help gratefully received

I think the easiest way will be to open the second workbook. This can be done unobtrusively.

hi stephen
i was hoping this would possible without as other users will be using
maybe the macro could code to open the workbook automatically and then shut and save at same time?

hi stephen as im not very au fait with code
i dont really know where to start

I find that hard to believe Steve, some of the stuff here must have rubbed off! In effect, instead of

Please Login or Register  to view this content.

you need

Please Login or Register  to view this content.

Does that make sense?

thanks stephen
will take a look when i get home
just hope i can work it out
thanks

hi stephen
i have this code

Please Login or Register  to view this content.

and i get an error when i run this

microsoft visual basic
400

any ideas?

Hello stevesunfold,

On which line does the error occur? You will get an faster answer if you include where the error occurs.

Sincerely,
Leith Ross

doesnt actually highlight a line leith
thanks for the reply

Hello stevesunfold,

Are you using a Mac?

Sincerely,
Leith Ross

no leith
windows xp
office 2007

Hello stevesunfold,

Ah, I don't have Excel 2007. Seems odd that would have changed VBA to not highlight the line where the error occurred, but stranger things have happened.

Sincerely,
Leith Ross

just did a quick record macro version and it came up with this

Please Login or Register  to view this content.

as you can see its very tatty but it works up to the point of closing the workbook goodbye where it says it already exists do you want to save

i want this to be yes

as you can see i only did the first 3 cell transfer instead of 6 to see what the code would be like

im happy for it to open as long as it closes itself

sorry guys bumping again because i think someone out there knows and is kind enough to help

I don't see how this would explain your error message, but I made a couple of basic errors in my previous code - the copying was the wrong way round. See if this makes any difference:

Please Login or Register  to view this content.

hi stephen
many thanks for your response

getting a message box coming up with
microsoft visual basic
400

I'm losing my marbles - the code was originally OK and my last correction was wrong as I got confused about where you were copying to/from. The code below works for me - adds the data in goodbye and leaves it intact in hello. I'm using xl2003 but have the file conversion thing, I don't know whether that makes a difference. Try the code below in a standard module in hello.

Please Login or Register  to view this content.

Stephen
that is absolutely superb and works the only thing i did was tell you the destinations cells were wrong.
it should have been a1,b1,c1,d1,e1,f1 but i worked that out
thanks very very much
how is the code adapted so that the next time i run it it will go to the next available row?
ie the next time i click it will go to a2,b2,c2,d2,e2,f2

many thanks

Steve - glad we got there in the end. Try amending relevant bit to:

Please Login or Register  to view this content.

stephen
absolutely superb
i cannot thank you enough
is it possible for an explanation of how this code works~?
if not i realise im pushing my luck

many thanks
steve

See if this helps. Excel Help is pretty good, and don't forget Google.

Please Login or Register  to view this content.

superb stephen
many thanks
i will try take this on board
its a lot better when its explained
thank you very very much
regards