VBA: Copying specific columns of table regarding number of rows

[zovche]

Hello,


i have interactive table with parameters, and it's updated once a week, and each week table will contain different number of rows. So in VBA how would i define definition that regardless of number of rows i copy that table completely to another sheet.


This is my current code for copying from 8th-76th row, but tomorrow that table could have 90 rows, so how to automatically define in VBA that? Would it help if I name that table? Thank You

Sub Prebacivanje_Prodaja()
'
' Prebacivanje_Prodaja Macro
'


'
    Range( _
        "D8:D76,G8:G76,H8:H76,I8:I76,J8:J76,K8:K76,L8:L76,M8:M76,N8:N76,O8:O76,P8:P76,R8:R76" _
        ).Select
    Range("R8").Activate
    Selection.Copy
    Sheets("Stavke").Select
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

 

[p45cal]

deleted (duplicate post - site is taking forever to update).

 

[p45cal]

try:

Sub Prebacivanje_Prodaja()
lr = Cells(Rows.Count, "[B][COLOR=#ff0000]D[/COLOR][/B]").End(xlUp).Row 'last row
Range("D8:D" & lr & ",G8:P" & lr & ",R8:R" & lr).Copy
Sheets("Stavke").Range("B2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

Adjust the D to the column guaranteed not to have a blank cell at the bottom of the table.

 

[zovche]

It seems as a good logic, but need some adjustments as this code copies everything from row 8 to top row 1, but I need from row to the bottom of the existing table no matter how many rows it has (it's variable every single time).

Can you help more to modify it.

Thank you.