MACRO to get sum of specific column from various worksheets and appending the sheet name

[ALNER]

Hi,

I have a workbook named as "DCR_Summary". In the sheet "FX", I want to get the sum of a specific column from two different workbooks named as "WNCR REPORT" and "DCCR-REPORT". The name of the column is "FXCOLL". The summation should start in row 7 all the way down where data is available. The good thing is that, my sum range will always starts at row 7 in both files but the bad thing is that the position of column "FXCOLL" is changing every day. The expected result is shown in the attached "DCR_Summary". It should pick at the same time the Sheet names where the summation came from. Sheet names is also changing and sometimes the FXCOLL is nil, hence, 0 value can be returned.

Summing this everyday is inefficiency for me, please help to create the code to get this done on a single click.

Thanks and regards,

Alner

[natefarm]

Please Login or Register  to view this content.

[ALNER]

Hi Natefarm,

there is an error in

Please Login or Register  to view this content.

please see the screen shotsGetFX.jpg

[natefarm]

The workbooks with those exact names have to be open for the code to work. If you need the code to open the workbooks for you, that could be added. Also, the summary workbook has to be the active workbook when you run the code.

[ALNER]

oh my! It's like a miracle to me! it works perfectly! Thanks a million...

[Tinbendr]

Dang! Nate beat me to it.

My contribution.

Please Login or Register  to view this content.