Need help with a macro to paste cells from other workbooks

[balla506]

Hi All,

I know a little about VBA but am struggling with this. To lay a little ground work we run quarterly reports and I have created a template spreadsheet which via macro breaks out worksheets into 6 separate files. There are 40 some worksheets. This macro needs to copy multiple ranges from the previous quarter's workbooks (these ranges are the Current Year's forecasted amount) The hierarchy tab is where the vlookup is occuring. I basically need to copy the ranges and paste them into the template in the same exact spots (ex: '[2014 Q2 INST.xlsm]Institutional'!A1:B10, and range A30:B35 and about 6 more ranges needs to be copied to the same exact spot on the Institutional worksheet in the Sales Forecast Template workbook. The hierarchy worksheet in P:R shows what file contains the certain worksheet that needs to be copied. Column P is just a list of the worksheets (or customers) in the sales forecast template, column Q is the file name it exists in from the previous Quarter (ex:2013 Q1 INST.xlsm) and column R is the string for the specific worksheet for the customer (ex:'[2013 Q1 INST.xlsm]Institutional'!). My feeble attempt at this has failed miserably. Any help would be much appreciated. Thanks.

Josh

Please Login or Register  to view this content.

[AlphaFrog]

I'm not sure I follow your data construct, but here's a guess.

This loops through each customer in Hierarchy!Q1:Q200 and copies from their workbook (in column P). The macro assumes each workbook is already open.

Please Login or Register  to view this content.

[balla506]

Thanks for looking at this AlphaFrog. I tried running the code and got a compile error. Invalid next control variable reference. I think it is because rngcust is not dimensioned (should this be set as a range also?). Also the customer names/Sheet names are in column P so I believe this line of code should read

Please Login or Register  to view this content.

Thanks so much for your help!

Josh

[AlphaFrog]

Please Login or Register  to view this content.

[balla506]

This works beautifully. Thank you so much for your help and quick response on this.

[AlphaFrog]

Your welcome.

Minor fix: the messages are swapped. Source<>Destination

Please Login or Register  to view this content.