Copy from another workbook based on data in a column

[biznez]

Hi everyone, I require a vba code where if in workbook "Summary", sheet "Report" column "E" = orange, apple and cherry then take the sum total from "F" and input that total into another workbook "Report", sheet "Total" in column "E7".
Thanks

[Arkadi]

Biznez,

Hi again. Which book would the code reside in? Also you are saying that for all used range of column E, if E has "orange", or "apple" or "cherry" then add the F value to a total, and that total is then put in "Report"?

[biznez]

Biznez,

Hi again. Which book would the code reside in? Also you are saying that for all used range of column E, if E has "orange", or "apple" or "cherry" then add the F value to a total, and that total is then put in "Report"?

Hey Arkadi, yes the code would reside in workbook "Report" sheet "Total"

[Arkadi]

Not too fancy, but will do the trick:

Please Login or Register  to view this content.

[biznez]

Not too fancy, but will do the trick:

Please Login or Register  to view this content.

Hi Arkadi, the file summary.xlsx will always be open. is the path c:\summary.xlsx necessary cuz this file is opened by explorer in excel format so i dont think it would have a path.

[biznez]

How can this code work if i have additional data in column E in workbook Summary and instead of putting the total in E7, it puts it in E8?

for example, also looks for "Cat, dog, lion" and puts that total in E8?

[Arkadi]

Sorry... so for other word matches you need a separate total?

[Arkadi]

Please Login or Register  to view this content.

[biznez]

Getting error 'Run-time error '9' Subscript out of range on the red line below

Please Login or Register  to view this content.

[Arkadi]

is summary.xlsx open? and is it called xlsx or xlsm?

[biznez]

its open and on the header it says "Summary [Compatibility Mode]. Its not saved anywhere as i extracted it from an external application.

[biznez]

ok now i saved it as xlsx and it still getting that same error message

[Arkadi]

I'm not sure how exactly you reference it, try just "Summary" without extension, or go into vba editor make a module in the Summary workbook, and make a sub with one line:

Please Login or Register  to view this content.

It should return the name of the book as it is in that condition.

[biznez]

I'm not sure how exactly you reference it, try just "Summary" without extension, or go into vba editor make a module in the Summary workbook, and make a sub with one line:

Please Login or Register  to view this content.

It should return the name of the book as it is in that condition.

Summary.xlsx is what i got

[Arkadi]

And is it still open?

[biznez]

yes its open

[biznez]

nows im getting

Run-time error '1004': Method 'Range' of object '_worksheet' failed

Please Login or Register  to view this content.

[Arkadi]

Ok, how did you get past the first error?

As for the new error... does the sheet "Report" exist in the "Summary" workbook? and does it have data?

[biznez]

Ok, how did you get past the first error?

As for the new error... does the sheet "Report" exist in the "Summary" workbook? and does it have data?

Hi Arkadi, is there any simpler way of writing this code?

[Arkadi]

What part are you trying to simplify? Is it that you are getting errors on and off, or having trouble adapting it, or something else? Just trying to understand what your concern is so I can do my best to help accordingly

[biznez]

What part are you trying to simplify? Is it that you are getting errors on and off, or having trouble adapting it, or something else? Just trying to understand what your concern is so I can do my best to help accordingly

still stuck at the error at "Set wb = Workbooks("Summary.xlsx")"
getting error subscript out of range. not sure why this is happening when the file is already opened

[Arkadi]

you said before that summary is a workbook extracted from an external application... is it still that way, or is it a saved book at this point?
Can you upload the report workbook, and if summary is a saved one now, maybe also that one?

[biznez]

you said before that summary is a workbook extracted from an external application... is it still that way, or is it a saved book at this point?
Can you upload the report workbook, and if summary is a saved one now, maybe also that one?

summary is a workbook extracted from an external application (access software). The actual file name is Summary_Batch_RPT1.xlsx

[biznez]

In addition. When the summary file is extracted, it opens new excel application. A whole new workbook. Maybe thats the issue. This code does not connect with the other new excel. Perhaps?

[Arkadi]

Yep that could be... that is a pretty relevant detail I suppose, I'll do a few tests when I can and get back to you.