Results 1 to 3 of 3

Need tweak to this code that moves a row of data to another sheet

Threaded View

PitBoo Need tweak to this code that... 12-01-2015, 03:31 PM
protonLeah Re: Need tweak to this code... 12-01-2015, 04:11 PM
PitBoo Re: Need tweak to this code... 12-01-2015, 06:00 PM
  1. #1
    Registered User
    Join Date
    03-18-2015
    Location
    Detroit
    MS-Off Ver
    2013
    Posts
    78

    Question Need tweak to this code that moves a row of data to another sheet

    Hi,
    I have this code that a few of you have helped me with a few weeks ago and it works good except one small thing.

    The idea was to move an entire row based on a date (any) that is put in the same row in column "I" (starting at row 8), which doesn't work at this time either (it takes any row even above 8 if anything is entered into that rows "I column" but that's ok I locked everyone out of 1-7 for the headers).

    The way it is now if anything goes in the cell it moves the row, which is ok (not ideal, I would like it to only move if its a date) but if any letter or anything is in there it moves. The biggest issue is that if someone starts typing something in that cell and they realize they clicked in the wrong row/date cell and try to backspace or delete the date, no matter what they do it still shoots the row to the next page. Even if they backspace and click another cell, that cell is gone. Any way to prevent this based on what is below?

    Thanks, and if it could be a "date only" trigger that would be fantastic, I'm just not sure how to work that into the code.

    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim nextrow As Long, i As Long
    
    nextrow = Sheet10.Cells(Rows.Count, "I").End(xlUp).Row + 1
    
    If Target.Cells.Count > 1 Then Exit Sub
    
    Application.ScreenUpdating = False
    
    If Not Intersect(Target, Range("I8:I" & Cells(Rows.Count, "I").End(xlUp).Row)) Is Nothing Then
        i = Target.Row
        If Target.NumberFormat > 1 Then
            Range(Cells(i, "A"), Cells(i, "O")).Copy
            Sheet10.Range("A" & nextrow).PasteSpecial xlPasteValues
            Application.EnableEvents = False
            Range("A" & Target.Row).EntireRow.Delete
            Application.EnableEvents = True
            Range("I" & i).Select
        End If
    End If
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
    End Sub
    Last edited by PitBoo; 12-01-2015 at 03:35 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Can someone tweak a small vba code for me?
    By boniouk in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-27-2013, 07:35 AM
  2. moves data from one excel sheet to another
    By tjdarth in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-18-2013, 05:27 AM
  3. Remove conditional format once data moves to another sheet
    By stankanie in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-28-2012, 04:06 PM
  4. Problems creating a time macro that moves data from one sheet to another.
    By Sanorah in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-23-2012, 08:27 AM
  5. Replies: 1
    Last Post: 03-01-2011, 03:31 PM
  6. code tweak to copy data from sheet1 to sheet2
    By imatomic in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-28-2010, 02:18 PM
  7. Best way to 'tweak' my code?
    By hutch@edge.net in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-29-2008, 10:10 AM
  8. [SOLVED] Code Tweak
    By bodhisatvaofboogie in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-21-2006, 11:40 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1