copy-paste the last 600 columns in sheet 2

[Dipak543]

want to copy past the last 600 columns of d and e in sheet2. Sheet 1 column values are dynamic in every 5 min its increases

 

[My Aswer Is This]

You said: last 600 columns of d and e
Did you man last 600 rows of Column D and E.

And I do not know how to run a script every 5 minutes

 

[Dipak543]

You said: last 600 columns of d and e
Did you man last 600 rows of Column D and E.

And I do not know how to run a script every 5 minutes

my column numbers are increasing every 5 min I am using a VBA which is printing stock value every 5 min. I want to copy-paste the last 600 cells of columns d and e in sheet2. if stock values are printed till 1000 columns I want the last 600 values, 1000 minus 600 column ie from 400 columns to 1000 column values copied in sheet 2 automatically

 

[My Aswer Is This]

I see you Quoted my question.
Not sure what Quoted means

 

[Dipak543]

I see you Quoted my question.
Not sure what Quoted means

my column numbers are increasing every 5 min I am using a VBA which is printing stock value every 5 min. I want to copy-paste the last 600 cells of columns d and e in sheet2. if stock values are printed till 1000 columns I want the last 600 values, 1000 minus 600 column ie from 400 columns to 1000 column values copied in sheet 2 automatically

 

[My Aswer Is This]

my column numbers are increasing every 5 min I am using a VBA which is printing stock value every 5 min. I want to copy-paste the last 600 cells of columns d and e in sheet2. if stock values are printed till 1000 columns I want the last 600 values, 1000 minus 600 column ie from 400 columns to 1000 column values copied in sheet 2 automatically

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[igold]

my column numbers are increasing every 5 min I am using a VBA which is printing stock value every 5 min.

If you have VBA that is running every 5 minutes, as opposed to using the Excel built in Automatic Refresh for Data Types, then you can add this code to your VBA. This code will overwrite the values on Sheet2 every time the code executes. Additionally, you did not specify where on Sheet2 you want the data written, so I assumed you had a header in Row 1 and started the data in Cell A2 on Sheet2.

Sub Last600()

    Dim lRow As Long, arr
    Dim ws1 As Worksheet: Set ws1 = Worksheets("Sheet1")
    Dim ws2 As Worksheet: Set ws2 = Worksheets("Sheet2")
    
    lRow = ws1.Cells(Rows.Count, 4).End(xlUp).Row
    If lRow <= 601 Then
        arr = ws1.Range("D2" & ":E" & lRow)
        ws2.Range("A2").Resize(UBound(arr, 1), 2) = arr
        Exit Sub
    Else
        arr = ws1.Range("D" & lRow - 599 & ":E" & lRow)
    End If
    ws2.Range("A2").Resize(600, 2) = arr
    
End Sub

 

[Dipak543]

If you have VBA that is running every 5 minutes, as opposed to using the Excel built in Automatic Refresh for Data Types, then you can add this code to your VBA. This code will overwrite the values on Sheet2 every time the code executes. Additionally, you did not specify where on Sheet2 you want the data written, so I assumed you had a header in Row 1 and started the data in Cell A2 on Sheet2.

Sub Last600()

    Dim lRow As Long, arr
    Dim ws1 As Worksheet: Set ws1 = Worksheets("Sheet1")
    Dim ws2 As Worksheet: Set ws2 = Worksheets("Sheet2")
   
    lRow = ws1.Cells(Rows.Count, 4).End(xlUp).Row
    If lRow <= 601 Then
        arr = ws1.Range("D2" & ":E" & lRow)
        ws2.Range("A2").Resize(UBound(arr, 1), 2) = arr
        Exit Sub
    Else
        arr = ws1.Range("D" & lRow - 599 & ":E" & lRow)
    End If
    ws2.Range("A2").Resize(600, 2) = arr
   
End Sub

thank you igold its working perfectly God bless you

 

[igold]

You're welcome. I was very happy to help. Thanks for the feedback.