I want VB to select data automatically to the end of the row

[Miguel]

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

 

[Eduardo]

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]

Eduardo,
the program didn't work. The LastRow subfuntion is not defined. Is there
another way to make it work?

Thanks

 

[OssieMac]

Hi Miguel,

I think you are looking for someting like this. Note that the space and
underscore at the end of a line is a line break in an ohterwise single line
of code. I use them in posts so that the data fits and can be copied into
your VBA editor without the hassel of joining up lines that break due to
posting.

If you are uncertain of what I have done to the range then look up
concatenating strings because that is all I have done.


Dim lngLastRow As Long 'Variable for last row of data

'Assign last row number with data to variable
'It is basically like selecting the last cell
'on the worksheet in column 1 then hold Ctrl key
'and press up arrow and then identify the row number.

With Sheets("Dental")
lngLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
End With

ActiveSheet.ChartObjects("Chart 19").Activate
ActiveChart.PlotArea.Select

ActiveChart.SeriesCollection(1).XValues = _
"=Dental!R16C1:R" & lngLastRow & "C1"
ActiveChart.SeriesCollection(1).Values = _
"=Dental!R16C2:R" & lngLastRow & "C2"
ActiveChart.SeriesCollection(2).XValues = _
"=Dental!R16C1:R" & lngLastRow & "C1"
ActiveChart.SeriesCollection(2).Values = _
"=Dental!R16C3:R" & lngLastRow & "C3"
ActiveChart.SeriesCollection(3).XValues = _
"=Dental!R16C1:R" & lngLastRow & "C1"
ActiveChart.SeriesCollection(3).Values = _
"=Dental!R16C8:R" & lngLastRow & "C8"
ActiveChart.SeriesCollection(4).XValues = _
"=Dental!R16C1:R" & lngLastRow & "C1"
ActiveChart.SeriesCollection(4).Values = _
"=Dental!R16C9:R" & lngLastRow & "C9"
ActiveWindow.Visible = False

 

[Eduardo]

Hi Miguel,
I don't know what happen but when copy the code something was missing add
this to the beginning
' Sub CopyDataWithoutHeaders()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim shLast As Long
Dim CopyRng As Range
Dim StartRow As Long