Select last 30 rows of a specific table in a sheet with multiple tables

[xefa]

Hi all,

I'm trying to select a range containing last 30 rows in a specific table so i could then paste it where needed.

I only found solutions on how to select the same for last x filled rows in a sheet. However my sheet contains 3 different tables (all same amount of rows but different amount of columns) from which i need to copy last 30 rows every time i run the macro.

Any advice is apreciated
Kind regards

 

[Peter_SSs]

Welcome to the MrExcel board!

Is this what you mean?
I have assumed the table has at least 30 rows of data.

Sub Last30()
  With Range("Table2")  '<- Edit to your table name
    .Offset(.Rows.Count - 30).Resize(30).Select
  End With
End Sub

Note too that you rarely need to select ranges in vba to work with them and selecting slows your code. As an example, this would copy the range to a range starting at Z1 without having to select it.

Sub Last30_v2()
  With Range("Table2")  '<- Edit to your table name
    .Offset(.Rows.Count - 30).Resize(30).Copy Destination:=Range("Z1")
  End With
End Sub

 

[xefa]

Thank you Peter, this helps a lot, especially the v2 part as im always happy to improve at this.

I'm still learning and mostly only capable of gluing existing code together.

I tested the 2nd solution and it works well, however I'm having trouble pasting that data. Is the method different when using code that copies the content without selecting them?

Specifically i tried to PasteSpecial with transpose in other sheet but end up with PasteSpecial error which i assume is because it actually has no copied data?

Worksheets("Test").Range("A1").PasteSpecial Transpose:=True

Sorry for extending the original question like this.
Kind regards

 

[Peter_SSs]

Try this

Sub Last30_v3()
  With Range("Table2")  '<- Edit to your table name
    .Offset(.Rows.Count - 30).Resize(30).Copy
    Worksheets("Test").Range("A1").PasteSpecial Transpose:=True
  End With
End Sub

 

[xefa]

Thank you Peter for your help this works !

I was putting it outside the With as i tough it only affects Table

Have a splendid day

 

[Peter_SSs]

I was putting it outside the With

That should be fine.
This works just as well for me.

Sub Last30_v4()
  With Range("Table2")  '<- Edit to your table name
    .Offset(.Rows.Count - 30).Resize(30).Copy
  End With
  Worksheets("Test").Range("A1").PasteSpecial Transpose:=True
End Sub

I assumed that the problem earlier was that you not removed this pert of my original code

.Offset(.Rows.Count - 30).Resize(30).Copy Destination:=Range("Z1")