Find the Last Row, Column, or Cell on a Sheet then

[Unicode]

Hello Everyone, I am not very in detail with macro VBA coding, I have compiled the following code. My project is set on a flat file, vertical format across range (A:D) there is only 4 columns with data. Titled "Bank, Year, Month, Total". Due to the months of the year, below "Year" column I see 2018, after, 9 rows down I have year 2019. My code selects the range of the 4 columns and creates a Chart on the next TAB called "Chart". Would you know how to make script code function if I enter the next month on the "DataSource" file going down on the "YEAR" column within "Total" columns, so, that when I run my script it will automatically list the next month and only keep the last 12 months behind and keeping the next added month moving forward on the second TAB named "Chart"?



[Code:]


Sub addcharttest()
Dim cht As ChartObject
Dim wks As Worksheet
Set wks = ActiveSheet




' Author: Unicode
' Date: 6/26/2019


'## Select Data from sheet "DataSource TAB". Select Range (A:D), Copy then, Paste results on Sheet(CHART), "Start paste one (8) Row Down" into sheet "Chart".


Dim LastRow As Long 'no dummy data with these Dimensions
Dim Results As Worksheet
Dim DataSource As Worksheet




Application.ScreenUpdating = False 'Makes the code run faster and reduces screen _
flicker a bit.




Set Results = Sheets("Chart")
LastRow = Results.Cells(Results.Rows.Count, "Z").End(xlUp).row

Range("A9:D23").Copy
Results.Range("A" & LastRow + 8).PasteSpecial Paste:=xlPasteValuesAndNumberFormats

Application.CutCopyMode = False





'## This is the Sheet script on Sheet(DataSource TAB) that requires the following script line for currency format (.NumberFormat = "$#,##0.00"), changes into your worksheet named "Chart" with RANGE(A1:O2).


Application.CutCopyMode = False


With Sheets("Chart").Range("A10:A23", "C10:C23")
.NumberFormat = "General"
.Value = .Value
End With

With Sheets("Chart").Range("D10:D23")
.NumberFormat = "$#,##0.00"
End With



'add an embedded chart and set it equal to an object variable


Set cht = wks.ChartObjects.Add(420, 75, 400, 250)


'specify the data and the chart type


With cht.Chart
.SetSourceData wks.Range("A9:A9", "A10:D23")
.ApplyCustomType xlColumnClustered
Worksheets("DataSource").ChartObjects.Delete '<-- Code deletes all charts within a worksheet


End With
End Sub

 

[sijpie]

You don't need to do any VBA. See here:

https://peltiertech.com/Excel/Charts/DynamicLast12.html

 

[Unicode]

How about I do this with the following

:

Sub setname()
ActiveWorkbook.Names.Add Name:="Last12", RefersTo:= _
"=OFFSET(ThisWorks!$a$1,COUNTA(ThisWorks!$a:$a)-12,0,12,1)"
End Sub






[QUOTE="sijpie, post: 5304450, member: 105311"]You don't need to do any VBA. See here:

[URL]https://peltiertech.com/Excel/Charts/DynamicLast12.html[/URL][/QUOTE]