Hi Miguel,
Try
'loop through all worksheets and copy the data to the DestSh
For Each sh In ActiveWorkbook.Worksheets
If sh.Name < DestSh.Name And sh.Visible = True Then

'Find the last row with data on the DestSh and sh
Last = LastRow(DestSh)
shLast = LastRow(sh)

'If sh is not empty and if the last row = StartRow copy the
CopyRng
If shLast 0 And shLast = StartRow Then

'Set the range that you want to copy
Set CopyRng = sh.Range(sh.Range("A" & StartRow),
sh.Range("IV" & shLast))

'Test if there enough rows in the DestSh to copy all the data
If Last + CopyRng.Rows.Count DestSh.Rows.Count Then
MsgBox "There are not enough rows in the Destsh"
GoTo ExitTheSub
End If

'This example copies values/formats, if you only want to
copy the
'values or want to copy everything look below example 1 on
this page
CopyRng.Copy
With DestSh.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

'Optional: This will copy the sheet name in the A column
'DestSh.Cells(Last + 1, "P").Resize(CopyRng.Rows.Count).Value =
sh.Name
If the answer was helpful please press yes

"Miguel" wrote:

Hi,
I have recorded a macro, but I want to improve it. I want VBA to select my
data to the last row automatically instead of manually changing the last cell
number. This is the VBA code:
ActiveWindow.SmallScroll Down:=15
ActiveSheet.ChartObjects("Chart 19").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).XValues = "=Dental!R16C1:R74C1"
ActiveChart.SeriesCollection(1).Values = "=Dental!R16C2:R74C2"
ActiveChart.SeriesCollection(2).XValues = "=Dental!R16C1:R74C1"
ActiveChart.SeriesCollection(2).Values = "=Dental!R16C3:R74C3"
ActiveChart.SeriesCollection(3).XValues = "=Dental!R16C1:R74C1"
ActiveChart.SeriesCollection(3).Values = "=Dental!R16C8:R74C8"
ActiveChart.SeriesCollection(4).XValues = "=Dental!R16C1:R74C1"
ActiveChart.SeriesCollection(4).Values = "=Dental!R16C9:R74C9"
Windows("Dental RADAR Monthly Report 200810.xls").SmallScroll Down:=36
ActiveWindow.Visible = False

Thus, instead of changing the number 74 (last row) I want VBA to
automatically do it. Is that possible?

Thanks
Miguel