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