Move all rows 1 down but skip row 26 & Public Function IF problem

Hey guys, its me again :p

I've got a pretty nice Worklist setup in Excel that enables you to organize all the work you need to do in a month. However i end up with little holes in the list whenever i finish a task because what my macroes does is whenever a job is marked as done on the list it moves that job to sheet2, which contains completed jobs. I'd like to have a macro that shifts all the rows 1 down whenever a job is completed(ill just call it from the job complete macro) but it needs to jump over(skip) row 26 because that row contains some images that my macros use. Altso i would like to, if possible avoid inserting any rows or cells or hiding them because that will totally mess up my macros :p I know i know, im not dynamic enough.


I've included a sample of the worksheet so you can see what i want.

By the way, I've altso got some trouble with a public function.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


If Target = 0 Then

    Select Case Target.Address
    Case "$A$15", "$A$48", "$A$49", "$A$50", "$A$51", "$A$52", "$A$53", "$A$54", "$A$55", "$A$56", "$A$57", "$A$58", "$A$59", "$A$60", "$A$61", "$A$62", "$A$63", "$A$64", "$A$65", "$A$66", "$A$67", "$A$68", "$A$69", "$A$70", "$A$71", "$A$72", "$A$73", "$A$74", "$A$75", "$A$76", "$A$77", "$A$16", "$A$17", "$A$18", "$A$19", "$A$20", "$A$21", "$A$22", "$A$23", "$A$24", "$A$25", "$A$26", "$A$27", "$A$28", "$A$29", "$A$30", "$A$31", "$A$32", "$A$33", "$A$34", "$A$35", "$A$36", "$A$37", "$A$38", "$A$39", "$A$40", "$A$41", "$A$42", "$A$43", "$A$44", "$A$45", "$A$46", "$A$47", "$L$15", "$L$16", "$L$17", "$L$18", "$L$19", "$L$20", "$L$21", "$L$22", "$L$23", "$L$24", "$L$25", "$L$26", "$L$27", "$L$28", "$L$29", "$L$30", "$L$31", "$L$32", "$L$33", "$L$34", "$L$35", "$L$36", "$L$37", "$L$38", "$L$39", "$L$40", "$L$41", "$L$42", "$L$43", "$L$44", "$L$45", "$L$46", "$L$48", "$L$47", "$L$49", "$L$50", "$L$51", "$L$52", "$L$53", "$L$54", "$L$55", "$L$56", "$L$57", "$L$58", "$L$59", "$L$60", "$L$61", "$L$62", "$L$63" _
, "$L$64", "$L$65", "$L$66", "$L$67", "$L$68", "$L$69", "$L$70", "$L$71", "$L$72", "$L$73", "$L$74", "$L$75", "$L$76", "$L$77"
   Target.Value = Date
   Target.NumberFormat = "dd.mm.yy"
   End Select
   
   End If
   End Sub

I'm trying to have this code insert Timestamp whenever a cell in side the Ranges is clicked, so far it does work but the timestamp changes when its clicked again, so i tried entering If Target = 0 to force it to only do it when the cell is empty, but it runs on all the cells now Anyone know a quick fix for this? =)

Thanks in advance,

George.

Hi George

For your second question try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Target, Range("A15:A77,L15:L77")) Is Nothing Then Exit Sub

If Target = 0 Then
   Target.NumberFormat = "dd.mm.yy"
   Target.Value = Date
End If
End Sub

Thanks! That worked well Just 1 quick note, is there any easy command to have the range ignore row 26? or do i have to define each cell individually then?

Cheers

upon further testing i realized that this new code is interfering with me existing code,

whenever i try to run this:

Sub Row1Copy()

Dim Answer As String
Dim MyNote As String

'Place your text here
    MyNote = "Vil du flytte raden til ferdige oppgaver?"

    'Display MessageBox
    Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "???")

    If Answer = vbNo Then
        'Code for No button Press
        Exit Sub
    Else
        'Code for Yes button Press
        Sheets("Aktive Oppgaver").Activate
        Range("A15:L15").Select
        Selection.Copy
        Sheets("Ferdige Oppgaver").Activate
        Rows("15:15").Select
        Selection.Insert Shift:=xlDown
        Sheets("Aktive Oppgaver").Activate
        Range("K15").Select
        Selection.EntireRow.Select
        Selection.ClearContents
        
        End If
        Call RemovePic1

I get the error message: run time error 13 type mismatch

and when i click debug i'm pointed to:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Target, Range("A15:A77,L15:L77")) Is Nothing Then Exit Sub

If Target = 0 Then
   Target.NumberFormat = "dd.mm.yy"
   Target.Value = Date
End If
End Sub

The part in bold is the part that is yellowed out. I'm assuming something here is interfering with my code running properly.

Ok, so the part that was supposed to be bold is

If Target = 0 Then

File Type: xls Book1.xls (165.5 KB, 2 views)

I uploaded this the same time i made the first post..

This contains the relevant data, or a sample of it anyway.
It's the first sheet i need shifted down, i've already sorted sheet2 with a simple insert.shift.xldown

But the 1st sheet isnt so simple since it has a huge gap in Row 26 and i don't know how jump over it without using an offset and that would mean offsetting each and every row.. Would be nice if someone knew an easy way

By the way, i get the same error whenever i select a bunch of cells by dragging around them, i tried changing Target to Trgt by dimming it as range but then it just gives me a compile error.

edit: altso tried changing Target = 0 to Target = "" but it didnt affect it in any way.

Awesome! When you put it that was using Selection does seem pretty silly :D I didn't know there was a Double Click event to use, i'll definately use that

Thanks a bunch for your time