Hi there,

Sorry if this appears twice - first attempt crashed on posting so trying to
re-submit my question again.

Ok here goes. I'm trying to automate a monthly process where i copy a range
of cells from various sheets in a workbook and paste the values into another
workbook.

So for example I copy A3:A22 from sheet1 in a workbook (lets call it
TESTSOURCE.xls) and paste the values to A15 in sheet1 in another workbook
(lets call it TESTTARGET.xls). I then copy sheet 2 to sheet 2, sheet 3 to
sheet 3 etc etc. I'm sure you get the idea.

I've managed to come up with some code that does this for me (not saying
it's efficient etc but it seems to work).

Sub Test1()

Workbooks("TESTSOURCE.xls").Activate
Sheets("Sheet1").Range("A3:A22").Copy

Workbooks("TESTTARGET.xls").Activate
Sheets("Sheet1").Range("A15").PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False



Workbooks("TESTSOURCE.xls").Activate
Sheets("Sheet2").Range("A3:A22").Copy

Workbooks("TESTTARGET.xls").Activate
Sheets("Sheet2").Range("A15").PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

End Sub



However the next month i need to copy Range B3:B22 to B15, the month after
that it's C3:C22 to C15 ... so on and so forth... again i'm sure you see the
pattern/idea.

Now i could just go into the code and use find/replace each month to change
the copy range and destination but i'm trying to find a way to made set the
variable at the top of the code so that i (or other users) only need to
change it once and the code still works correctly. Anything i've tried thus
far keeps giving me range/class errors and the likes so i'm hoping someone
might be able to point me in the right direction?

Even better would be if i could call an input box which asked the user to
enter the source range and then specify the destination cell. Would that be
possible/easy to add in ?

Hope that makes sense but please let me know if anything i'm trying to do is
unclear.

Thanks in advance for your help.

Regards,
Craig