Dynamic Range VBA

[Heathkleff]

Hello guys,

We are using and old file for doing reporting every week, and this file contains many graphics. I am trying to improve this file by adding new graphic that uses data coming from the sheet "Zero Trafic" I recently created and that is updated every week by new data coming from an other file.

The idea is when I click on the button "Décaler sem +1", the new graphic should be updated and the graphic should contains only the data of the last 11 weeks (Sxx).

Here bellow the macro affected to the button "Décaler sem +1":

Sub DecalerPlageBAS()


'Dim Plage As Range
Dim Nm As Name


Application.ScreenUpdating = False

'On Error Resume Next


'Sheets.Add After:=ActiveSheet
'Range("A1").Select


'Boucle sur les noms du classeur
For Each Nm In ThisWorkbook.Names
If Nm <> "=#NAME?" Then
NOMPLAGE = Nm.Name
Range(Nm).Select
premierecellule = ActiveCell.Offset(Selection.Columns.Count - 1).Address
dernierecellule = ActiveCell.Offset(Selection.Rows.Count - 1).Address


premierecelluleV2 = Range(premierecellule).Offset(1, 0).Address
dernierecelluleV2 = Range(dernierecellule).Offset(1, 0).Address


'définition de ma plage
MaPlage = premierecelluleV2 & ":" & dernierecelluleV2

'Modification de la plage
With ActiveWorkbook.Names(NOMPLAGE)
.Name = NOMPLAGE
.RefersTo = "=Evolution!" & MaPlage
End With

' ActiveCell = Nm
'ActiveCell.Offset(1, 0).Select
End If
Next Nm


Range("A1").Select
ActiveSheet.Calculate




End Sub

And here bellow a sample of data contained in the sheet "Zero Trafic":

Semaine	Inférieur 3jrs	Supérieur 3jrs	Supérieur 1sem	Supérieur 2sem
S53	319	190	30	0
S01	293	200	25	0
S02	595	238	41	11
S03	725	247	84	10
S04	622	356	98	22
S05	552	219	64	12
S06	726	301	44	11
S07	507	273	94	12
S08	606	291	78	5
S09	459	221	89	7
S10	754	191	77	25
S11	526	250	51	23
S12	668	299	100	34
S13	929	280	86	18
S14	709	442	97	16
S15	510	232	114	35
S16	414	131	43	14

<colgroup><col span="4"><col></colgroup><tbody>
</tbody>

Can you please give a help?

Thanks

 

[PatOBrien198]

Could you point your graphics information to a standard location and have your macro copy the last 11 weeks of data to the standard location. That way you would not need to update the graphic, just the info in the standard location.