VBA to determine range of data at left side and copy it to the right side of Excel sheet.

[Flicker]

Hi
I have a dynamic range of data on the left (by using the randarray() function.) and I want to add VBA to a button that copy this value and paste as value to the right (as a history of random value I generated.)

I tried to google VBA but I can't get what I want. Now the problem are:

1. I cannot select range of data at the left side of my excel yet.
The origin of data is cell A5. but the number of rows and columns here is dynamic. So, I have to determine this area.
I tried to use the following code but it select only 1 row or 1 column. not the entire range of those area I want.

Range(ActiveCell, ActiveCell.End(xlDown)).Select
Range(ActiveCell, ActiveCell.End(xlToRight)).Select

2. If I can determine the range of data then I can copy and paste value with the following vba.

Range.Copy
'Range("J5").PasteSpecial (xlPasteValues)

Could somebody suggest me what I should do please?

Thank you

 

[Trebor76]

Hi Flicker,

Use the same parameters for the RANDARRAY function to set (resize) the range from cell A5 like so:

Option Explicit
Sub Macro2()

    Dim i As Long
    
    On Error Resume Next
        i = Range("K:M").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    On Error GoTo 0
    
    i = IIf(i < 5, 5, i + 3) 'Five is the default initial row with two blank rows inbetween. Change to suit if necessary.
    With Application
        .ScreenUpdating = False
            Range("A5").Resize(Range("B3"), Range("B1")).Copy
            Range("K" & i).PasteSpecial xlPasteValues
        .CutCopyMode = False: .ScreenUpdating = True
    End With

End Sub

Regards,

Robert

 

[Flicker]

Thank you very much Trebor76.

 

[Trebor76]

Thank you very much Trebor76.

You're welcome

 

[kevin9999]

Another option (i'd already started so I thought I might as well post it )

Option Explicit
Sub Copy_Random_Array()
    Application.Calculation = xlManual
    Dim ws As Worksheet, r As Range, t As Range, LRow As Long, LCol As Long
    Set ws = Worksheets("Sheet1")       '<-- *** Change to actual sheet bane ***
    LRow = ws.Cells(Rows.Count, "A").End(xlUp).Row + 2
    LCol = Application.Max(ws.Cells(5, Columns.Count).End(xlToLeft).Column, 10)
    
    Set r = ws.Range("A5").CurrentRegion
    Set r = r.Resize(r.Rows.Count + 2)
    
    Set t = ws.Range(ws.Cells(5, 11), ws.Cells(LRow, LCol))
    Application.CutCopyMode = False
    t.Insert shift:=xlDown
    ws.Range("K5").Resize(r.Rows.Count, r.Columns.Count).Value = r.Value
    Application.Calculation = xlAutomatic
End Sub