I believe I can get around this by using Set sourceWB etc.

More significant issue is your presumption that both workbooks have the same number of sheets and each sheet is logically subsequent i.e. Sheet1, Sheet2 etc

What I am attempting to do is take for example cell AC2 from the first sheet (not named sheet1) and copy it into cell AB3 (However if easier it could go into A1) in a different workbook or worksheet (whichever is easier). I then want cell AC2 on the second sheet (not named sheet2) copied into the row below e.g. A2.

I want this to occur for every sheet and have all the cells AC2,AC16,AC17,AD8,AD9,AD10,AD11,AC5,AC3,AD3 copied into a single row for every worksheet.

Sorry if this is still confusing.