Re: Moving (Up, Down, etc.) to another cell, without clearly selecting a specific cell
Sure I have added my code below. Much of the code was created using the macro recording option, so pelase excuse the amatuer level of the coding.
READ BEFORE CODE FOR CLARITY: The macro below copies data from sheet one which containt data organized by dates in Column A, the copied data is taken in 2 week intervals, so the week of the date selected and the following week are copied and taken to sheet2 for pasting. ONE OF THE PROBLEMS I AN HAVING: if I select a date which as an incomplete selected week the macro doesnt colec the data, I need to fix it so that if i select a date from the firt 5 dates for example, the macro will still collect data for as much of the week as is available.
SECOND PROBLEM:
Everytime I paste new data into the Sheet2, I have to create a new line over which I overlap newly pasted info so that all the data isnt just overwrite itself.
Code:
Sub Two_week_period_dataselection()
' Local Variables
Dim dteEnd As Date
Dim dteStart As Date
Dim intRowCount As Integer
' Verify selection contains a valid date
If Not IsDate(Selection.Range("A1")) Then Exit Sub
' Set date selection range
dteStart = DateValue(DateAdd("d", -Weekday(Selection.Range("A1")) + 1, Selection.Range("A1")))
dteEnd = DateAdd("d", 13, dteStart)
' Find size of data and select
intRowCount = 0
Do While Selection.Range("A1").Offset(intRowCount, 0) >= dteStart
intRowCount = intRowCount - 1
Loop
Selection.Range("A1").Offset(intRowCount + 1, 0).Select
intRowCount = 1
Do While Selection.Range("A1").Offset(intRowCount + 1, 0) <= dteEnd
intRowCount = intRowCount + 1
Loop
Range(Selection, Selection.Range("A1").Offset(intRowCount, 0)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Sheet2").Select
ActiveCell.Activate
ActiveSheet.Paste
'Creating extra highlighted line, indicating end of 2 week interval
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Selection.Insert Shift:=xlDown
'highlighting of line
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
'Now I am clearingt he contents of the extra copied line, so that I can insert the next set of data which will overlap over the deleted row
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
Selection.End(xlToLeft).Select
Sheets("Sheet1").Select
End Sub
