Copy range of cells using variable criteria and named ranges

I am trying to write some simple VBA that will find a range of cells on one worksheet, to go to another worksheet to paste values of that range based on another range of cells.

The method I’d like to adopt to reference the ranges is Range(Cells(R,C)),as it seems to provide the flexibility I need for the ranges, which may change. Additionally, I am using Named Ranges where I can in order to preserve references should rows or columns shift in the evolution of my spreadsheet.

That said, I’d like to: 1) Find an anchor cell [Named Range1], which is the upper left of the range; 2) Select the range where R = Named Range2, and C = Named Range1’s column value + Named Range3. For instance, Named Range1’s address is $B$5, Named Range2 = 11, and Named Range3 = 6. The goal is that the selected range for this example is $B$5:$G$15. If any of these Named Ranges [variables] change, the resulting selected range will change. Each of the named ranges are scoped to the workbook. Once the range is selected, I would like the macro to go to a second anchor [Named Range4], and paste values.

I’ve tried searching for an answer to this, found many similar questions, but my complete lack of programming skill and ignorance of VBA syntax and lexicon has just stopped me from gaining any traction. Without even attempting to code.

Any help the community can provide will I’m sure be invaluable.
Book1.xlsx

This was very helpful getting me started. I didn't quite understand the function/purpose of the If statement - so I made the changes below, and everything appears to be working as I need it to. I added some formatting commands just to test if everything was working...Thanks for your help!

Please Login or Register  to view this content.

Here is the spreadsheet as revisedBook1.xlsm