MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Activate Workbook by using a variable

  Asked By: Maddison    Date: Nov 13    Category: MS Office    Views: 4413

I am trying to activate a workbook using a variable in place of the
file name.

(I am pulling data from one form to create a series of new forms. I
need to pull data from form 1, open form 2 and add the data, return to
form 1, pull a new set of data and, reopen a blank for 2 and add data
and so on).

I am able to do this once, but need to make form 1 active again,
without relying on a static file name.

Any thoughts?



4 Answers Found

Answer #1    Answered By: Dot net Sachin     Answered On: Nov 13

I think you're using confusing terminology.
Are you using "userforms" or are you simply moving
data from one spreadsheet to another?

Next, where do your macros reside?

It sounds like you're trying to take data  from one file  and copy it
to several other files.

Let's say you have a file that contains your macros, and it is in a
file called "macrofile.xls".
Now, this macro opens a file called "formdata1.xls"
Once open, your macro can contain the line:
FileName1 = activeworkbook.name
SheetName1 = Activesheet.name
Then, you open  file #2 called "formdata2.xls"

your macro says:
workbook(activeworkbook.name).sheets(activesheet).cells(1,1) = _

Doing this does not require you to "make form  1 active  again".

If, for some reason, you DO need to make the different workbooks "active",
you simply have to use something like:


but I don't think you'll need that.

At first glance, the terminology you're using sounds like you're pulling  data
from a Userform in one file to dynamically create  a userform in another file.
which, can be done (I think) but the rest of your question leads me to believe
that you're just moving data from one spreadsheet to another.
Which is MUCH simpler.

If you give me some sample data, I'd be glad to throw something together for

Answer #2    Answered By: Renee Lane     Answered On: Nov 13

ok maybe i'm just confused.. your subject line says workbook.. and sounds like
your either switching workbooks or worksheets.. but your saying form... if
it's a form.. where is the form  1 getting its information and then your
dynamically creating form 2 ... i'm lost why you would be doing this from forms
instead of where your getting the data  from.

if your talking workbooks.. then you can just use a variable  and before you
switch to the second workbook.. just save that filename...

dim mybook as string

mybook = activeworkbook.name (or thisworkbook.name)
paste or what ever then


that'll bring you back to the first workbook.

of course like i said i'm confused at what your trying to do.. so i may be way

Answer #3    Answered By: Volney Fischer     Answered On: Nov 13

Thanks for the responses and sorry for the confusion. I was trying
to be brief, I know everyone is busy.

I have a workbook  that gathers summary data  to start a process (not a
user form, but a business form). This data is in three parts, a
general header (common to all), data in a row specific to a single
product and a grid listing further detail down a column.

This data is used to populate a second workbook (another business
form). The first workbook can contain data to populate up to 15 of
the second.

So, I am pulling  data from workbook one, opening workbook two and
adding it, moving back to workbook 1 and going to the next data set
and repeating.

I was able to solve my issue using the Workbooks(VARIABLE).Activate
code. May not be the most efficient way to process, but it seems to
be working.

Thanks all for the help, and again, sorry for the confusion.

Answer #4    Answered By: Sophie Campbell     Answered On: Nov 13

Just to be clear, there really isn't a business "form".
what you have is a spreadsheet, that "represents" (or simulates) a form.
In excel, the word "form" carries special meaning.

Just make sure you don't "bounce" back and forth
between the workbooks. It wastes a HUGE amount of time.
Do the second (and third, etc) workbooks already exist?
In that case, yes.. open  the second file, but you don't really have
to "move back" to the first file. In my previous example, simply
cycle through the:

sheets(thissheet).cell(1,1) =

type of scenario.
When you exit and save, you'll be back at the first book.
Of course, if you only have a couple of files, then the total cycle time isn't a
But if you're running like 10,20,100 files, bouncing back and forth can add
significant time to the overall process.

Didn't find what you were looking for? Find more on Activate Workbook by using a variable Or get search suggestion and latest updates.