copy common cells and variable range from multiple worksheets to single master workbook

Hoping that someone can help me!

I'm a complete novice when it comes to VBA, but i have an urgent need for help with a particular project at work.

Basically, i have a common workbook template that is used by multiple users across the business to request a cost for numerous new products.

Within the template, there is a common section at the top, where specific project information is entered. There is also a table beneath where 1 or many products can be entered, with specific information relating to that product in the same row.

All the submitted requests are uploaded via an email attachment, to a particular sharepoint directory.

What i would like to do in the master workbook is the following:-

1. Open in turn every uploaded workbook within the sharepoint directory and copy the following cells into the master workbook, each in it's own row (or next available), with the data in adjacent cells.... 1st cell to enter data is $B6.

Cells to copy from each sheet:

Common info contained within cells:
$DG$2,$N$11,$N$12,$N$19,$N$13,$AO$7,$AO$8,$AO$9,$AO$10,$AO$11,$AO$12,$AO$12,$AO$13,$AO$14,$BO$8,$BO$11,$BO$14

Product specific info: $U37, $AD37, $AH37, $DH37, $C37, $O37
Depending on the number of products requested, we need to repeat (loop?) until it finds the next blank row in the table. I have hidden a blank row in the table, so there will always be one!
All of the common information needs to be included for each product specific entry.

For each file, once the upload has been completed, i would like the file to be moved to another "archive" directory.

I have attached the template for information. The master workbook is still in development so can't share currently.

Hope that i've covered everything, if not let me know and i can elaborate a bit more!

Best regards
James

Bernie, Apologies for the late response.

Thinking about what i want, i've made things a little simpler (i hope!)....

Basically, i need to copy a variable range of data, from the "SKU Information" sheet from each workbook in the following folder (C:\Users\richardson.js\Desktop\FCR DIRECTORY\SUBMITTED).

The data range starts at B5 and ends in column AG, with a variable row number, determined by data entry into column B. However, this cell is populated by a formula, so i only need to find the last row with data in that cell.

The copied data from the source files, needs to be pasted into the next available row starting from cell B6 in the "FCR Summary" sheet of the active workbook that is running the VBA.

Once the data has been copied over, i would like the workbook to be closed and moved to an archive folder (C:\Users\richardson.js\Desktop\FCR DIRECTORY\ARCHIVE).

Hope that this makes sense?

Regards
James

Bernie,
Column B has the formula, which will only provide a value, dependant on other criteria in the same row.
So, last row should be found in column B.
Thanks!

Ok, understand. Say column X then, not at work so can't check. If ok, please highlight code that needs amending if it's wrong! Thank you


Sent from my iPad using Tapatalk

Thanks for this Bernie, but haven't you forgotten the code to paste values into FCR Summary sheet, on the next available line, starting at cell B5. Also for any files that are opened in the submitted directory, I need to disable the macros, as my company has a file classification macro to label each file as Highly restricted, restricted etc... Really appreciate ur help!


Sent from my iPad using Tapatalk

Bernie,

Thanks for the code, we are nearly there.... really appreciate your help and patience with me!

The only issues i have now are the following:-

1) Is that on copying the range from "SKU Information" sheet, it only pastes the last row.

The data within the "SKU Information" sheet is in a table, would it be easier to select that object, instead of finding the last row? Table name is "TABLE_SKU_INFORMATION".

2) i need the values pasted, currently it is pasting the formulas over, (ie: requires paste special values?)

If you could tweak the code i would be much appreciated!

James

Bernie, perfect-thanks!


Sent from my iPad using Tapatalk