Can't get nested Loop to copy range values from 2 tables working correctly

Hi,

I am working on a VBA application and I have encountered a situation that needs a nested loop to copy values from 2 tables (Departments and Roles) from a Source sheet to a Destination sheet.

The copy operation needs to show all possible combinations of Department and Role that can exist as illustrated below :

Departments
A
B
C

Roles
1
2
3

Required result (9 possible combinations):

A1
A2
A3
B1
B2
B3
C1
C2
C3

I know we will need a nested loop here that goes through both tables sequentially using an inner and outer loop but I can't figure out the correct syntax to get this working. I have seached the forum but the examles I found were very complex and I couldn't amend the code to achieve the desired result without getting lots of errors.

I am attaching a spreradsheet to better illustrate what is required. I have also created the bare bones of a procedure for the loop.

I am hoping that there is some VBA guru out there who can help !!

Many thanks in advance,


GTOL