Hello,
I am using a data validation list in cell A22 on sheet1 to call a macro. For example, when I select "1" from the list, I want a macro to copy the range ("C46:BH63") from sheet2 and paste it 2 cells to the right, for example ("C22:BH39"), of the data validation cell on sheet1. I want it to paste right of the data validation list cell because in sheet1 I will have many of this same list, just in different locations vertically.

I am currently using this code to copy:


Sub Copy_Info1

Sheets("sheet2").Range("C26:BH43").copy Sheets("sheet1").Range("C22:BH39")

End Sub


The next data validation list is 20 cells lower in cell A42, and the code would be the same except for the macro name and copy/paste location:


Sub Copy_Info2

Sheets("sheet2").Range("C46:BH63").copy Sheets("sheet1").Range("C42:BH59")

End Sub



But I do not want to create a new macro for each list to paste 2 cells right of each list. I just want one macro that will paste 2 cells right of the active cell in sheet1, which should be the data validation cell.

Thank you