Jimbob2000
New Member
- Joined
- Jun 27, 2019
- Messages
- 25
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!
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!
Code:
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