It is really difficult for me to solve this problem without seeing the
workbooks. If you want you could send then to me (david at Grugeon dot com
dot au) and I will put the macro in and test it.
Best Regards
David Grugeon
Excel VBA Group Moderator
-----Original Message-----
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On Behalf
Of bazza36
Sent: Thursday, 16 August 2012 7:59 AM
To: ExcelVBA@yahoogroups.com
Subject: [ExcelVBA] Re: Worksheet update Automatically from same cell in
another workbook
Thanks.
I tried it, and could not get it to work.
My knowledge of Vba is pretty much non existent, so I am trying to
understand this as much as possible.
(In the 'Set s' line, it looks like there might be an extra space after
before the .Worksheets. I tried taking out the space.) Where is it telling
the "tops" workbook the name of the "orders" workbook?
Looking over my post, it looks like I was also unclear, and I would like to
clarify.
The "b5" was just an example of a cell that would have info. Many of the
cells (lets say c3:NL32) could have info in it. The goal is that any cell in
the Orders worksheet of the orders workbook that has info in it would be
updated automatically (or when it is opened) in the Orders worksheet of the
Tops Workbook.
Thanks for your continued help.
--- In ExcelVBA@yahoogroups.com, "David Grugeon" <yahoo@...> wrote:
>
> I would put it in the TOPS file as I think that is the one which is
> always available. The orders files will be freshly generated for each
> order and there is no need to have the macro in them
>
> Best Regards
> David Grugeon
> Excel VBA Group Moderator
>
> -----Original Message-----
> From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On
> Behalf Of bazza36
> Sent: Wednesday, 15 August 2012 5:24 AM
> To: ExcelVBA@yahoogroups.com
> Subject: [ExcelVBA] Re: Worksheet update Automatically from same cell
> in another workbook
>
> Thanks for that.
> The VBA would be the best, as it would be a lot of the cells which
> need data entry.
> IS that code going into the Tops file or the orders?
> Thanks so much for your help.
>
> --- In ExcelVBA@yahoogroups.com, "David Grugeon" <yahoo@> wrote:
> >
> > Hi
> >
> > There are several ways of approaching this.
> >
> > One would be to fill every cell in the sheet (or in so much as you
> > want
> > filled) with a formula like (in A1)
> > ='[Orders.xlsm]Orders'!A1
> >
> > You may not need this in every cell - much of your order may consist
> > of standing text, formulas, etc. You probably only need to collect
> > the variable information.
> >
> > If the source file name can change you may need to use an indirect
> formula.
> > You put the filename in b5 and then use a formula like
> > =indirect("'["&B5&"]Orders'!A1")
> >
> > However this will not result in fixed values in the main workbook.
> > To achieve this you will need, either to select all the cells, copy
> > and PasteSpecial/values, or you could use vba.
> >
> > The VBA will look something like
> >
> > '========================================
> > Sub CopyValues()
> >
> > Dim s as Worksheet
> > Dim t as Worksheet
> > Dim c as range
> >
> > Set t = Workbooks("Tops.xlsm").Worksheets("Orders")
> > Set s = Workbooks(t.range(B5).value) .Worksheets("Orders") For each
> > c in t.Range("A3:G200") ' Set this to the area you want copied
> > c.value=s.Range(c.Address)
> > Next c
> >
> > End Sub
> > '===========================================
> >
> > If there are multiple ranges of cells you want to copy you can
> > specify the range like
> >
> > t.Range("A3:C45,F7:F12")
> >
> > Best Regards
> > David Grugeon
> > Excel VBA Group Moderator
> >
> > -----Original Message-----
> > From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On
> > Behalf Of bazza36
> > Sent: Friday, 3 August 2012 5:40 AM
> > To: ExcelVBA@yahoogroups.com
> > Subject: [ExcelVBA] Re: Worksheet update Automatically from same
> > cell in another workbook
> >
> > Hi,
> > Thanks for your response; I hope I can clarify.
> > I am using excel 2007, both files in the same directory.
> > The main file (Workbook 1) is called Tops.xlsm, and the smaller is
> > Orders.xlsm. (The worksheet would also be called orders). The data
> > is in all different columns and rows.
> > The orders file (workbook 2) is set up in the same fashion (columns
> > rows
> > etc) as the Orders Worksheet in the main Tops workbook. It is
> > basically a copy of the Orders worksheet in the main file, made into
> > a
> separate file.
> > Thats why I thought the easiest way may be to put orders into the
> > orders file, and tell the Tops file to update its orders worksheet
> > based on every cell of the orders file, so that whenever an order is
> > put into the order file, it will get updated into the same cell as
> > the orders worksheet of the main file. So there would be many cells
> > in all different columns and rows; my question is, is there a way to
> > tell the main workbook 1 file to update every cell based on the
> > exact same cell in the orders file. (It doesnt have to be immediate,
> > it could update the
> main file when it gets opened).
> > I hope that is a bit clearer, thanks for any help!
> >
> > --- In ExcelVBA@yahoogroups.com, "David Grugeon" <yahoo@> wrote:
> > >
> > > Hi bazza36
> > >
> > > Could you let us know how the data is organised in the "Orders"
> > > worksheet in Workbook 1?
> > > What columns/rows do you want copied and where in Workbook 2 do
> > > you want them?
> > > Is there a column which would be filled in on the last row used in
> > > worksheet "Orders"
> > > What are the names of the files (including extensions) What is the
> > > name of the destination sheet in Workbook 2 ?
> > > What version of Excel are you using?
> > > Are the two workbooks always going to be in the same directory?
> > >
> > > Best Regards
> > > David Grugeon
> > > Excel VBA Group Moderator
> > >
> > > -----Original Message-----
> > > From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com]
> > > On Behalf Of bazza36
> > > Sent: Wednesday, 1 August 2012 3:10 PM
> > > To: ExcelVBA@yahoogroups.com
> > > Subject: [ExcelVBA] Worksheet update Automatically from same cell
> > > in another workbook
> > >
> > > Hi all, thanks for being there to help out. I am looking to have a
> > > workbook that automatically updates itself from the same cell in
> > > another
> > workbook.
> > > I.E. Workbook 1 has a few worksheets, 1 of which is called orders.
> > > I would like to enter the info into a different workbook (workbook
> > > 2), with
> > > 1 worksheet called orders, so that when it is entered into
> > > workbook 2, into cell b5, it should automatically update (or at
> > > least when
> > > opened) in Workbook 1, worksheet "orders", into cell b5.
> > > If no data in workbook 2, workbook 1 should be blank.
> > > (If it is relevant: I want to do this because I enter some info
> > > from a mobile device, and workbook 1 has formulas that get changed
> > > when the file is opened on the mobile device. This way I can make
> > > a simple input workbook (2) for orders, and then it will get
> > > pulled into workbook 1 where the formulas can all go to work etc.
> > > I will set it up so that each cell should be the same in both
workbooks. ).
> > > Thanks so much for any help!
> > >
> > >
> > >
> > > ------------------------------------
> > >
> > > ----------------------------------
> > > Be sure to check out TechTrax Ezine for many, free Excel VBA articles!
> > > Go
> > > here: http://www.mousetrax.com/techtrax to enter the ezine, then
> > > search the ARCHIVES for EXCEL VBA.
> > >
> > > ----------------------------------
> > > Visit our ExcelVBA group home page for more info and support files:
> > > http://groups.yahoo.com/group/ExcelVBA
> > >
> > > ----------------------------------
> > > More free tutorials and resources available at:
> > > http://www.mousetrax.com
> > >
> > > ----------------------------------Yahoo! Groups Links
> > >
> >
> >
> >
> >
> > ------------------------------------
> >
> > ----------------------------------
> > Be sure to check out TechTrax Ezine for many, free Excel VBA articles!
> > Go
> > here: http://www.mousetrax.com/techtrax to enter the ezine, then
> > search the ARCHIVES for EXCEL VBA.
> >
> > ----------------------------------
> > Visit our ExcelVBA group home page for more info and support files:
> > http://groups.yahoo.com/group/ExcelVBA
> >
> > ----------------------------------
> > More free tutorials and resources available at:
> > http://www.mousetrax.com
> >
> > ----------------------------------Yahoo! Groups Links
> >
>
>
>
>
> ------------------------------------
>
> ----------------------------------
> Be sure to check out TechTrax Ezine for many, free Excel VBA articles!
> Go
> here: http://www.mousetrax.com/techtrax to enter the ezine, then
> search the ARCHIVES for EXCEL VBA.
>
> ----------------------------------
> Visit our ExcelVBA group home page for more info and support files:
> http://groups.yahoo.com/group/ExcelVBA
>
> ----------------------------------
> More free tutorials and resources available at:
> http://www.mousetrax.com
>
> ----------------------------------Yahoo! Groups Links
>
------------------------------------
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
here: http://www.mousetrax.com/techtrax to enter the ezine, then search the
ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------Yahoo! Groups Links
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar