help

[Luke1690]

please can someone help me im trying to copy a section of data that changes daily.
just want to add the columns aswell.

Sub somesub()
Dim last_row As Long
Range("d4").Select
range("d4").End(xlDown).Offset(-1).Select
Range(ActiveCell, ActiveCell.End(xlUp)).Offset(1).Select
Selection.Copy
end sub


I'm trying to do it all my self and I'm rubbish!!! I've managed to select the data that i need finding out the last entry and offsetting the title row.
i just need it to select the columns as well to (Q)

There might be an easier code than what I've used above to do this. Basically need range D4:Q4 Down to the last entry input copying.


Thanks

 

[Alex Blakenburg]

You mean something like this ?

Sub CopyRange()
    Dim last_row As Long
    Dim copy_rng As Range
    Dim ws As Worksheet
    
    Set ws = ActiveSheet
    last_row = ws.Range("D" & Rows.Count).End(xlUp).Row
    Set copy_rng = ws.Range("D4:Q" & last_row)
    copy_rng.Copy
    
End Sub

 

[Luke1690]

That worked perfectly thankyou i was just about to reply as i found a soloution myself which does the same thankyou both work.

Sub pastebelowlastcell()
Dim lRow As Long
Dim LastRow As Long
lRow = Sheets("Sheet1").Cells(Rows.Count, "d").End(xlUp).Row
lRow = lRow
LastRow = Sheets("Sheet1").Cells(Rows.Count, "q").End(xlUp).Row
LastRow = LastRow
ActiveSheet.Range("d4:q" & lRow).Copy

 

[Alex Blakenburg]

Thanks for letting me know. Glad I was of some help .

 

[Jimmypop]

Hi there

This could also work, however if you want to be more specific with ranges then Alex's solution is best...

Sub somesub()
    Range("D4").Offset(1, 0).Select
        Range(ActiveCell, ActiveCell.End(xlDown).End(xlToRight)).Select
    Selection.Copy
End Sub

 

[Alex Blakenburg]

Sub pastebelowlastcell()
Dim lRow As Long
Dim LastRow As Long
lRow = Sheets("Sheet1").Cells(Rows.Count, "d").End(xlUp).Row
lRow = lRow
LastRow = Sheets("Sheet1").Cells(Rows.Count, "q").End(xlUp).Row
LastRow = LastRow
ActiveSheet.Range("d4:q" & lRow).Copy

In case you find a critique helpful see below:

Sub pastebelowlastcell()
    Dim lRow As Long
    Dim LastRow As Long
    lRow = Sheets("Sheet1").Cells(Rows.Count, "d").End(xlUp).Row
    ' lRow = lRow                       ' This line doesn't do anything
    LastRow = Sheets("Sheet1").Cells(Rows.Count, "q").End(xlUp).Row    ' Not used in original code
    'LastRow = LastRow                   ' This line doesn't do anything
    
    ' You are not using LastRow - if you are unsure if Column Q or Column D is going
    ' to be longer then test for the largest row value and use the larger value ie
    If lRow < LastRow Then lRow = LastRow
    ActiveSheet.Range("d4:q" & lRow).Copy

End Sub