Transferring range of data from workbook

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:59 PM GMT

Question

I have a computer withworkbook the spreadsheed starts with a date and other data follows in the row, i want to copy from this workbook to another workbook on another computer but i only want to copy rows where the date = current date. the 2 computers are on the same network. i would like the second computer to update automatically so it always has the current info for the current date.

Answer

It sounds like you might want a macro (excel VBA code) that runs (preferably, triggered when the workbook opens, and/or also triggered whenever the user wants), in the second workbook, to copy rows if the date is today's date.

Actually I think there is a simpler way to do it.

Just copy a large, adequate range from the 1st workbook (say, maybe, A1:G50000 or whatever the range is that suits you). Then paste it as a LINK in the second workbook.

Lastly, write a macro so that in the second workbook that deletes any rows where the value in column A is not today's date.

Here is even another way to approach it.

Copy and paste the link even just from one cell in Workbook1 (copy, paste special, Link) to workbook2, just so that you get the syntax correctly, as it can vary depending on your server names, mapped drives on your computers/network, and workbook names. You will need to CLOSE workbook1, after copying the cell and pasting special as link in Workbook2, before you will see the correct syntax of the link formula in Workbook2. Let's just say for the saek of argument that the syntax for this link-formula, in Workbook2, ends up being this:

='U:\[Workbook One.xlsx]Sheet1'!$A$1

(in my example, I have a server shared location mapped to the letter U, but who knows how it will show up for you)

Now, if the data is always standardized/the same/predictable format in Workbook1, i.e. the date is ALWAYS the ONLY value in column A, then you can tweak the formula in Workbook2 to say this:

=IF('U:\[Workbook One.xlsx]Sheet1'!$A$1=TODAY(),'U:\[Workbook One.xlsx]Sheet1'!$A$1,"")

This will ensure that the cell displays nothing, except, if the value in workbook1 cell is today's date.

Now to make this formula useful so you can drag it down/across long areas, remove the dollar signs.

=IF('U:\[Workbook One.xlsx]Sheet1'!A1=TODAY(),'U:\[Workbook One.xlsx]Sheet1'!A1,"")

There are a lot of ways to approach this, you probably have some considering of all of this to do before deciding your next step.

PS, are you sure you dont' just want one single shared workbook? :)

Advertisement

©2020 eLuminary LLC. All rights reserved.