Variable range without copy/paste

[Jimbob2000]

I use the following code to grab lines from employee timesheets and copy them into one master workbook. The first few columns sometimes don't contain data, but the forth column always does, so I use the xlDown and offset to find the range. Y5 is always the end of the copied range.

What I'm trying to do is stop using the clipboard for this task and instead find a way to use the .Values method, which I'm thinking will be a bit smoother.

One thing I'm really struggling with is designating the range on the master sheet that I want to equal the range I'm transferring in.

Any suggestions gratefully received!

Sub Grab()'
' Copies used rows on the active timesheet into the next available rows on the Grabber tab
'


'


    Dim g As Workbook
    Set g = ActiveWorkbook


    ActiveSheet.Select
    Range("K4").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(rowOffset:=0, columnOffset:=-3).Activate
    Range(ActiveCell, "Y5").Select
    Selection.Copy
    
    Application.WindowState = xlNormal
    Workbooks("Grabber.xlsm").Sheets("Grabber").Activate
    
    Range("D1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(rowOffset:=1, columnOffset:=-3).Activate
    
    ActiveCell.PasteSpecial Paste:=xlPasteValues
    
    Application.CutCopyMode = False
    
    g.Close
    
    Workbooks("Grabber.xlsm").Save
    
    Workbooks("Grabber.xlsm").Sheets("Staff Days").Activate
    
    
End Sub

 

[Fluff]

How about

Sub Jimbob2000()
   Dim Ary As Variant
   
   Ary = Range("H5:Y" & Range("K" & Rows.Count).End(xlUp).Row).Value2
   With Workbooks("Grabber.xlsm").Sheets("Grabber")
      .Range("D" & Rows.Count).End(xlUp).Offset(1, -3).Resize(UBound(Ary), 18) = Ary
   End With
   ActiveWorkbook.Close
   Workbooks("Grabber.xlsm").Save
   Workbooks("Grabber.xlsm").Sheets("Staff Days").Activate
End Sub

 

[MARK858]

Just a way of writing it without an array (if I have read it right, not tested).

    With Range(Range("H" & Range("K4").End(xlDown).Row), "Y5")
        Sheets("Grabber").Range("D1") _
        .End(xlDown).Offset(1, -3).Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With

 

[Jimbob2000]

Thanks for this!

I realized that there are formulas in the cells below those that I'm trying to transfer across--not in the D column, but in some of those to the right. When I use this macro, it pulls some data across from those cells.

Is there a way to stop the range at the last row with data in the D column?

Thanks again!

J

 

[Fluff]

Just change the K to D on this line

Ary = Range("H5:Y" & Range("[COLOR=#ff0000]K[/COLOR]" & Rows.Count).End(xlUp).Row).Value2

 

[Jimbob2000]

This worked perfectly! Thank you!

 

[Fluff]

Glad w could help & thanks for the feedback