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":

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


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.