MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

macro to get info from workbook thats name changes

  Asked By: Magenta    Date: Nov 02    Category: MS Office    Views: 1191

I'm fairly new to vba and am learning just now so any help is
appreciated. Ive written various macros that take information from a
workbook containing a form, however ive just realised that the form
is being saved under different names before the macros are going to
be run and that all my macros begin with activating the workbook
name ("Master.xls"). How do i get them to work when i don't know the
name the workbook will be saved under. Is their a way to select the
name of the worksheet and insert it into the macro before running,
that way it will work with any of the saved workbooks.

If I haven't explained it well enough please let me know.



7 Answers Found

Answer #1    Answered By: Adalgisa Fischer     Answered On: Nov 02

I am sorry, I don't understand.
Can you explain step by step what you are trying to do?

Answer #2    Answered By: Meenakshi Khochar     Answered On: Nov 02

Didn't think i had explained this very well so here goes -

Have a workbook  called Master.xls which contains a form  with a
customers information  on it (and various other worksheets feeding
from the form).

Different individuals in the office enter information into the form
and then save a copy of the workbook under whatever name they choose
and they do this for several customers under whatever names  they

Now I am not allowed to actally make any changes to the form so what
i have done is stuck macros  on a seperate workbook which will be
open alongside the saved form. My companion workbook has command
buttons that allow you to rearrange information on the form and copy
information from the form to the companion workbook

Everything was alright until i realised that the code might be used
on saved forms with different names and now i'm lost. I'm trying to
make it as easy to use as possible. It will be used on one workbook
at a time with basically a random name.

Is there a way they can pick the workbook to be used from list of
their saved workbooks  and this value passed to my macros. Sounds a
tad out of my skill range at the moment.

Answer #3    Answered By: Roxanne Dixon     Answered On: Nov 02

I am still not sure I understand, but I'll try anyway.
if you are working in the specific workbook  when the macro  (from your
Master.xls) is going to run  then, in your macro, why don't you try to pick up
the workbook name: Let's say something like:

dim strActiveWorkbookName as string
strActiveWorkbookName = activeworkbook.name

Does that help?

Answer #4    Answered By: Elisa Larson     Answered On: Nov 02

not sure if that works if i'm in the open companion workbook  when
the macro  is run. sorry if i'm not explaining this too well.

Answer #5    Answered By: Alessio Smith     Answered On: Nov 02

I am guessing that at the time of the running  of your macro's, you would
know the name of the workbook.

So, you could setup a variable that you would fill-in at the time you
run the macro.

Dim strWorkbookName as String
strWorkbookName = InputBox("Type the Name of your Workbook") &".xls"

Then anywhere in your macro  where you are specifically referencing a
workbook, replace that information  with strWorkbookName. So
("Master.xls") would be replaced with (strWorkbookName)

Answer #6    Answered By: Sammy Anderson     Answered On: Nov 02

Could you post the code that you are using so that we can "see" what you
are doing?

Answer #7    Answered By: Sammy Brown     Answered On: Nov 02

Sub Changevalues()
Range("AV18").Value = False
Range("AV19").Value = True
Range("AV22").Value = Range("AW22").Value
Range("I28").Value = Range("AB28").Value
Range("I30").Value = Range("AB30").Value
Range("I32:I36").Value = Range("AB32:AB36").Value
Range("I39:N39").Value = Range("AB39:AG39").Value
Range("I41:N41").Value = Range("AB41:AG41").Value
Range("I43:N43").Value = Range("AB43:AG43").Value
end sub

I think all of my macros  begin with the line Windows
("Master.xls").Activate . Unfortunately as i say its actually going
to have to run  under any name instead of master. I'm trying to work
out the easiest way to do it. Because its for other people whoare
not very pc friendly i'm trying to make it a simple click this
button process. However the buttons and macros are in a seperate
workbook from the master.

Didn't find what you were looking for? Find more on macro to get info from workbook thats name changes Or get search suggestion and latest updates.