Forum Discussion
Processing a list of data through a separate excel setup
curry2445 I know you said it is a simplified example but I'm not sure why you need to copy the values over to perform that calculation and then copy the value back. Why can't column C just be =a2*b2 and fill down? Even if for some reason you do need to copy the values then you still only need to do it once.
If you truly need to do all these steps and want/need VBA then you probably want to do most if not all the calculations in VBA instead of sheet formulas then. So although I could show you a simple VBA that copies cell contents from A2 to ... it is really not worth much since it is almost certainly not what you should do.
So maybe give us a more accurate example (ideally the actual spreadsheet with personal info like names and other personal info anonymized).
I need to input cells C13, C14, C21, C22, D26, F26 and G26 into the MAIN tab in the attached sheet from an input sheet which looks like the capture image.
E2=C13, D2=C14, F2=C21, F2=C22, G2=D26, J2=F26 and K2=G26
Then I want to extract from the spreadsheet tab 'Cases' Q15 and V15 and insert them N2 and O2 respectively.
Then continue this on repeat from 2 to ~200.
curry2445 So it is as I feared, lol. If this is something you will do a lot in the future you may want to invest in tracing each formula back and creating the correct functions accordingly. That said, in the attached I wrote a macro to loop through all rows in an active sheet and do as you asked but be careful because I did NOT add any error checking and such (i.e. it will overwrite without prompting and such)
I tried to make the names of variable as easy for you to understand to tweak as needed. Instead of hardcoding the name of the sheet, I opted for using cell A1 to point to a cell in the other workbook on the main sheet and pulled the workbook and worksheet info that way.
I noted that you wanted to fill cells N2 and O2 even though it appears L2 and M2 are the "open" cells.
I also noted that after running it the values for your sample were "not good".
I hope this template will at least let you figure out how to complete it.
Best of luck.
Thanks for this. It's really appreciated.
I am trying to run it and I running into error. Also definetely at my ability as a user rather than your code.
I have updated cell A1 to have the location where the calculation spreadsheet is saved on my computer. I have saved both sheets in this folder.
When I run the macro it gets stuck at:
Set MPAmain = Application.Range(Mid(Asheet.Range("a1").Formula, 2)).Worksheet
Do I need to link the sheets? I am currently running the macro out of sheet 1.
My mistake in my earlier capture I missed off )2 but that what the location I wanted than formula - thanks.
Thanks again.
curry2445 I'm sorry I didn't explain it well.
I changed the macro and sheet a little so I think it makes more sense for you.
Now the macro sheet only has 2 cells; one for the address of the data sheet and one for the address of that formula worksheet. The macro assumes the data sheet is the only or at least the first sheet in the workbook. If not you can tweak the macro (or the workbook).
If this works I hope you will like and mark as best answer 🙂
