Automatically change graph references when pasting graph into new sheet

Question

Hi there,

I am trying to make a more streamlined process for my data. I have over 200-300 sheets formatted the same way (from Row1 to Row30000) but vary with the data between them (ex: AA2-AA5 in one sheet is 2,3,4,5, while another sheet's AA2-AA5 being 0,1,1,0). I am tired of reformatting the graphs (upwards of 10 graphs on each sheet) each time I copy and paste the graphs onto a new sheet. I thought that making a reference template sheet with the graphs ready for the data would work (just pasting the data instead of the graphs), but when I duplicate the sheet, the graphs do not duplicate over with the sheet. I am willing to try a VBA solution but I have about 10 min of experience in it.

Thanks

Answer 1

Right click the sheet name. Then click create a copy. The chart will always use the data range in same sheet.

==============

Sub Macro2() 'Create 100 copy of sheet1

For i = 1 To 100

Sheets("Sheet1").Select 

Sheets("Sheet1").Copy Before:=Sheets(1) 

Next i 

End Sub 

==============

==============

Sub Macro3() 'Copy data from workbook 2.xlsx to 3.xlsx for 100 sheet

For i = 1 To 100

Windows("2.xlsx").Activate 

Sheets(i).Select 

Range("A1:C2").Select 

Selection.Copy 

Windows("3.xlsx").Activate 

Sheets(i).Select 

Range("A1").Select 

ActiveSheet.Paste 

Next i

End Sub

==============