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
----------------------------------
Tidak ada komentar:
Posting Komentar