Sheet to sheet activation coding needed

HI
I am trying to set up vba as part of a macro that will do the following:
Open a master workbook
Open a data workbook( There will be a few of these to process)
Copy the data from a data workbook sheet (INPUT SHEET) to INPUT SHEET in the master workbook.
I will rename the master and then continue with the same process for the next data workbook.
I cannot copy/move the input sheet because of an MS bug whereby all of the sheet coding is lost whe you insert a sheet into a workbook.

I have set up a sheet in the master workbook that lists all of the data workbook names and paths I set the data workbooks up with a string variable name of TRGT.

I can get the coding to open the TRGT workbook but am having trouble getting the subsequent sheet commands to work in order to extract the data.

Here is a snippet of the code:

Please Login or Register  to view this content.

From here I want to open the data workbook sheet, select a range of data using sells reference, move the data to the master workbook sheet using the cells reference … then repeat the process with the next range of data. Etc. etc

I need the “open sheet”( activate, select…) text to flip back and forth between the workbooks.

Thanks in advance for your help..

God Bless America !

hi MrLucky,

I cannot copy/move the input sheet because of an MS bug whereby all of the sheet coding is lost whe you insert a sheet into a workbook.

It is good to provide more background to comments like the above by linking to your previous post (even if unresolved) ie
http://www.excelforum.com/excel-prog...iable-bug.html

I need the “open sheet”( activate, select…) text to flip back and forth between the workbooks.

Usually it is not necessary to "activate" or "select" the different workbooks when coding. I suggest you record a macro of your manual actions for one example (compare to my below example*) & then post back with your recorded code if you have further questions...
*As I put this example together I got a bit carried away & it may even work for you "as is".

Please Login or Register  to view this content.

btw, the below code declares the "TRGT" as a variant because the "as string" only relates to the variable immediately before the "as" (this is the same for the MM declaration).

Please Login or Register  to view this content.

hth
Rob

That bug's been corrected for a long time. Are you really still using Excel 97?

Hi Rob

Thank you so much for your work on this, your info was much more than I was expecting...

I have working code to copy the data from one input sheet to another input sheet in the same work book.

Question: Can I substitute these lines to flip back and forth tween the target and the master workbooks, once they are both open? and if so.. how do i define the sheet description since they are both called Input Sheet in each of the workbooks.

Please Login or Register  to view this content.

Thanks again.

God Bless America !

hi,

Roy, Ive answered the question as the code tags have been added.

Mr Lucky,
(edit: I'm not sure who has marked this post as solved, but it is best not to ask another question within a post that is marked as "solved" because you may get no answers.)

There is usually no need to "flip back & forth between 2 workbooks" as this is normally unnecessary (unless you are manually selecting ranges within your code?) & slows the whole macro down.

Anyway, have a closer look at my code & you can see the beginnings of an answer to your question there:

Please Login or Register  to view this content.

In other words, if the declaration of the worksheet variable explicitly includes the relevant workbook name everything will be fine. An example of this without using an variables is:

Please Login or Register  to view this content.

hth
Rob

hi,

I think it will be easier for us to help you if we can see the entire code. Can you please attach a zipped example of the Master file with the code & a "target file" that is being copied from?
It may also help us suggest some changes to your sheet layout that may prevent the need for the use of "step 2" & allow a single paste.

Shg,
I haven't come across the sheet code issue before, since he's using Excel 2000, are there any work-arounds/fixes he can use?
Also, is it possible to "unsolve" a thread (this one may keep going for a while)?

Rob