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