Trying to indirectly reference ranges for arrays in a control sheet

[galenthias]

Hi,

I have a workbook which has multiple sheets for our product ranges. Each sheet is laid out the same with the same charts and tables.

I've created code that will take a specified range in the vba of each sheet and copy the contents as an image into a presentation which 1 range per slide. It's working well but I will need to do the same for many more products.

Instead of manually writing all the arrays into the VBA code is there a way of having my Control sheet with a list of sheets and their ranges next to them to be used in the arrays as some of the ranges can change sheet to sheet?

Current array selection:
RngArray = Array(Worksheets("PROD1").Range("C1:Y43"), Worksheets("PROD2").Range("C1:Y44"))

The CONTROL sheet would be like this:

Sheet	Range1
PROD1	C1:Y43
PROD2	C1:Y44

I've come back to VBA after 12 years away from it and I'm a bit rusty!

Thank you!

Pen

 

[mart37]

Sub MakeImages()
    Dim shControl As Worksheet
    Dim rngArray As Range
    Dim lLastrow As Long
    Dim shName As String
    Dim sRange As String
    Set shControl = Worksheets("Control") 'name of the control sheet
    lLastrow = shControl.Range("A" & Rows.Count).End(xlUp).Row
    For x = 2 To lLastrow
        shName = shControl.Range("A" & x)
        sRange = shControl.Range("B" & x)
        Set rngArray = Worksheets(shName ).Range(sRange)
        'here your code
    Next
End Sub