Apr 27 2022 11:30 AM
Hello Excel folks.
I am hoping for some VBA help. I am needing to copy data, daily, from a report generated as a worksheet. That data is always in teh same location relative to the data label (see example) but the row is seldom if ever the same each time the report is run. The label will always be Column B and the needed value will always be Column M. In the example I need to copy the value of 2.64 to another sheet workbook and populate a cell next to "Apples"
Any help will be GREATLY appreciated
Copy from here
.
Copy to here
Thanks
JJ
Apr 27 2022 11:48 AM
Well, some questions to define this situation a bit more fully. Personally, I'm hoping that VBA is not what you need. I'm generally of the opinion that the more elegant solutions always take advantage of the amazing pre-defined functions/formulas that Excel offers. But there are times when the brute force methods have to be used, so I won't be absolutist about it.
Apr 27 2022 12:13 PM
I am all for the elegant approach. To be more clear, the worksheet that is generated but the 3rd party application is not adjustable at all. It is a report that is run daily and the generated worksheet is then discarded. The one that the data is being copied to (you probably figured this out) remains and is appended daily.
Thanks million for the help, manually copy/pasting this daily with a large collection of "fruit" is making me crazy.
Apr 28 2022 08:22 AM
SolutionHere's a formula for you to try.
=INDEX([Source.xlsx]Sheet1!$M$1:$M$100,XMATCH("Fruit: Apples",[Source.xlsx]Sheet1!$B$1:$B$100,0)+1)
I've made it work in the attached very rough example files.
Basic ideas:
You'd have to do some things to adapt it to your situation.
Apr 28 2022 05:04 PM
May 04 2022 06:28 AM
May 04 2022 06:44 AM
How about incorporating a use of INDIRECT to refer to the new file name (Report (1)...etc) .
INDIRECT can be tricky to master, but once you've gotten it, it gives a lot of flexibility.
https://exceljet.net/excel-functions/excel-indirect-function
May 04 2022 09:56 AM
May 04 2022 10:17 AM
Apr 28 2022 08:22 AM
SolutionHere's a formula for you to try.
=INDEX([Source.xlsx]Sheet1!$M$1:$M$100,XMATCH("Fruit: Apples",[Source.xlsx]Sheet1!$B$1:$B$100,0)+1)
I've made it work in the attached very rough example files.
Basic ideas:
You'd have to do some things to adapt it to your situation.